betway必威-betway必威官方网站
做最好的网站

【betway必威官方网站】server数据库表结构,生成

骨干提醒:昨日照望资料时寓目好似此三个查询数据库中的表和字段音信的说话

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. SqlServer二零零零数据库词典--表构造.sql

今日整治材质时见到有那般三个查询数据库中的表和字段音信的说话,很强! 正是忘了当下何人写的了,印像中该是邹建所创。也贴出来未来备用吧。

-------在sql server 二零零五中赢得表构造请先进行以上语句

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')
betway必威官方网站,      AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'State of Qatar, 0卡塔尔国 AS 小数位数,
      CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
      AS 默认值, ISNULL(g.[value], ''State of Qatar 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 39INNELAND 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 42OLX570DE大切诺基 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
  INNETucson 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.    SqlServer二零零五数据库词典--表构造.sql

本文由betway必威发布于编程开发,转载请注明出处:【betway必威官方网站】server数据库表结构,生成

TAG标签: betway必威
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。