SQL Server 中获取数据库所有表及其字段名称、类型、长度的SQL

对于有些数据库我们没有权限或者因为其它原因无法查看其数据库的表结构，那么我们将要自己想办法去做了，如果能够应用程序访问的话，我们就可以使用如下所示的

SQL Server 2005及以上版本的sql脚本来获取数据库中的所有表结构了，代码如下：

SELECT ( CASE

           WHENa.colorder = 1 THEN

             d.name

           ELSE''

         END )                                          表名,

       a.colorder                                       字段序号,

       a.name                                           字段名,

       ( CASE

           WHENCOLUMNPROPERTY(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

           WHENa.isnullable = 1 THEN

             '√'

           ELSE''

         END )                                          允许空,

       isnull(e.text, '')                               默认值,

       isnull(g.[value], '')                            AS 字段说明

FROM   syscolumns a

       LEFT JOIN systypes b

              ONa.xtype = b.xusertype

       INNER JOIN sysobjects d

               ONa.id = d.id

                  ANDd.xtype = 'U'

                  ANDd.name <> 'dtproperties'

       LEFT JOIN syscomments e

              ONa.cdefault = e.id

       LEFT JOIN sys.extended_properties g

              ONa.id = g.major_id

                 ANDa.colid = g.minor_id

--WHERE  d.name IN ( 'Contact', 'StockBmps','AddressType' )---查询具体的表，注释掉后就是查询整个数据库了

ORDER  BY a.id,

          a.colorder

 

---

SQL Server 2000

如是SQL Server2000 那么就需要将sys.extended_properties用sysproperties代替了。

因为sysproperties这个系统表，但在2008版本中却提示找不到，在联机文档中也找不到，后来发现这个系统表在2005版本中就已经被系统表sys.extended_properties所代替。

查看表T的扩展信息：

select object_id from sys.sysobjects where name = 'T';

select * from sys.extended_properties where major_id = object_id;

扩展信息中有一name项是MS_Description，这个选项可以查看备注信息

select * from sys.extended_properties where major_id = object_id where name = 'MS_Description';

SELECT 表名 = CASE

              WHENa.colorder = 1 THEN

                d.name

              ELSE''

            END,

       表说明 = CASE

               WHENa.colorder = 1 THEN

                 isnull(f.value, '')

               ELSE''

             END,

       字段序号 = a.colorder,

       字段名 = a.name,

       标识 = CASE

              WHENCOLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN

                '√'

              ELSE''

            END,

       主键 = CASE

              WHENEXISTS(SELECT 1

                          FROM   sysobjects

                          WHERE  xtype = 'PK'

                                 AND parent_obj = a.id

                                 AND name IN(SELECT name

                                             FROM   sysindexes

                                             WHERE  indid IN(SELECT indid

                                                            FROM  sysindexkeys

                                                            WHERE id = a.id

                                                                   AND colid= a.colid))) THEN

                '√'

              ELSE''

            END,

       类型 = b.name,

       占用字节数 = a.length,

       长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),

       小数位数 = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),

       允许空 = CASE

               WHENa.isnullable = 1 THEN

                 '√'

               ELSE''

             END,

       默认值 = isnull(e.text, ''),

       字段说明 = isnull(g.[value], '')

FROM   syscolumns a

       LEFT JOIN systypes b

              ONa.xusertype = b.xusertype

       INNER JOIN sysobjects d

               ONa.id = d .id

                  ANDd .xtype = 'U'

                  ANDd .name <> 'dtproperties'

       LEFT JOIN syscomments e

              ONa.cdefault = e.id

       LEFT JOIN sysproperties g

              ONa.id = g.id

                 ANDa.colid = g.smallid

       LEFT JOIN sysproperties f

              ONd .id = f.id

                 ANDf.smallid = 0

ORDER  BY a.id,

          a.colorder

 

 

---

SQL Server 2000