比较麻烦,你参数这个自己做修改吧: --得到所有用户表的表名,字段名,数据类型和长度 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
--这个更详细一些,结果中:字段定义列包含了每个字段的简单定义,你再修改一下 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
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 identyfrom sysobjects obj, syscolumns col, systypes as stypewhere 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 as 字段名
,t.name 数据类型
,c.prec as 长度
from syscolumns c
inner join
systypes t
on c.xusertype=t.xusertype
where objectproperty(id,'IsUserTable')=1
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
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 identyfrom sysobjects obj,
syscolumns col,
systypes as stypewhere col.id = obj.id
and obj.xtype = 'U'
and col.xtype = stype.xtype
and stype.xtype = stype.xusertype
楼上两位的办法都能够很好的将各个表的字段及其属性提取出来,但是
我还是不能将所选出的结果嵌套到alter table 语句里面。不知道这个里面能不能够用到类似vf里面的宏操作?