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 ASCfrom sysobjects obj inner join syscolumns c on c.id=obj.id and obj.type='U' inner join systypes ton c.xusertype=t.xusertypeleft outer join syscomments mon c.cdefault=m.id--表说明(默认MS_Description)left outer join sys.extended_properties tableRemarkon 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 PFDON PFD.class=1 AND c.[id]=PFD.major_id AND c.colid=PFD.minor_idand 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.Namefrom sys.indexes IDXINNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_idLEFT outer JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_idINNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息(SELECT [object_id], Column_id, index_id=MIN(index_id)FROM sys.index_columnsGROUP BY [object_id], Column_id ) IDXCUQON IDXC.[object_id]=IDXCUQ.[object_id]AND IDXC.Column_id=IDXCUQ.Column_idAND IDXC.index_id=IDXCUQ.index_id) IDXON C.[id]=IDX.[object_id]AND C.colid=IDX.column_idorder by obj.name