在 SQL Server 2005 中查询表结构及索引
在 SQL Server 2005 中查询表结构及索引--1.表结构信息查询
--========================================================================
--表结构信息查询
--邹建2005.08(引用请保留此信息)
--========================================================================
SELECT
TableName=CASEWHENC.column_id=1THENO.nameELSEN''END,
TableDesc=ISNULL(CASEWHENC.column_id=1THENPTB.END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
=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,
=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.,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.=O.
ANDO.type='U'
ANDO.is_ms_shipped=0
INNERJOINsys.typesT
ONC.user_type_id=T.user_type_id
LEFTJOINsys.default_constraintsD
ONC.=D.parent_object_id
ANDC.column_id=D.parent_column_id
ANDC.default_object_id=D.
LEFTJOINsys.extended_propertiesPFD
ONPFD.class=1
ANDC.=PFD.major_id
ANDC.column_id=PFD.minor_id
--ANDPFD.name='Caption'--字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFTJOINsys.extended_propertiesPTB
ONPTB.class=1
ANDPTB.minor_id=0
ANDC.=PTB.major_id
--ANDPFD.name='Caption'--表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFTJOIN--索引及主键信息
(
SELECT
IDXC.,
IDXC.column_id,
Sort=CASEINDEXKEY_PROPERTY(IDXC.,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.=IDXC.
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.=KC.
ANDIDX.index_id=KC.unique_index_id
INNERJOIN--对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT,Column_id,index_id=MIN(index_id)
FROMsys.index_columns
GROUPBY,Column_id
)IDXCUQ
ONIDXC.=IDXCUQ.
ANDIDXC.Column_id=IDXCUQ.Column_id
ANDIDXC.index_id=IDXCUQ.index_id
)IDX
ONC.=IDX.
ANDC.column_id=IDX.column_id
--WHEREO.name=N'要查询的表'--如果只查询指定表,加上此条件
ORDERBYO.name,C.column_id
--2.索引及主键信息
--========================================================================
--索引及主键信息
--邹建2005.08(引用请保留此信息)
--========================================================================
SELECT
TableId=O.,
TableName=O.Name,
IndexId=ISNULL(KC.,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.,IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN1THEN'DESC'WHEN0THEN'ASC'ELSE''END,
PrimaryKey=CASEWHENIDX.is_primary_key=1THENN'√'ELSEN''END,
=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.=IDXC.
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.=KC.
ANDIDX.index_id=KC.unique_index_id
INNERJOINsys.objectsO
ONO.=IDX.
INNERJOINsys.columnsC
ONO.=C.
ANDO.type='U'
ANDO.is_ms_shipped=0
ANDIDXC.Column_id=C.Column_id
--INNERJOIN--对于一个列包含多个索引的情况,只显示第1个索引信息
--(
--SELECT,Column_id,index_id=MIN(index_id)
--FROMsys.index_columns
--GROUPBY,Column_id
--)IDXCUQ
--ONIDXC.=IDXCUQ.
--ANDIDXC.Column_id=IDXCUQ.Column_id
--
页:
[1]