查询数据库表的列字段、字段类型、字段长度、是否为空

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 'Y' ELSE 'N' 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

where d.name in ('DT_BLOG_CP'
,'mst_udc')

ORDER BY
d.name,
a.id
,a.colorder

 

 

 

select b.name tablename ,a.name as columnname,
(CASE WHEN b.isnullable=1 THEN '√' ELSE '' END) as isemp,
c.name typename,
a.max_length,
a.precision,
a.scale
from sys.columns a,
sys.objects b,
sys.types c
where a.object_id=b.object_id
and a.user_type_id=c.user_type_id
and b.type='u'

and b.name in ('DT_BLOG_CP','DT_BOOK')
--and c.name in('varchar','nvarchar','char','nchar','text','ntext')
--and object_name(a.object_id)<>'t'

order by b.name