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

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

网上流传的查询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  

总结

以上是内存溢出为你收集整理的SQLServer表数据字典/表描述动态查询_改进全部内容，希望文章能够帮你解决SQLServer表数据字典/表描述动态查询_改进所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。