use below T-sql script you can get table property:
select obj.name as tables,
col.name as columns,
stype.name as type,
col.length as length,
nullif(col.xprec,0) as precesion,
col.scale as scale,
case col.isnullable
when 1 then 'yes'
else 'no'
end as allownull,
col.colstat as identy
from sysobjects obj,
syscolumns col,
systypes as stype
where col.id = obj.id
and obj.xtype = 'U'
and col.xtype = stype.xtype
and stype.xtype = stype.xusertype
--这个更详细一些,结果中:字段定义列包含了每个字段的简单定义,你再修改一下
select object_name(id) as 表名,c.name 字段名,t.name 字段类型名
,c.name +' '+t.name+
case when t.name in ('binary','char','nchar','nvarchar','varbinary','varchar')
then '('+cast(c.prec as varchar)+')'
when t.name in ('decimal','numeric')
then '('+cast(c.prec as varchar)+','+cast(c.scale as varchar)+')'
when t.name in ('bigint')
then ''
else '' end as 字段定义
from syscolumns c
inner join
systypes t
on c.xusertype=t.xusertype
where
c.status<>0x80
and c.name not in
(
'image','text','uniqueidentifier','sql_variant'
,'ntext','timestamp','sysname'
)
and objectproperty(id,N'IsUserTable')=1
比较麻烦,你参数这个自己做修改吧:
--得到所有用户表的表名,字段名,数据类型和长度
select object_name(id) as 表名
,c.name as 字段名
,t.name 数据类型
,c.prec as 长度
from syscolumns c
inner join
systypes t
on c.xusertype=t.xusertype
where objectproperty(id,'IsUserTable')=1