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

if exists (select 1 from sysobjects where name = ‘sysproperties’and
xtype = ‘V’)
begin
    DROP VIEW sysproperties
end
    GO
    CREATE VIEW sysproperties
AS
 SELECT class AS id,Minor_id AS sMallid,* from
sys.extended_properties 
 

–1. SqlServer2000数据库字典–表结构.sql

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

——-在sql server 2005中获取表结构请先执行以上语句

SELECT TOP 100 PERCENT –a.id, s
      CASE WHEN a.colorder = 1 THEN d.name ELSE ” END AS 表名,
      CASE WHEN a.colorder = 1 THEN isnull(f.value, ”) ELSE ” END AS
表说明,
      a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN
COLUMNPROPERTY(a.id,
      a.name, ‘IsIdentity’) = 1 THEN ‘√’ ELSE ” END AS 标识,
      CASE WHEN EXISTS
          (SELECT 1
         FROM dbo.sysindexes si INNER JOIN
               dbo.sysindexkeys sik ON si.id = sik.id AND si.indid =
sik.indid INNER JOIN
               dbo.syscolumns sc ON sc.id = sik.id AND sc.colid =
sik.colid INNER JOIN
               dbo.sysobjects so ON so.name = si.name AND so.xtype =
‘PK’
         WHERE sc.id = a.id AND sc.colid = a.colid) THEN ‘√’ ELSE ” END
AS 主键,
      b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name,
‘PRECISION’)
      AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, ‘Scale’), 0) AS
小数位数,
      CASE WHEN a.isnullable = 1 THEN ‘√’ ELSE ” END AS 允许空,
ISNULL(e.text, ”)
      AS 默认值, ISNULL(g.[value], ”) AS 字段说明, d.crdate AS
创建时间,
      CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS
更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
      dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
      dbo.sysobjects d ON a.id = d.id AND d.xtype = ‘U’ AND
      d.status >= 0 LEFT OUTER JOIN
      dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
      dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
      g.name = ‘MS_Description’ LEFT OUTER JOIN
      dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
      f.name = ‘MS_Description’
ORDER BY d.name, a.colorder

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
我修改一下,变个精简版本的:

SELECT
  表名   = CASE a.colorder WHEN 1 THEN c.name ELSE ” END,
  序     = a.colorder,
  字段名 = a.name,
  标识   = CASE COLUMNPROPERTY(a.id,a.name,’IsIdentity’) WHEN 1 THEN ‘√’
ELSE ” END,
  主键   = CASE
    WHEN EXISTS (
      SELECT *
      FROM sysobjects
      WHERE xtype=’PK’ AND name IN (
        SELECT name
        FROM sysindexes
        WHERE id=a.id AND indid IN (
          SELECT indid
          FROM sysindexkeys
          WHERE id=a.id AND colid IN (
            SELECT colid
            FROM syscolumns
            WHERE id=a.id AND name=a.name
          )
        )
      )
    )
    THEN ‘√’
    ELSE ”
  END,
  类型   = b.name,
  字节数 = a.length,
  长度   = COLUMNPROPERTY(a.id,a.name,’Precision’),
  小数   = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,’Scale’),0)
    WHEN 0 THEN ”
    ELSE CAST(COLUMNPROPERTY(a.id,a.name,’Scale’) AS VARCHAR)
  END,
  允许空 = CASE a.isnullable WHEN 1 THEN ‘√’ ELSE ” END,
  默认值 = ISNULL(d.[text],”),
  说明   = ISNULL(e.[value],”)
FROM syscolumns a
  LEFT  JOIN systypes      b ON a.xtype=b.xusertype
  INNER JOIN sysobjects    c ON a.id=c.id AND c.xtype=’U’ AND
c.name<>’dtproperties’ and c.name=’此处改为你获取的表名称’
  LEFT  JOIN syscomments   d ON a.cdefault=d.id
  LEFT  JOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid
ORDER BY c.name, a.colorder

–2.    SqlServer2005数据库字典–表结构.sql

Author

发表评论

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