用Sql语句查询表结构

关于表结构的查询可以用sp_help，但是查出来的比较凌乱，而且列名还是英文的，不方便拷贝。

自己根据系统表写了一个查询表结构的语句，方便使用

DECLARE @TableName VarChar(1000)
SET @TableName = '要查询表结构的表名'
SELECT 列名 = a.name ,
        类型 = CASE
             WHEN b.name = 'numeric' THEN b.name + '(' + CAST(a.xprec AS VarChar(5)) + ', ' + CAST(a.xscale AS VarChar(5)) + ')'
             WHEN b.name = 'varchar' THEN b.name + '(' + CAST(a.length AS VarChar(10)) + ')'
             WHEN b.name = 'datetime' THEN b.name
             WHEN b.name = 'int' THEN b.name
             WHEN b.name = 'bit' THEN b.name
             WHEN b.name = 'smallint' THEN b.name
             END ,
        --类型 = b.name ,
        --长度 = a.length ,
        --精度级别 = a.xprec ,
        --小数位数 = ISNULL(a.xscale , '') ,
        标识 = CASE WHEN COLUMNPROPERTY(a.id , a.name , 'IsIdentity') = 1 THEN '√' ELSE '' END ,
        主键 = CASE WHEN c.name IS NULL THEN '' ELSE '√' END ,
        允许空Null = CASE WHEN isnullable = 1 THEN '√' ELSE '' END ,
        默认值 = ISNULL(d.text , '') ,
        解释说明 = ''
FROM syscolumns a
     INNER JOIN systypes b ON a.xtype = b.xtype
     LEFT JOIN(SELECT d.name , d.id , d.colid
               FROM sysobjects a
                    INNER JOIN sysindexes b ON a.parent_obj = b.id
                                           AND a.name = b.name
                                           AND a.xtype = 'PK'
                    INNER JOIN sysindexkeys c ON b.id = c.id
                                             AND b.indid = c.indid
                    INNER JOIN syscolumns d ON c.id = d.id
                                           AND c.colid = d.colid
               WHERE OBJECT_NAME(a.parent_obj) = @TableName)c ON a.id = c.id
                                                             AND a.colid = c.colid
     LEFT JOIN syscomments d ON a.cdefault = d.id
WHERE OBJECT_NAME(a.id) = @TableName
ORDER BY a.colid

　　对于查询中用到的一些系统表跟表中的列名的意思，自己去查sql server的联机丛书，相信你能够看明白。

转载于:https://www.cnblogs.com/Home-Root/p/3730189.html