用于SQL2005的表结构查询语句

来源：互联网发布：java变量类型有哪两种编辑：程序博客网时间：2024/05/22 04:16

前面转帖了“钱途无梁”的

［数据库字典］简单的3个SQL视图搞定所有SqlServer数据库字典

自己改了下，把中文放前面，以后好用，呵呵。

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 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 '√'ELSE '' END,
默认值=ISNULL(e.text,''),
字段说明=ISNULL(g.[value],'')
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.minor_id
WHERE
d.name = 'Sys_Users'
ORDER BY
object_name(a.id), a.colorder