【Sql语句】查询数据的字段说明

sql 语句

SELECT 表名=CASE
    WHEN a.colorder=1 THEN
    d.name
    ELSE ''
    END ,表说明 =CASE
    WHEN a.colorder=1 THEN
    ISNULL(f.value,'')
    ELSE ''
    END ,字段序号=a.colorder ,字段名 =a.name ,标识 =CASE
    WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN
    '√'ELSE ''
    END ,主键 =CASE
    WHEN EXISTS
    (SELECT 1
    FROM sysobjects
    WHERE xtype='PK'
            AND name IN 
        (SELECT name
        FROM sysindexes
        WHERE indid IN
            (SELECT indid
            FROM sysindexkeys
            WHERE id = a.id
                    AND colid=a.colid ))) THEN
            '√'
    ELSE ''
    END ,类型 =b.name ,占用字节=a.length ,长度 =COLUMNPROPERTY(a.id,a.name,'PRECISION') ,小数位数=ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) ,允许空 =CASE
    WHEN a.isnullable=1 THEN
    '1'ELSE ''
    END ,默认值 =ISNULL(e.text,'') ,字段说明=ISNULL(g.[value],'')
FROM syscolumns a
LEFT 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 JOIN syscomments e
    ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g
    ON a.id=g.major_id
        AND a.colid=g.minor_id
LEFT JOIN sys.extended_properties f
    ON d.id=f.major_id
        AND f.minor_id=0
WHERE d.name LIKE 'Abstract%'
ORDER BY  a.id,a.colorder

通用语句

declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
SELECT 
CASE
    WHEN a.colorder=1 
	THEN d.name
    ELSE ''
    END as 表名 ,
	CASE
    WHEN a.colorder=1 
	THEN ISNULL(f.value,'')
    ELSE ''
    END AS 表说明 ,
	a.colorder 字段序号,
	 a.name  AS 字段名, 
	b.name 类型  ,
	a.length 占用字节 ,
	COLUMNPROPERTY(a.id,a.name,'PRECISION')长度 ,
   ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) 小数位数,
	a.isnullable  允许空, 
	ISNULL(e.text,'') 默认值 , 
	ISNULL(g.[value],'') 字段说明
FROM syscolumns a
LEFT 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 JOIN syscomments e
    ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g
    ON a.id=g.major_id
        AND a.colid=g.minor_id
LEFT JOIN sys.extended_properties f
    ON d.id=f.major_id
        AND f.minor_id=0
--WHERE d.name LIKE 'Abstract%'
WHERE d.name in (select name from sysobjects  where name like 'Abstract%')
ORDER BY  a.id,a.colorder

/*你的SQL脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())