查询一个数据库中所有表的结构

select 
obj.name as tableName
,obj.type as objType
,ISNULL(tableRemark.value,N'') as tableRemark
,c.name as FieldName
,isnull(PFD.[value],'') as FieldRemark
,t.name as DateType
,c.prec as FieldLength
-- ,p.value as 字段说明
,ISNULL(m.text,N'') as DefaultValue
,C.isnullable
,ISNULL(IDX.PrimaryKey,0) as PrimaryKey
--标识的详细

,case when COLUMNPROPERTY(c.id,c.name,'IsIdentity')=1 then 1 else 0 end 
as IsIdentity
,IndexName=ISNULL(IDX.IndexName,N'')
,IndexSort=ISNULL(IDX.Sort,N'')
--DESC or ASC
from sysobjects obj 
inner join syscolumns c 
on c.id=obj.id and obj.type='U' 
inner join systypes t
on c.xusertype=t.xusertype
left outer join syscomments m
on c.cdefault=m.id
--表说明(默认MS_Description)
left outer join sys.extended_properties tableRemark
on tableRemark.class=1 and obj.id=tableRemark.major_id 
and tableRemark.minor_id=0 and tableRemark.name='MS_Description'
--取字段说明(默认MS_Description)
LEFT outer JOIN sys.extended_properties PFD
ON PFD.class=1 
AND c.[id]=PFD.major_id 
AND c.colid=PFD.minor_id
and PFD.name='MS_Description'
left outer join 
(
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' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN 1 ELSE 0 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 outer 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.[id]=IDX.[object_id]
AND C.colid=IDX.column_id
order by obj.name

 

转载于:https://www.cnblogs.com/gaoming7122/articles/2585355.html