转:http://blog.csdn.net/vivianfdlpw/archive/2005/10/17/506598.aspx
/**//*
--导出表结构脚本 --用法: --exec sp_ExportTables 'tb' --vivianfdlpw 2005.10 引用请保留此信息 
*/
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('sp_ExportTables') AND XTYPE='p')DROP PROCEDURE sp_ExportTablesGOCREATE PROCEDURE sp_ExportTables @table_name varchar(32)as beginCreate Table #CreateStatements (uid int identity(1,1),Info text)DECLARE @table_id int, @CurrColumn int, @MaxColumn int, @CreateStatement varchar(8000), @ColumnTypeName varchar(255), @uid int, @i int, @primary_key_field varchar(50) select @table_id=id from sysobjects where xtype='U' and [name] <> 'dtproperties' and [name] = @table_name select @primary_key_field = convert(varchar(32),c.name) from sysindexes i, syscolumns c, sysobjects o, syscolumns c1 where o.id = @table_id and o.id = c.id and o.id = i.id and (i.status & 0x800) = 0x800 and c.name = index_col (@table_name, i.indid, c1.colid) and c1.colid <= i.keycnt and c1.id = @table_id Select @CreateStatement = CHAR(13) + 'CREATE TABLE [' + [name] + '] ( ' from SYSOBJECTS WHERE ID=@TABLE_ID --循环列 Select @CurrColumn=Min(colid),@MaxColumn = Max(colid) from syscolumns where id= @table_id --Select * from syscolumns where id=1511676433 while @currColumn <= @MaxColumn begin --print @currColumn Declare @UQIndex int, @DefaultValue nvarchar(4000) set @DefaultValue = null select @DefaultValue=text from syscomments where id= (select constid from sysconstraints where id=@table_id and colid=@currColumn) --处理不同的列类型 SELECT @CreateStatement = @CreateStatement + CHAR(13) + '[' + [name] + '] ' + type_name(xtype) + case --ie numeric(10,2) WHEN type_name(xtype) IN ('decimal','numeric') THEN ' ('+ convert(varchar,prec) + ',' + convert(varchar,length) + ')' + case when autoval is null then '' else ' IDENTITY(1,1)' end + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END --ie float(53) WHEN type_name(xtype) IN ('float','real') THEN ' ('+ convert(varchar,prec) + ')' + case when autoval is null then '' else ' IDENTITY(1,1)' end + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END --ie varchar(40) WHEN type_name(xtype) IN ('char','varchar','nchar','nvarchar') THEN ' ('+ convert(varchar,length) + ')' + case when autoval is null then '' else ' IDENTITY(1,1)' end + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END --ie int ELSE + case when autoval is null then '' else ' IDENTITY(1,1)' end + CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END end --检测 'PRIMARY KEY' + CASE when syscolumns.name = @primary_key_field THEN ' PRIMARY KEY' else '' END + CASE when @DefaultValue is null then '' ELSE CASE WHEN type_name(xtype) IN ('decimal','numeric','float','real','bigint','int','smallint','tinyint','money','smallmoney') THEN ' DEFAULT ' + convert(varchar,@DefaultValue) ELSE ' DEFAULT ' + convert(varchar,@DefaultValue) END END + ',' from syscolumns where id=@table_id and colid=@CurrColumn Select @CurrColumn = @CurrColumn + 1 end insert into #CreateStatements(Info) values(@CreateStatement) SELECT @CreateStatement='' select @uid=@@IDENTITY --添加外键关系代码 declare @cursorID int declare c1 cursor for SELECT fkeyid from sysforeignkeys where fkeyid=@table_id open c1 fetch next from c1 into @cursorID SELECT @CreateStatement=@CreateStatement + (select + CHAR(13) +'FOREIGN KEY (' + [syscolumns].[name] + ') REFERENCES ' from syscolumns where id=fkeyid and colid =fkey) + (select (SELECT distinct [sysobjects].[name] from sysobjects where id=rkeyid) + '(' + [syscolumns].[name] + '),' from syscolumns where id=rkeyid and colid =rkey) from sysforeignkeys where fkeyid=@table_id close c1 deallocate c1 --添加UNIQUE约束代码 declare c1 cursor for select id from sysobjects where xtype='UQ' and parent_obj=@table_id open c1 fetch next from c1 into @cursorID while @@fetch_status >= 0 begin declare @indid smallint SELECT @indid = indid,@CreateStatement=@CreateStatement + CHAR(13) + 'CONSTRAINT ' + object_name(@cursorID) + ' UNIQUE ' + case when (status & 16)=16 then ' CLUSTERED' else ' NONCLUSTERED' end from sysindexes where name = object_name(@cursorID) and id = @table_ID declare @thiskey nvarchar(131), -- 128+3 @keys nvarchar(2126) -- a specific size for MS for whatever reason select @keys = index_col(@table_name, @indid, 1), @i = 2 if (indexkey_property(@table_id, @indid, 1, 'isdescending') = 1) select @keys = @keys + '(-)' select @thiskey = index_col(@table_name, @indid, @i) if ((@thiskey