想要查看SQL字段属性，应该如何实现呢？下面为您介绍的就是查询SQL字段属性的sql语句写法，希望对您学习SQL字段属性方面有所启迪。
- SELECT sysobjects.name AS tableName, syscolumns.name AS field, properties.[value] AS fieldRemark, systypes.name AS type,
- syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [decimal], syscolumns.isnullable AS isnulls,
- CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [Default],
- CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') = 1 THEN 'Y' ELSE 'N' END AS [id], 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 = syscolumns.id AND colid = syscolumns.colid)))
- THEN 'Y' ELSE 'N' END AS PK FROM syscolumns INNER JOIN sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
- systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN sysproperties properties ON syscolumns.id = properties.id AND
- syscolumns.colid = properties.smallid LEFT OUTER JOIN sysproperties ON sysobjects.id = sysproperties.id AND
- sysproperties.smallid = 0 LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
- WHERE (sysobjects.xtype = 'U') and systypes.name<>'sysname' order by sysobjects.name
- 使用：把这段代码拷贝到查询分析器内选中相关查询的数据库名即可。
- //符合我自己使用的查询数据库字典方法：SQL server 200
- SELECT sysobjects.name AS 表名,--获取数据库表名
- 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 = syscolumns.id AND colid = syscolumns.colid)))
- THEN 'K' ELSE '' END AS PK,--是否是主键
- syscolumns.name AS 字段名,
- properties.[value] AS 字段描述,
- systypes.name AS 数据类型,
- syscolumns.length AS 长度,
- CASE syscolumns.isnullable WHEN '1' THEN '是' ELSE '否'END AS 允许为空,
- CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS 默认值
- FROM syscolumns INNER JOIN
- sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
- systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
- sysproperties properties ON syscolumns.id = properties.id AND
- syscolumns.colid = properties.smallid LEFT OUTER JOIN
- sysproperties ON sysobjects.id = sysproperties.id AND sysproperties.smallid = 0 LEFT OUTER JOIN
- syscomments ON syscolumns.cdefault = syscomments.id
- WHERE (sysobjects.xtype = 'U') and systypes.name<>'sysname'
- order by sysobjects.name desc
【编辑推荐】
