怎么得到MSSQLSERVER数据库表的字段，类型，长度

发布网友 发布时间：2022-04-20 11:42

我来回答

共1个回答

热心网友 时间：2023-07-21 02:22

SQL 2005及以上版本，执行如下，可获取字段及相关属性值

SELECT a.name 字段名 ,
COLUMNPROPERTY(a.id, a.name, 'IsIdentity') 标识 ,
( CASE WHEN ( SELECT COUNT(*)
FROM sysobjects
WHERE ( name IN (
SELECT name
FROM sysindexes
WHERE ( id = a.id )
AND ( indid IN (
SELECT indid
FROM sysindexkeys
WHERE ( id = a.id )
AND ( colid IN (
SELECT
colid
FROM
syscolumns
WHERE
( id = a.id )
AND ( name = a.name ) ) ) ) ) ) )
AND ( xtype = 'PK' )
) > 0 THEN '1'
ELSE '0'
END ) 主键 ,
b.name 类型 ,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 ,
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数 ,
( CASE WHEN a.isnullable = 1 THEN '1'
ELSE '0'
END ) 允许空 ,
ISNULL(g.[value], '') AS 字段说明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = 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
WHERE d.name = 'AA_AppFile'
ORDER BY a.id ,
a.colorder

注：sql2000版本中，需要使用sysproperties来代替sys.extended_properties。