数据字典（SQL2005）

最新推荐文章于 2024-04-20 11:21:24 发布

sdhdy

最新推荐文章于 2024-04-20 11:21:24 发布

阅读量636

点赞数

分类专栏：对象信息文章标签： sql join properties

对象信息专栏收录该内容

4 篇文章 0 订阅

订阅专栏

--功能概述：显示某一表的结构(适用MSSQL2005)

DECLARE @tableName nvarchar(100)
SET @tableName ='tablename'

SELECT  (    
CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名, 
a.colorder 字段序号, 
a.name 字段名, 
(CASE WHEN COLUMNPROPERTY( 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 WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空, 
ISNULL(e.text,'') 默认值, 
ISNULL(g.[value],'') AS 字段说明 
FROM syscolumns a 
LEFT JOIN systypes b ON a.xtype=b.xusertype 
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name <>'dtproperties' 
LEFT JOIN syscomments e ON a.cdefault=e.id 
LEFT JOIN sys.extended_properties g  ON a.id=g.major_id AND a.colid = g.major_id 
WHERE d.name=@tableName 
ORDER BY a.id,a.colorder