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

来源:网络时间:2011-08-04

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

  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.name<>'dtproperties'
  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.name<>'dtproperties' where c.name = 表名

意见反馈
发表评论

最新评论(共0条)