哈哈,大功告成create proc proc_design
as
begin
set nocount on
declare @myid int
declare @name varchar(256)
create table #tmp (tablename varchar(256),colname varchar(256),typename varchar(256),length int,isnullable int)
declare my_cur cursor for select name ,id from sysobjects where type ='U'
open my_cur
fetch next from my_cur into @name,@myid
while @@fetch_status = 0
begin
insert #tmp select @name,a.name ,b.name ,a.length, a.isnullable from syscolumns a, systypes b where a.xtype=b.xtype and a.id =@myid
fetch next from my_cur into @name,@myid
endclose my_cur
deallocate my_cur
select * from #tmp
set nocount off
end
as
begin
set nocount on
declare @myid int
declare @name varchar(256)
create table #tmp (tablename varchar(256),colname varchar(256),typename varchar(256),length int,isnullable int)
declare my_cur cursor for select name ,id from sysobjects where type ='U'
open my_cur
fetch next from my_cur into @name,@myid
while @@fetch_status = 0
begin
insert #tmp select @name,a.name ,b.name ,a.length, a.isnullable from syscolumns a, systypes b where a.xtype=b.xtype and a.id =@myid
fetch next from my_cur into @name,@myid
endclose my_cur
deallocate my_cur
select * from #tmp
set nocount off
end
CREATE VIEW dbo.tablestruct
ASselect 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.xusertypestep 2:use bcp TO a txt file