sbf123官网

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

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

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

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 = 表名

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章