SQLServer表数据字典/表描述动态查询_改进

网上流传的查询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,2)  
                            OR cl.name = INDEX_COL(@tableName,3)  
                            OR cl.name = INDEX_COL(@tableName,4)  
                            OR cl.name = INDEX_COL(@tableName,5)  
                            OR cl.name = INDEX_COL(@tableName,6)  
                            OR cl.name = INDEX_COL(@tableName,7)  
                            OR cl.name = INDEX_COL(@tableName,8)  
                            OR cl.name = INDEX_COL(@tableName,9)  
                            OR cl.name = INDEX_COL(@tableName,10)  
                            OR cl.name = INDEX_COL(@tableName,11)  
                            OR cl.name = INDEX_COL(@tableName,12)  
                            OR cl.name = INDEX_COL(@tableName,13)  
                            OR cl.name = INDEX_COL(@tableName,14)  
                            OR cl.name = INDEX_COL(@tableName,15)  
                            OR cl.name = INDEX_COL(@tableName,16)  
                        )  
                        AND cl.name = a.name )  
                THEN 'Y'  
                ELSE ''  
                END,[DB Type] = b.name,[Bytes in store] = a.length,[Length] = COLUMNPROPERTY(a.id,'PRECISION'),[Numeric] = ISNULL(COLUMNPROPERTY(a.id,'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  

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!