MS SQL2000 && 2005转出数据字典

最新推荐文章于 2025-04-02 07:15:00 发布

转载最新推荐文章于 2025-04-02 07:15:00 发布 · 53 阅读

本文提供SQL Server 2000与2005中用于查询表结构的SQL语句，包括字段序号、字段名、类型等详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL 2000

          SELECT
        ( case when a.colorder = 1 then d.name else '' end )表名,
        a.colorder字段序號,
        a.name字段名,
        ( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end )標識,
        ( case when ( SELECT count ( * )
         FROM sysobjects
         WHERE (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))))))) AND
               (xtype = ' PK ' )) > 0 then ' √ ' else '' end )主鍵,
        b.name類型,
        a.length佔用字節數,
         COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as長度,
         isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as小數位數,
        ( case when a.isnullable = 1 then ' √ ' else '' end )允許空,
         isnull (e. text , '' )默認值,
         isnull (g. [ value ] , '' ) AS字段說明

FROM syscolumns a left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and a .name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by a.id,a.colorder

SQL 2005

SELECT
        ( case when a.colorder = 1 then d.name else '' end )表名,
        a.colorder字段序號,
        a.name字段名,
        ( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end )標識,
        ( case when ( SELECT count ( * )
         FROM sysobjects
         WHERE (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))))))) AND
               (xtype = ' PK ' )) > 0 then ' √ ' else '' end )主鍵,
        b.name類型,
        a.length佔用字節數,
         COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as長度,
         isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as小數位數,
        ( case when a.isnullable = 1 then ' √ ' else '' end )允許空,
         isnull (e. text , '' )默認值,
         isnull (g. [ value ] , '' ) AS字段說明

FROM syscolumns a left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sys.extended_properties g
on a.id = g.major_id AND a.colid = g.major_id
order by a.id,a.colorder

轉自:http://www.cnblogs.com/xh831213/archive/2008/04/21/1164093.html