SELECT d.name ‘TABLE_NAME‘,f.value as ‘TABLE_COMMENT‘,a.[name] as ‘COLUMN_NAME‘,a.length ‘长度‘,c.[name] ‘DATA_TYPE‘,e.value as ‘COLUMN_COMMENT‘,
COLUMNPROPERTY(a.id,a.name,‘IsIdentity‘) as ‘COLUMN_IDENTITY‘,
case when exists(SELECT 1 FROM sysobjects where xtype=‘PK‘and name in (SELECT name
FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys
WHERE id = a.id AND colid=a.colid ))) then 1 else 0 end as ‘COLUMN_PRIMARYKEY‘
FROM syscolumns a left join systypes b on a.xusertype=b.xusertype
left join systypes c on a.xtype = c.xusertype
inner join sysobjects d on a.id=d.id and d.xtype=‘U‘
left join sys.extended_properties e on a.id = e.major_id and a.colid = e.minor_id and e.name=‘MS_Description‘
left join sys.extended_properties f on d.id = f.major_id and f.minor_id=0 and e.name=‘MS_Description‘

SQLServer数据库查询数据库表和所有字段信息