想要查看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
【编辑推荐】
SQL约束控制语句
带您深入了解sql字段类型
教您如何获取SQL字段默认值
SQL修改字段默认值
SQL Server时间算法大全
当前标题:教您查询SQL字段属性的sql语句写法
URL地址:http://www.mswzjz.cn/qtweb/news29/315079.html
攀枝花网站建设、攀枝花网站运维推广公司-贝锐智能,是专注品牌与效果的网络营销公司;服务项目有等
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 贝锐智能