Question

sql server 查询列名，列数据类型，列长度 sql语句怎么写

Answer 1

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。

Answer 2

seselect b.name as tablename,a.name as columnname,c.name as typename,a.max_length as typelength 

from sys.columns a inner join sys.tables b on b.object_id=a.object_id 

inner join sys.types c on c.system_type_id=a.system_type_id 

where b.name='表名'

order by b.name,a.column_id

Answer 3

列信息都在sys.all_columns表中，直接查询

select * from sys.all_columns

Answer 4

desc 表名