SQLServer表数据字典/表描述动态查询_改进_sqlserver 表描述-优快云博客

针对网上流传的SQLServer表描述查询存在的不准确问题，本文提供了一种改进方案。详细讨论了如何处理是否主键的判断，并给出了适用于SQL2000及2005/2008的不同版本的查询SQL，通过加入或注释JOIN部分实现兼容。

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

网上流传的查询sqlserver表描述的方法，发现有些地方不准确，比如是否主键的处理。
改进了一下，对于sql2000和 2005/2008的区别，去掉/添加join部分的注释即可。

    -- =============================================  
    -- Author:      <E.R>  
    -- Create date: <5/31/2011>  
    -- Description: <Get the description of a table>  
    -- =============================================   
    CREATE PROCEDURE [dbo].[proc_QueryDD]   
        @tableName varchar(50)  
    AS  
    BEGIN  
       
    SELECT    
        [Owner] = h.name  
        ,[Table Name] = d.name  
        ,[Table Description] = ISNULL(f.value, '')  
        ,[Field Index] = a.colorder  
        ,[Field Name] = a.name  
        ,[Field Description] = ISNULL(g.[value], '')  
        ,[Is Identity] =   
            CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1  
                THEN 'Y'  
                ELSE ''  
            END  
        ,[Primary Key] =   
            CASE   
                WHEN EXISTS (   
                    SELECT 1 FROM sysindexes id, syscolumns cl, sysobjects ob  
                    WHERE ob.id = a.id AND ob.id = cl.id AND ob.id = id.id  
                        AND ( id.status & 0x800 ) = 0x800  
                        AND ( cl.name = INDEX_COL(@tableName, id.indid, 1)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 2)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 3)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 4)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 5)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 6)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 7)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 8)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 9)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 10)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 11)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 12)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 13)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 14)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 15)  
                            OR cl.name = INDEX_COL(@tableName, id.indid, 16)  
                        )  
                        AND cl.name = a.name )  
                THEN 'Y'  
                ELSE ''  
                END  
        ,[DB Type] = b.name  
        ,[Bytes in store] = a.length  
        ,[Length] = COLUMNPROPERTY(a.id, a.name, 'PRECISION')  
        ,[Numeric] = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0)  
        ,[Is Nullable] =   
            CASE WHEN a.isnullable = 1 THEN 'Y' ELSE '' END  
        ,[Default Value] = ISNULL(e.text, '')  
    FROM    syscolumns a  
    LEFT OUTER JOIN systypes b   
        ON a.xusertype = b.xusertype  
    INNER JOIN sysobjects d   
        ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'  
    LEFT OUTER JOIN sysusers h   
        ON h.uid = d.uid  
    LEFT OUTER JOIN syscomments e   
        ON a.cdefault = e.id  
    --X:for sql 2005/2008  
    LEFT OUTER JOIN sys.extended_properties g   
        ON a.id = g.major_id AND a.colid = g.minor_id AND g.name = 'MS_Description'  
    LEFT OUTER JOIN sys.extended_properties f   
    ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description'  
    --X:end  
    /*  
    --Y:for sql 2000  
    left outer join sysproperties g   
    on a.id=g.id and a.colid=g.smallid and g.name='MS_Description'  
    left outer join sysproperties f   
    on d.id=f.id and f.smallid=0 and f.name='MS_Description'  
    --Y:end  
    */  
    WHERE   d.name = @tableName  
    ORDER BY a.id, a.colorder  
       
    END   
       
      
    GO