.Net常用技巧_获取SQL Server表字段的各种属性

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 = ‘表名称‘)

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

SELECT 
    表名       = case when a.colorder=1 then d.name else ‘‘ end,
    表说明     = case when a.colorder=1 then isnull(f.value,‘‘) 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 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 ‘√‘ 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.xusertype=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  
left join 

sys.extended_properties f
on 
    d.id=f.major_id and f.minor_id=0
where 
    d.name=‘Product‘    --如果只查询指定表,加上此条件
order by 
    a.id,a.colorder