-- --
 --exec Up_ExportTables 'Customers'
-- When you want to export a table structure to T-SQL, everyone always points you to Enterprise Manager, or a script that calls BCP.
-- Sometimes, a developer either doesn't have access to those tools, or just doesn't want to use them (like me) out of spite.
-- I made this T-SQL because it was educational, and noone else seems to have done it yet.
-- ideally, with this script AND the sp_generate_inserts script from http://vyaskn.tripod.com/code.htm, you could use a loop to export both the definition of a table, And then it's contents.
-- please, if you take this script and make it better, please email me a copy at lowell@stormrage.com so I can learn from your improvements.
--
-- Best to view the results in Query Analyzer Results in Text (Control +T)
--
-- Known limitations at this time:
-- 1.Doesn't add the check constraint's yet, only the implied PK AND Unique Constraints.
-- 2.Doesn't add the indexes for the table yet.
-- 3. Completely ignores the optional COLLATE statement because I never use it when creating a varchar; ie  COLLATE SQL_Latin1_General_CP1_CI_AS
 
CREATE PROCEDURE Up_ExportTables
-- USAGE: sp_ExportTables gmproj
@table_name    varchar(32)
as
begin

Create 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
    /*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 @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
        --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
                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 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