查询SQL Server数据库所有表字段备注
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | SELECT 表名 = case when a.colorder=1 then d. name else '' end , 表说明 = case when a.colorder=1 then isnull (f.value, '' ) else '' end , 字段序号 = a.colorder, 字段名 = a. name , 字段说明 = isnull (g.[value], '' ), 类型 = b. name , 占用字节数 = a.length, 长度 = COLUMNPROPERTY(a.id,a. name , 'PRECISION' ), 标识 = case when COLUMNPROPERTY( a.id,a. name , 'IsIdentity' )=1 then '√' else '' end , 主键 = case when exists( SELECT 1 FROM sysobjects where xtype= 'PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end , 小数位数 = isnull (COLUMNPROPERTY(a.id,a. name , 'Scale' ),0), 允许空 = case when a.isnullable=1 then '√' else '' end , 默认值 = isnull (e.text, '' ) FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype= 'U' and d. name <> 'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 --where d.name in ( 'TableName1','tableName2') --如果只查询指定表,加上此红色where条件,tablename是要查询的表名;去除红色where条件查询说有的表信息 order by a.id,a.colorder |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步