select d.name,a.name ,b.name ,a.length, a.isnullable from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
通过系统表来获得,显示所有用户的表结构: 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 as 字段名 ,t.name 数据类型 ,c.prec as 长度 from syscolumns c inner join systypes t on c.xusertype=t.xusertype where object_name(id)='表名' ----注意这里的表名 go
select d.name,a.name ,b.name ,a.length, a.isnullable from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='U' and id=object_id('表名')
更正一下:select d.name,a.name ,b.name ,a.length, a.isnullable from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='U' and b.id=object_id('表名')
select rtrim(b.name) as colname ,case when h.id is not null then 'PK' else '' end as primarykey ,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type ,b.length ,case b.isnullable when 0 then 'N' else 'Y' end as [isnull] ,isnull(e.text,'') as [default] ,isnull(c.value,'') as descript from sysobjects a,syscolumns b left outer join sysproperties c on b.id = c.id and b.colid = c.smallid left outer join syscomments e on b.cdefault = e.id left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid where a.id = b.id and a.id = object_id('titles') --tablename改成你要导出的表的名称 order by b.colid
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
如果我想指定一个表,我应该怎么写??谢谢
,c.name as 字段名
,t.name 数据类型
,c.prec as 长度
from syscolumns c
inner join
systypes t
on c.xusertype=t.xusertype
where object_name(id)='表名' ----注意这里的表名
go
and id=object_id('表名')
and b.id=object_id('表名')
,case when h.id is not null then 'PK' else '' end as primarykey
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as descript
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and a.id = object_id('titles') --tablename改成你要导出的表的名称
order by b.colid