获取SQL Server表字段的各种属性实例代码

时间:2021-05-02

复制代码 代码如下:


-- 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'为你想要查找的数据表。

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章