获取数据字典
?表结构信息查询
SELECT?
????TableName=CASE?WHEN?C.column_id=?THEN?O.name?ELSE?N''?END,
????TableDesc=ISNULL(CASE?WHEN?C.column_id=?THEN?PTB.[value]?END,N''),
????Column_id=C.column_id,
????ColumnName=C.name,
????PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
????[IDENTITY]=CASE?WHEN?C.is_identity=?THEN?N'√'ELSE?N''?END,
????Computed=CASE?WHEN?C.is_computed=?THEN?N'√'ELSE?N''?END,
????Type=T.name,
????Length=C.max_length,
????Precision=C.precision,
????Scale=C.scale,
????NullAble=CASE?WHEN?C.is_nullable=?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=
????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=?
????????????AND?C.[object_id]=PFD.major_id?
????????????AND?C.column_id=PFD.minor_id
--?????????????AND?PFD.name='Caption'??--?字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
????LEFT?JOIN?sys.extended_properties?PTB
????????ON?PTB.class=?
????????????AND?PTB.minor_id=?
????????????AND?C.[object_id]=PTB.major_id
--?????????????AND?PFD.name='Caption'??--?表说明对应的描述名称(一个表可以添加多个不同name的描述)?
?LEFT?JOIN???????????????????????--?索引及主键信息
????(
????????SELECT?
????????????IDXC.[object_id],
????????????IDXC.column_id,
????????????Sort=CASE?INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
????????????????WHEN??THEN?'DESC'?WHEN??THEN?'ASC'?ELSE?''?END,
????????????PrimaryKey=CASE?WHEN?IDX.is_primary_key=?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??--?对于一个列包含多个索引的情况,只显示第1个索引信息
????????(
????????????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'要查询的表'???????--?如果只查询指定表,加上此条件
ORDER?BY?O.name,C.column_id索引及主键信息
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??THEN?'DESC'?WHEN??THEN?'ASC'?ELSE?''?END,
????PrimaryKey=CASE?WHEN?IDX.is_primary_key=?THEN?N'√'ELSE?N''?END,
????[UQIQUE]=CASE?WHEN?IDX.is_unique=?THEN?N'√'ELSE?N''?END,
????Ignore_dup_key=CASE?WHEN?IDX.ignore_dup_key=?THEN?N'√'ELSE?N''?END,
????Disabled=CASE?WHEN?IDX.is_disabled=?THEN?N'√'ELSE?N''?END,
????Fill_factor=IDX.fill_factor,
????Padded=CASE?WHEN?IDX.is_padded=?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=
????????????AND?IDXC.Column_id=C.Column_id
--????INNER?JOIN??--?对于一个列包含多个索引的情况,只显示第1个索引信息
--????(
--????????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本文参与?腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2007-11-12 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读