SQL查询表字段名称与字段类型、长度

select o.name as 表名, c.name as 字段名称, t.name as 字段类型, c.length as 字段长度
from
syscolumns c inner join
sysobjects o on c.id = o.id and o.xtype = 'u' inner join
systypes t on c.xtype = t.xtype

where o.name = '表名'

 

 

列出所有表：

 

SELECT (CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名
,a.colorder 字段序号
,a.name 字段名
,(
CASE
WHEN COLUMNPROPERTY(a.id ,a.name ,'IsIdentity')=1 THEN '√'
ELSE ''
END
) 标识
,(
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 '√'
ELSE ''
END
) 主键
,b.name 类型
,a.length 占用字节数
,COLUMNPROPERTY(a.id ,a.name ,'PRECISION') AS 长度
,ISNULL(COLUMNPROPERTY(a.id ,a.name ,'Scale') ,0) AS 小数位数
,(CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空
,ISNULL(e.text ,'') 默认值
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
ORDER BY
a.id
,a.colorder