T-SQL to Export Table Structure to a script

CREATE PROCEDURE sp_ExportTables 
-- USAGE: sp_ExportTables gmproj
@table_namevarchar(32)
as 
begin

Create Table #CreateStatements(uid int identity(1,1),Infotext)

DECLARE @table_id int,
@CurrColumnint,
@MaxColumnint,
@CreateStatementvarchar(8000),
@ColumnTypeNamevarchar(255),
@uidint,
@iint,
@primary_key_field varchar(50)

select @table_id=id from sysobjects where xtype='U' and [name] <> 'dtproperties' and [name] = @table_name
/*Since a table can have only one Primary key, get the column name for this table(if any) that is the PK*/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
--For Each Column
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)

--Process different Column Types differently
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
--code to determine if '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

--CODE TO ADD ALL THE FOREIGN KEYS TO THE BOTTOM OF THE STATEMENT
declare @cursorIDint
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
--CODE TO ADD ALL THE UNIQUE CONSTRAINTS TO THE BOTTOM OF THE DEFINITION.
declare c1 cursor for select id from sysobjects where xtype='UQ' and parent_obj=@table_id
open c1
fetch next from c1 into @cursorID
--adapted shamelessly from sp_helpconstraints
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
fromsysindexes
wherename = 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 is not null) and (indexkey_property(@table_ID, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null)
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@table_name, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@table_ID, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end
Select @CreateStatement=@CreateStatement + '(' + @keys + '),'
fetch next from c1 into @cursorID
end
close c1
deallocate c1
--CODE TO ADD CHECK CONSTRAINTS TO THE BOTTOM OF THE DEFINITION?

--CODE TO ADD INDEXES TO THE BOTTOM OF THE DEFINITION?

--at this point, there is a trailing comma, or it blank
DECLARE @ptrval binary(16),@txtlen INT
if len(@CreateStatement) > 0
BEGIN
SELECT @ptrval = TEXTPTR(info) ,
@txtlen = DATALENGTH(info)
   FROM #CreateStatements
      WHERE uid=@uid
UPDATETEXT #CreateStatements.info @ptrval @txtlen 0 @CreateStatement
END

--note that this trims the trailing comma from the end of the statement
SELECT @ptrval = TEXTPTR(info) ,
@txtlen = DATALENGTH(info) - 1
   FROM #CreateStatements
      WHERE uid=@uid

SELECT @CreateStatement= ')'+ CHAR(13)     
UPDATETEXT #CreateStatements.info @ptrval @txtlen 1 @CreateStatement

Select info from #CreateStatements
drop table #CreateStatements
end