Querying table structures and indexes in SQL Server 2005

server| Index

Querying table structures and indexes in SQL Server 2005
--1. Table Structure Information Query
-- ========================================================================
--Table Structure information query

--Jiangjian 2005.08 (please keep this information for reference)
-- ========================================================================
SELECT
Tablename=case when c.column_id=1 THEN o.name ELSE N ' "End,
Tabledesc=isnull (case when c.column_id=1 THEN ptb.[ Value], End,n '),
COLUMN_ID=C.COLUMN_ID,
Columnname=c.name,
Primarykey=isnull (IDX. Primarykey,n '),
[Identity]=case when C.is_identity=1 THEN n ' √ ' ELSE n ' "End,
Computed=case when c.is_computed=1 THEN n ' √ ' ELSE n ' "End,
Type=t.name,
Length=c.max_length,
Precision=c.precision,
Scale=c.scale,
Nullable=case when c.is_nullable=1 THEN n ' √ ' ELSE n ' "End,
[Default]=isnull (D.definition,n '),
Columndesc=isnull (Pfd.[value],n '),
Indexname=isnull (IDX. Indexname,n '),
Indexsort=isnull (IDX. Sort,n '),
Create_date=o.create_date,
Modify_date=o.modify_date
From Sys.columns C
INNER JOIN sys.objects O
On c.[object_id]=o.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
INNER JOIN sys.types T
On c.user_type_id=t.user_type_id
Left JOIN sys.default_constraints D
On c.[object_id]=d.parent_object_id
and c.column_id=d.parent_column_id
and c.default_object_id=d.[object_id]
Left JOIN sys.extended_properties PFD
On Pfd.class=1
and c.[object_id]=pfd.major_id
and c.column_id=pfd.minor_id
--and Pfd.name= ' Caption '--The description name for the field description (one field can add more than one description of different name)
Left JOIN sys.extended_properties PTB
On Ptb.class=1
and Ptb.minor_id=0
and c.[object_id]=ptb.major_id
--and Pfd.name= ' Caption '--table description of the corresponding description name (a table can add more than one description of different name)

Left JOIN--index and primary key information
(
SELECT
Idxc. [OBJECT_ID],
IDXC.COLUMN_ID,
Sort=case Indexkey_property (idxc.[ object_id],idxc.index_id,idxc.index_column_id, ' isdescending ')
When 1 THEN ' DESC ' if 0 THEN ' ASC ' ELSE ' end,
Primarykey=case when Idx.is_primary_key=1 THEN n ' √ ' ELSE n ' "End,
Indexname=idx. Name
From Sys.indexes IDX
INNER JOIN sys.index_columns IDXC
On IDX. [OBJECT_ID]=IDXC. [OBJECT_ID]
and idx.index_id=idxc.index_id
Left JOIN sys.key_constraints KC
On IDX. [OBJECT_ID]=KC.[PARENT_OBJECT_ID]
and idx.index_id=kc.unique_index_id
INNER JOIN--Displays only the 1th index information for a column that contains multiple indexes
(
SELECT [object_id], column_id, Index_id=min (index_id)
From Sys.index_columns
GROUP by [object_id], column_id
) Idxcuq
On IDXC. [Object_id]=idxcuq. [OBJECT_ID]
and IDXC. Column_id=idxcuq. column_id
and idxc.index_id=idxcuq.index_id
) IDX
On C.[object_id]=idx. [OBJECT_ID]
and c.column_id=idx.column_id

--where o.name=n ' tables to query '--if only the specified table is queried, plus this condition
ORDER BY o.name,c.column_id

--2. Index and primary key information
-- ========================================================================
--Index and primary key information

--Jiangjian 2005.08 (please keep this information for reference)
-- ========================================================================
SELECT
TABLEID=O.[OBJECT_ID],
Tablename=o.name,
Indexid=isnull (kc.[object_id],idx.index_id),
Indexname=idx. Name,
Indextype=isnull (Kc.type_desc, ' Index '),
INDEX_COLUMN_ID=IDXC.INDEX_COLUMN_ID,
COLUMNID=C.COLUMN_ID,
Columnname=c.name,
Sort=case Indexkey_property (idxc.[ object_id],idxc.index_id,idxc.index_column_id, ' isdescending ')
When 1 THEN ' DESC ' if 0 THEN ' ASC ' ELSE ' end,
Primarykey=case when Idx.is_primary_key=1 THEN n ' √ ' ELSE n ' "End,
[Uqique]=case when idx.is_unique=1 THEN n ' √ ' ELSE n ' "End,
Ignore_dup_key=case when Idx.ignore_dup_key=1 THEN n ' √ ' ELSE n ' "End,
Disabled=case when idx.is_disabled=1 THEN n ' √ ' ELSE n ' "End,
Fill_factor=idx.fill_factor,
Padded=case when idx.is_padded=1 THEN n ' √ ' ELSE n ' "End
From Sys.indexes IDX
INNER JOIN sys.index_columns IDXC
On IDX. [OBJECT_ID]=IDXC. [OBJECT_ID]
and idx.index_id=idxc.index_id
Left JOIN sys.key_constraints KC
On IDX. [OBJECT_ID]=KC.[PARENT_OBJECT_ID]
and idx.index_id=kc.unique_index_id
INNER JOIN sys.objects O
On O.[object_id]=idx. [OBJECT_ID]
INNER JOIN sys.columns C
On o.[object_id]=c.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
and IDXC. column_id=c.column_id
--INNER JOIN--Displays only the 1th index information for a column that contains multiple indexes
--    (
--SELECT [object_id], column_id, Index_id=min (index_id)
--From Sys.index_columns
--GROUP by [object_id], column_id
--) Idxcuq
--on IDXC. [Object_id]=idxcuq. [OBJECT_ID]
--and IDXC. Column_id=idxcuq. column_id
--