在 SQL Server 2005 中查询表结构及索引

在 SQL Server 2005 中查询表结构及索引

--1.表结构信息查询

--========================================================================
--表结构信息查询

--邹建2005.08(引用请保留此信息)
--========================================================================
SELECT
TableName=CASEWHENC.column_id=1THENO.nameELSEN''END,
TableDesc=ISNULL(CASEWHENC.column_id=1THENPTB.[value]END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASEWHENC.is_identity=1THENN'√'ELSEN''END,
Computed=CASEWHENC.is_computed=1THENN'√'ELSEN''END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASEWHENC.is_nullable=1THENN'√'ELSEN''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
FROMsys.columnsC
INNERJOINsys.objectsO
ONC.[object_id]=O.[object_id]
ANDO.type='U'
ANDO.is_ms_shipped=0
INNERJOINsys.typesT
ONC.user_type_id=T.user_type_id
LEFTJOINsys.default_constraintsD
ONC.[object_id]=D.parent_object_id
ANDC.column_id=D.parent_column_id
ANDC.default_object_id=D.[object_id]
LEFTJOINsys.extended_propertiesPFD
ONPFD.class=1
ANDC.[object_id]=PFD.major_id
ANDC.column_id=PFD.minor_id
--ANDPFD.name='Caption'--字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFTJOINsys.extended_propertiesPTB
ONPTB.class=1
ANDPTB.minor_id=0
ANDC.[object_id]=PTB.major_id
--ANDPFD.name='Caption'--表说明对应的描述名称(一个表可以添加多个不同name的描述)

LEFTJOIN--索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN1THEN'DESC'WHEN0THEN'ASC'ELSE''END,
PrimaryKey=CASEWHENIDX.is_primary_key=1THENN'√'ELSEN''END,
IndexName=IDX.Name
FROMsys.indexesIDX
INNERJOINsys.index_columnsIDXC
ONIDX.[object_id]=IDXC.[object_id]
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.[object_id]=KC.[parent_object_id]
ANDIDX.index_id=KC.unique_index_id
INNERJOIN--对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT[object_id],Column_id,index_id=MIN(index_id)
FROMsys.index_columns
GROUPBY[object_id],Column_id
)IDXCUQ
ONIDXC.[object_id]=IDXCUQ.[object_id]
ANDIDXC.Column_id=IDXCUQ.Column_id
ANDIDXC.index_id=IDXCUQ.index_id
)IDX
ONC.[object_id]=IDX.[object_id]
ANDC.column_id=IDX.column_id

--WHEREO.name=N'要查询的表'--如果只查询指定表,加上此条件
ORDERBYO.name,C.column_id

--2.索引及主键信息

--========================================================================
--索引及主键信息

--邹建2005.08(引用请保留此信息)
--========================================================================
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=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN1THEN'DESC'WHEN0THEN'ASC'ELSE''END,
PrimaryKey=CASEWHENIDX.is_primary_key=1THENN'√'ELSEN''END,
[UQIQUE]=CASEWHENIDX.is_unique=1THENN'√'ELSEN''END,
Ignore_dup_key=CASEWHENIDX.ignore_dup_key=1THENN'√'ELSEN''END,
Disabled=CASEWHENIDX.is_disabled=1THENN'√'ELSEN''END,
Fill_factor=IDX.fill_factor,
Padded=CASEWHENIDX.is_padded=1THENN'√'ELSEN''END
FROMsys.indexesIDX
INNERJOINsys.index_columnsIDXC
ONIDX.[object_id]=IDXC.[object_id]
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.[object_id]=KC.[parent_object_id]
ANDIDX.index_id=KC.unique_index_id
INNERJOINsys.objectsO
ONO.[object_id]=IDX.[object_id]
INNERJOINsys.columnsC
ONO.[object_id]=C.[object_id]
ANDO.type='U'
ANDO.is_ms_shipped=0
ANDIDXC.Column_id=C.Column_id
--INNERJOIN--对于一个列包含多个索引的情况,只显示第1个索引信息
--(
--SELECT[object_id],Column_id,index_id=MIN(index_id)
--FROMsys.index_columns
--GROUPBY[object_id],Column_id
--)IDXCUQ
--ONIDXC.[object_id]=IDXCUQ.[object_id]
--ANDIDXC.Column_id=IDXCUQ.Column_id
--