查看内容

查询数据库表和字段MSSQL语句

  • 2020-03-24 13:25
  • 编程人生
  • Views

核心提示:今天整理资料时看到有这样一个查询数据库中的表和字段信息的语句

今天整理资料时看到有这样一个查询数据库中的表和字段信息的语句,很强! 就是忘了当初谁写的了,印像中该是邹建所创。也贴出来以后备用吧。

1SELECT 2表名= CASE a.colorder WHEN 1 THEN c.name ELSE END, 3序= a.colorder, 4字段名 = a.name, 5标识= CASE COLUMNPROPERTY(a.id,a.name,IsIdentity) WHEN 1 THEN √ ELSE END, 6主键= CASE 7WHEN EXISTS ( 8SELECT * 9FROM sysobjects 10WHERE xtype=PK AND name IN ( 11SELECT name 12FROM sysindexes 13WHERE id=a.id AND indid IN ( 14SELECT indid 15FROM sysindexkeys 16WHERE id=a.id AND colid IN ( 17SELECT colid 18FROM syscolumns 19WHERE id=a.id AND name=a.name 20) 21) 22) 23) 24THEN √ 25ELSE 26END, 27类型= b.name, 28字节数 = a.length, 29长度= COLUMNPROPERTY(a.id,a.name,Precision), 30小数= CASE ISNULL(COLUMNPROPERTY(a.id,a.name,Scale),0) 31WHEN 0 THEN 32ELSE CAST(COLUMNPROPERTY(a.id,a.name,Scale) AS VARCHAR) 33END, 34允许空 = CASE a.isnullable WHEN 1 THEN √ ELSE END, 35默认值 = ISNULL(d.[text],), 36说明= ISNULL(e.[value],) 37FROM syscolumns a 38LEFTJOIN systypesb ON a.xtype=b.xusertype 39INNER JOIN sysobjectsc ON a.id=c.id AND c.xtype=U AND c.namedtproperties 40LEFTJOIN syscommentsd ON a.cdefault=d.id 41LEFTJOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid 42ORDER BY c.name, a.colorder 我修改一下,变个精简版本的:

1 2select a.name, b.xtype,b.name 3from syscolumns a 4innerJOIN systypesb 5ON a.xtype=b.xusertype 6inner join sysobjects c ON 7a.id=c.id AND c.xtype=U AND c.namedtproperties where c.name = 表名

上一篇:Server二〇〇一数据库分离与附加 下一篇:没有了