手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >获取SQL Server表字段的各种属性实例代码
获取SQL Server表字段的各种属性实例代码
摘要:复制代码代码如下:--SQLServer2000SELECTa.nameAS字段名,CASEWHENEXISTS(SELECT1FROMsy...

复制代码 代码如下:

-- SQL Server 2000

SELECT a.name AS 字段名, CASE WHEN EXISTS

(SELECT 1

FROM sysobjects

WHERE xtype = 'PK' AND parent_obj = a.id AND name IN

(SELECT name

FROM sysindexes

WHERE indid IN

(SELECT indid

FROM sysindexkeys

WHERE id = a.id AND colid = a.colid)))

THEN '1' ELSE '0' END AS 主键, CASE WHEN COLUMNPROPERTY(a.id, a.name,

'IsIdentity') = 1 THEN '1' ELSE '0' END AS 标识, b.name AS 类型,

a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度,

a.xscale AS 小数, a.isnullable AS 可空, ISNULL(e.text, '') AS 默认值, ISNULL(g.[value],

'') AS 字段说明

FROM syscolumns a LEFT OUTER 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 OUTER JOIN

syscomments e ON a.cdefault = e.id LEFT OUTER JOIN

sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN

sysproperties f ON d.id = f.id AND f.smallid = 0

WHERE (d.name = '表名称')

--2。SQL SERVER 2005

SELECT CASE WHEN EXISTS

(SELECT 1

FROM sysobjects

WHERE xtype = 'PK' AND parent_obj = a.id AND name IN

(SELECT name

FROM sysindexes

WHERE indid IN

(SELECT indid

FROM sysindexkeys

WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name,

'IsIdentity') = 1 THEN '1' ELSE '0' END AS 'identity', a.name AS ColName, c.name AS TypeName, a.length AS 'byte', COLUMNPROPERTY(a.id, a.name,

'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'

FROM sys.syscolumns AS a INNER JOIN

sys.sysobjects AS b ON a.id = b.id INNER JOIN

sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN

sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN

sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id

WHERE (b.name = 'keyfactory') AND (c.status <> '1')

--b.name = 'Keyfactory','Keyfactory'为你想要查找的数据表。

--2、SQL SERVER 2005

SELECT CASE WHEN EXISTS

(SELECT 1

FROM sysobjects

WHERE xtype = 'PK' AND parent_obj = a.id AND name IN

(SELECT name

FROM sysindexes

WHERE indid IN

(SELECT indid

FROM sysindexkeys

WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name,

'IsIdentity') = 1 THEN '1' ELSE '0' END AS 'identity', a.name AS ColName, c.name AS TypeName, a.length AS 'byte', COLUMNPROPERTY(a.id, a.name,

'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'

FROM sys.syscolumns AS a INNER JOIN

sys.sysobjects AS b ON a.id = b.id INNER JOIN

sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN

sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN

sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id

WHERE (b.name = 'keyfactory') AND (c.status <> '1')

--b.name = 'Keyfactory','Keyfactory'为你想要查找的数据表。

【获取SQL Server表字段的各种属性实例代码】相关文章:

SQL Server2005 中的数据类型总结

Sql Server中的视图介绍

SQL Server 临时表的删除

SQL Server各种日期计算方法

如何将SQL Server表驻留内存和检测

MDF文件在SQL Server中的恢复技术

SQLServer 设置单词首字母大写

SQLServer中的存储过程

推荐SQL Server 重新恢复自动编号列的序号的sql代码

SQL Server 大量数据的分页存储过程代码

精品推荐
分类导航