-- USAGE: exec sp_GetDDL whatever1    
--   or   exec sp_GetDDL 'schemaname.tablename' 
--   or   exec sp_GetDDL '[schemaname].[table name]'      
--#############################################################################    
-- copyright 2004-2009 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.    
-- http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005.txt
-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask   
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,   
-- so I can learn from the things you've enhanced.The feedback you give will be what makes   
-- it worthwhile to me, and will be fed back to the SQL community.  
-- add this to your toolbox of helpful scripts.
--############################################################################# 
CREATE PROCEDURE [dbo].[sp_GetDDL]  
  @TBLNAME                VARCHAR(255)  
AS  
BEGIN  
  SET NOCOUNT ON  
  DECLARE  
    @MASTERSTATEMENTS       VARCHAR(max),  
    @CREATESTATEMENT        VARCHAR(max),  
    @COLSTATEMENT           VARCHAR(max),  
    @CALCULATEDCOL          VARCHAR(max),  
    @COLNAME                VARCHAR(255),  
    @COLCHECKCONSTRAINTS    VARCHAR(1000),  
    @DEFAULTVALUE           VARCHAR(255),  
    @PK_NAME                VARCHAR(255),  
    @PK_FIELD               VARCHAR(255),  
    @RULENAME               VARCHAR(255),  
    @RULESSTATEMENT         VARCHAR(max),  
    @TRIGGERSTATEMENT       VARCHAR(max),  
    @INDEXSTATEMENT         VARCHAR(max),  
    @COLUMNNAME             VARCHAR(255),  
    @SCHEMANAME             VARCHAR(255),
    @DEFAULTID              INT,  
    @RULESID                INT,  
    @TABLE_ID               INT,  
    @STRINGLEN              INT,  
    @CURRCOLUMN             INT,  
    @MAXCOLUMN              INT,  
    @CNSTID                 INT,  
    @I                      INT,  
    @CURSORID               INT,  
    @INDID                  INT ,  
    @MAXTABLELENGTH         INT,  
    @MAXINDEXLENGTH         INT,  
    @CNSTDES                NVARCHAR(max),-- STRING TO BUILD UP INDEX DESC  
    @CNSTNAME               SYSNAME,       -- NAME OF CONST. CURRENTLY UNDER CONSIDERATION  
    @CNSTTYPE               CHAR(2),  
    @THISKEY                NVARCHAR(131) ,  
    @KEYS                   NVARCHAR(2126)  --LENGTH (16*MAX_IDENTIFIERLENGTH)+(15*2)+(16*3)  
--##############################################################################  
-- INITIALIZE  
--##############################################################################  
  --does the tablename contain a schema?
  IF CHARINDEX('.',@TBLNAME) > 0
    BEGIN
      SELECT @SCHEMANAME = substring(@TBLNAME,1,CHARINDEX('.',@TBLNAME) - 1 ),
             @TBLNAME    = substring(@TBLNAME,CHARINDEX('.',@TBLNAME) + 1,125)
      SELECT 
        @TABLE_ID   = [id]  
      FROM sysobjects  
      WHERE [xtype]          =  'U'  
        AND [name]          <>  'dtproperties'  
        AND [name]           =  @TBLNAME
        AND [uid] =  schema_id(@SCHEMANAME) ;
    END
  ELSE
    BEGIN
      SELECT 
        @SCHEMANAME = ISNULL(schema_name(uid),'dbo') ,
        @TABLE_ID   = [id]  
      FROM sysobjects  
      WHERE [xtype] =  'U'  
        AND [name]  <> 'dtproperties'  
        AND [name]  =  @TBLNAME;
    END
    
--##############################################################################  
-- Check If TableName is Valid  
--##############################################################################  
  IF ISNULL(@TABLE_ID,0) = 0  
    BEGIN  
      SET @MASTERSTATEMENTS='Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database ' + db_name()  
      SELECT @MASTERSTATEMENTS;  
      RETURN 0  
    END  
--##############################################################################  
-- Valid Table, Continue Processing  
--##############################################################################  
--longest column name for formatting purposes.  
  SELECT  
    @STRINGLEN = MAX(LEN(syscolumns.[name])) + 1  
  FROM sysobjects   
    INNER JOIN syscolumns   
      ON  sysobjects.id = syscolumns.id  
      AND sysobjects.id = @TABLE_ID;  
--start building the string  
  SELECT  
    @CREATESTATEMENT = CHAR(13) + 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER([name]) + '] ( '  
  FROM sysobjects  
  WHERE id = @TABLE_ID;  
  
  SET @RULESSTATEMENT=''  
--##############################################################################  
--For Each Column in the table  
--##############################################################################  
  SELECT  
    @CURRCOLUMN = MIN(colid),  
    @MAXCOLUMN = MAX(colid)  
  FROM syscolumns  
  WHERE id = @TABLE_ID;  
  
  WHILE @CURRCOLUMN <= @MAXCOLUMN  
    BEGIN        SET @RULESSTATEMENT=''  
      SET @COLSTATEMENT = '' ;  
      SET @DEFAULTVALUE = NULL;  
--COLUMN LEVEL CHECK CONSTRAINT: can be multiple per column;  
      SET @COLCHECKCONSTRAINTS = ''  
--CRITICAL FIX...COLIDS CAN BE NONSEQUENTIAL IF COLUMNS ARE DROPPED AND RE-ADDED. GAPS IN THE SEQ MADE FOR BAD COLUMN DEFINITIONS.  
      IF EXISTS(SELECT  
                  NAME  
                FROM SYSCOLUMNS  
                WHERE ID  = @TABLE_ID  
                AND COLID = @CURRCOLUMN)  
        BEGIN  
          DECLARE TCURSOR CURSOR FOR  
            SELECT  
              id  
            FROM sysobjects  
            WHERE xtype = 'C'  
              AND parent_obj = @TABLE_ID  
              AND info = @CURRCOLUMN;  
--CHECK CONSTRAINTS  
            OPEN TCURSOR;  
            FETCH NEXT FROM TCURSOR INTO @I;  
            WHILE @@FETCH_STATUS > = 0  
              BEGIN  
                SELECT  
                  @COLCHECKCONSTRAINTS = @COLCHECKCONSTRAINTS   
                  + (SELECT   
                       ''  
                       + ' CHECK '   
                       +   CONVERT(VARCHAR(250),[syscomments].[text])   
                       + ' '  
                     FROM syscomments  
                     WHERE id = @I ) ;  
                FETCH NEXT FROM TCURSOR INTO @I;  
              END;  
          CLOSE TCURSOR;  
          DEALLOCATE TCURSOR;  
--CALCULATED COLUMN?  
          SELECT  
            @CALCULATEDCOL = ISNULL([text],'')  
          FROM syscomments  
          WHERE id = @TABLE_ID  
            AND [number] = @CURRCOLUMN ;  
          SET @CALCULATEDCOL = UPPER(@CALCULATEDCOL)  
--DEFAULT CONSTRAINT: can be only one per column  
          SELECT  
            @DEFAULTVALUE = ISNULL([text],'')  
          FROM syscomments  
          WHERE id = (SELECT  
                        cdefault  
                      FROM syscolumns  
                      WHERE id   = @TABLE_ID  
                        AND COLID = @CURRCOLUMN);  
--COLUMN DEFINITION  
          SELECT  
            @COLNAME = UPPER([name]),@COLSTATEMENT = @COLSTATEMENT   
            + CASE  
                WHEN ISCOMPUTED = 1   
                THEN CHAR(13)   
                     + '['   
                     + UPPER([name])    
                     + '] '   
                     + SPACE(@STRINGLEN - LEN(name))     
                     + 'AS ' + @CALCULATEDCOL  
                ELSE CHAR(13)   
                     + '['   
                     + UPPER([name])    
                     + '] '   
                     + SPACE(@STRINGLEN - LEN(name))   
                     + UPPER(TYPE_NAME(xusertype))   
                     + CASE  
--IE NUMERIC(10,2)  
                         WHEN TYPE_NAME(xusertype) IN ('decimal','numeric')   
                         THEN '('   
                              + CONVERT(VARCHAR,prec)   
                              + ','   
                              + CONVERT(VARCHAR,xscale)   
                              + ') '   
                              + SPACE(6 - LEN(CONVERT(VARCHAR,prec)   
                              + ','   
                              + CONVERT(VARCHAR,xscale)))   
                              + SPACE(7)   
                              + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                              + CASE  
                                  WHEN isnullable = 0   
                                  THEN ' NOT NULL'  
                                  ELSE '     NULL'  
                                END  
--IE FLOAT(53)  
                         WHEN  TYPE_NAME(xusertype) IN ('float','real')   
                         THEN   
                         --addition: if 53, no need to specifically say (53), otherwise display it  
                              CASE  
                                WHEN prec = 53   
                                THEN SPACE(11 - LEN(CONVERT(VARCHAR,prec)))   
                                     + SPACE(7)   
                                     + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                + CASE  
                                         WHEN isnullable = 0   
                                         THEN ' NOT NULL'  
                                         ELSE '     NULL'  
                                       END  
                                ELSE '('  
                                     + CONVERT(VARCHAR,prec)   
                                     + ') '   
                                     + SPACE(6 - LEN(CONVERT(VARCHAR,prec)))   
                                     + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                                     + CASE  
                                         WHEN isnullable = 0   
                                         THEN ' NOT NULL'  
                                         ELSE '     NULL'  
                                       END  
                              END  
--ie VARCHAR(40)  
                         WHEN  TYPE_NAME(xusertype) IN ('char','varchar')   
                         THEN CASE  
                                WHEN  length = -1   
                                THEN  '(max)'   
                                      + SPACE(6 - LEN(CONVERT(VARCHAR,length)))   
                                      + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                                      + CASE  
                                          WHEN isnullable = 0   
                                          THEN ' NOT NULL'  
                                          ELSE '     NULL'  
                                        END  
                                ELSE '('  
                                     + CONVERT(VARCHAR,length)   
                                     + ') '   
                                     + SPACE(6 - LEN(CONVERT(VARCHAR,length)))   
                                     + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                                     + CASE  
                                         WHEN isnullable = 0   
                                         THEN ' NOT NULL'  
                                         ELSE '     NULL'  
                                       END  
                              END  
                         WHEN TYPE_NAME(xusertype) IN ('nchar','nvarchar')   
                         THEN CASE  
                                WHEN  prec = -1   
                                THEN '(max)'   
                                     + SPACE(6 - LEN(CONVERT(VARCHAR,prec)))   
                                     + SPACE(7)   
                                     + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                                     + CASE  
                                         WHEN isnullable = 0   
                                         THEN  ' NOT NULL'  
                                         ELSE '     NULL'  
                                       END  
                                ELSE '('  
                                     + CONVERT(VARCHAR,prec)   
                                     + ') '   
                                     + SPACE(6 - LEN(CONVERT(VARCHAR,prec)))   
                                     + SPACE(7)   
                                     + SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                                     + CASE  
                                         WHEN isnullable = 0   
                                         THEN ' NOT NULL'  
                                         ELSE '     NULL'  
                                       END  
                              END  
                         WHEN TYPE_NAME(xusertype) IN ('datetime','money','text','image')   
                         THEN SPACE(18 - LEN(TYPE_NAME(xusertype)))   
                              + '              '   
                              + CASE  
                                  WHEN isnullable = 0   
                                  THEN ' NOT NULL'  
                                  ELSE '     NULL'  
                                END  
--IE INT  
                 ELSE SPACE(16 - LEN(TYPE_NAME(xusertype)))   
                              + CASE  
                                  WHEN COLUMNPROPERTY ( @TABLE_ID , name , 'IsIdentity' ) = 0     
                                  THEN '              '  
                                  ELSE ' IDENTITY('   
                                       + CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )   
                                       + ','   
                                       + CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )   
                                       + ')'  
                                END  
                              + SPACE(2)   
                              + CASE  
                                  WHEN isnullable = 0   
                                  THEN ' NOT NULL'  
                                  ELSE '     NULL'  
                                END  
                       END   
                     + CASE  
                         WHEN @DEFAULTVALUE IS NULL   
                         THEN '' + @COLCHECKCONSTRAINTS  
                         ELSE CASE  
                                WHEN TYPE_NAME(xusertype) IN ('decimal','numeric','float','real','bigint','int','smallint','tinyint','money','smallmoney')   
                                THEN ' DEFAULT '   
                                     + CONVERT(VARCHAR,@DEFAULTVALUE)   
                                     + @COLCHECKCONSTRAINTS  
                                --i thought it needed to be handled differently! NOT!  
                                ELSE ' DEFAULT '   
                                     + CONVERT(VARCHAR,@DEFAULTVALUE)   
                                     + @COLCHECKCONSTRAINTS  
                              END  
                       END  
              END --iscomputed  
            + ','  
          FROM syscolumns  
          WHERE id    = @TABLE_ID  
            AND colid = @CURRCOLUMN;  
--build the column substring here in the middle of the loop  
          SELECT  
            @CREATESTATEMENT = @CREATESTATEMENT   
            + @COLSTATEMENT;  
--new section  check if column is bound to a SQL server Rule  
          SELECT  
            @RULESID = domain  
          FROM syscolumns  
          WHERE domain > 0  
            AND id     = @TABLE_ID  
            AND colid  = @CURRCOLUMN  
          IF @RULESID > 0  
            BEGIN  
              SELECT  
                @RULENAME = name  
              FROM sysobjects  
              WHERE id=@RULESID  
                
              SELECT  
                @RULESSTATEMENT = @RULESSTATEMENT   
                + char(13)   
                + 'if not exists(SELECT  NAME FROM SYSOBJECTS WHERE XTYPE=''R'' AND NAME = ''' + @RULENAME +''')'   
                + char(13)   
                + TEXT  
              FROM SYSCOMMENTS  
              WHERE ID = @RULESID  
                
              SELECT  
                @RULESSTATEMENT = @RULESSTATEMENT   
                + CHAR(13) + 'EXEC sp_binderule '   
                + @RULENAME   
                + ',''[' + @TBLNAME +'].[' + @COLNAME + ']'''   
                + char(13)  
            END;  
        END; --IF EXISTS  
      SELECT  
        @CURRCOLUMN = @CURRCOLUMN + 1;  
    END; --COLUMN LOOP  
  SET @MASTERSTATEMENTS = @CREATESTATEMENT;  
--##############################################################################  
--TRIGGERS?  
--##############################################################################  
  SET @TRIGGERSTATEMENT = ''  
  SELECT  
    @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + [text] + CHAR(13) + 'GO'  
  FROM syscomments  
  WHERE id IN(SELECT  
                id  
              FROM sysobjects  
              WHERE xtype      = 'TR'  
                AND parent_obj = @TABLE_ID)  
--##############################################################################  
--GET PRIMARY KEY'S NAME DEFINE BY USER  
--##############################################################################  
  SELECT  
    @PK_NAME = S2.name  
  FROM sysobjects S1   
    INNER JOIN sysobjects S2   
      ON S1.id   = S2.parent_obj  
  WHERE S1.id    = @TABLE_ID  
    AND S2.xtype = 'PK'  
--CODE TO ADD ALL THE PRIMARY KEYS TO THE BOTTOM OF THE STATEMENT  
  SELECT  
    @CREATESTATEMENT = '' ;  
  DECLARE TCURSOR CURSOR FOR  
    SELECT  
      CONVERT(nVARCHAR(100),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 (@TBLNAME, i.indid, c1.colid)  
      AND C1.colid < = I.keycnt  
      AND C1.id = @TABLE_ID;  
    SET @I = 0;  
    OPEN TCURSOR;  
    FETCH NEXT FROM TCURSOR INTO @PK_FIELD;  
    IF (@@FETCH_STATUS > = 0)  
      SELECT  
        @CREATESTATEMENT = @CREATESTATEMENT   
        + char(13)   
        + 'CONSTRAINT ['   
        + UPPER(@PK_Name)   
        + '] PRIMARY KEY ('  
    WHILE @@FETCH_STATUS > = 0  
      BEGIN  
        IF (@I <> 0)  
          SELECT  
            @CREATESTATEMENT = @CREATESTATEMENT + ',' ;  
        SELECT  
          @CREATESTATEMENT = @CREATESTATEMENT   
          + '['   
          + UPPER( @PK_FIELD)   
          + ']' ;  
        SET @I = @I + 1  
        FETCH NEXT FROM TCURSOR INTO @PK_FIELD;  
      END;  
--simple text cleanup  
    IF(@I>0)  
      SELECT  
        @CREATESTATEMENT = @CREATESTATEMENT + '),' ;  
  CLOSE TCURSOR;  
  DEALLOCATE TCURSOR;  
  
  SELECT  
    @MASTERSTATEMENTS = @MASTERSTATEMENTS + @CREATESTATEMENT;  
--##############################################################################  
-- CODE TO ADD ALL UNIQUE CONSTRAINTS  
--##############################################################################  
  DECLARE TCURSOR CURSOR LOCAL STATIC FOR  
    SELECT  
      id, xtype, name  
    FROM sysobjects  
    WHERE parent_obj = @TABLE_ID  
      AND xtype = 'UQ' FOR READ ONLY;  
    OPEN TCURSOR;  
    FETCH TCURSOR INTO @CNSTID ,@CNSTTYPE ,@CNSTNAME;  
    WHILE @@FETCH_STATUS > = 0  
      BEGIN  
        BEGIN  
-- get indid and index description  
          SELECT  
            @INDID = indid,  
            @CNSTDES = CASE  
                         WHEN @CNSTTYPE = 'PK'   
                         THEN 'PRIMARY KEY'  
                         ELSE 'UNIQUE'  
                       END   
            + CASE  
                WHEN (status & 16) = 16   
                THEN ' (CLUSTERED)'  
                ELSE ' (NON-CLUSTERED)'  
                END  
          FROM sysindexes  
          WHERE name = OBJECT_NAME(@CNSTID)  
            AND id   = @TABLE_ID;  
-- Format keys string  
          SELECT  
            @KEYS = INDEX_COL(@TBLNAME, @INDID, 1),   
            @I = 2  
          IF (INDEXKEY_PROPERTY(@TABLE_ID, @INDID, 1, 'isdescending') = 1)  
            SELECT  
              @KEYS = @KEYS  + '(-)' ;  
          SELECT  
            @THISKEY = INDEX_COL(@TBLNAME, @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(@TBLNAME, @INDID, @I) ;  
              IF ((@THISKEY IS NOT NULL) AND (INDEXKEY_PROPERTY(@TABLE_ID, @INDID, @I, 'isdescending') = 1))  
                SELECT  
                  @THISKEY = @THISKEY + '(-)' ;  
        END;  
        SELECT  
          @CREATESTATEMENT = @CREATESTATEMENT   
            + CHAR(13)   
            + ','   
            + @CNSTDES   
            +  @CNSTNAME   
            + '(' + @KEYS   
            + ')' ;  
      END;  
      FETCH TCURSOR INTO @CNSTID ,@CNSTTYPE ,@CNSTNAME;  
    END;  
  CLOSE TCURSOR;  
  DEALLOCATE TCURSOR;  
--##############################################################################  
--CODE TO ADD ALL THE FOREIGN KEYS TO THE BOTTOM OF THE STATEMENT  
--##############################################################################  
  SELECT  
    @CREATESTATEMENT = '' ;  
  SELECT  
    @STRINGLEN = MAX(LEN(syscolumns.name)) + 1  
  FROM syscolumns   
    INNER JOIN sysobjects   
      ON  sysobjects.id = syscolumns.id  
      AND sysobjects.id = @TABLE_ID;  
  DECLARE TCURSOR CURSOR FOR  
    SELECT  
      fkeyid  
    FROM sysforeignkeys  
    WHERE fkeyid = @TABLE_ID;  
    OPEN TCURSOR;  
    FETCH NEXT FROM TCURSOR INTO @CURSORID;  
    SELECT @CREATESTATEMENT = @CREATESTATEMENT   
           + (SELECT  
                CHAR(13)   
                + 'FOREIGN KEY ('   
                +   [syscolumns].[name]   
                + ')' + SPACE(@STRINGLEN - LEN([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 TCURSOR;  
 DEALLOCATE TCURSOR;  
--##############################################################################  
--CODE TO ADD ALL THE UNIQUE CONSTRAINTS TO THE BOTTOM OF THE DEFINITION.  
--##############################################################################  
  DECLARE TCURSOR CURSOR FOR  
    SELECT  
      id  
    FROM sysobjects  
    WHERE xtype      = 'UQ'  
      AND parent_obj = @TABLE_ID;  
    OPEN TCURSOR;  
    FETCH NEXT FROM TCURSOR INTO @CURSORID;  
--adapted shamelessly from sp_helpconstraints  
    WHILE @@fetch_status > = 0  
      BEGIN  
        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;  
        SELECT  
          @KEYS = INDEX_COL(@TBLNAME, @INDID, 1), @I = 2  
        IF (INDEXKEY_PROPERTY(@TABLE_ID, @INDID, 1, 'isdescending') = 1)  
          SELECT  
            @KEYS = @KEYS  + '(-)'  
        SELECT  
          @THISKEY = INDEX_COL(@TBLNAME, @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(@TBLNAME, @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 TCURSOR INTO @CURSORID;  
      END;  
  CLOSE TCURSOR;  
  DEALLOCATE TCURSOR;  
--##############################################################################  
--CODE TO ADD INDEXES TO THE BOTTOM OF THE DEFINITION?  
--##############################################################################  
  DECLARE @INDEXES TABLE(  
    COLNAMES        VARCHAR(max),  
    TABLENAME       VARCHAR(128),  
    TABLEID         INT,  
    INDEXID         INT,  
    INDEXNAME       VARCHAR(128),  
    STATUS          INT,  
    ISUNIQUE        INT,  
    ISCLUSTERED     INT,  
    INDEXFILLFACTOR INT )  
  INSERT INTO @INDEXES  
    SELECT  
      REPLICATE(' ',8000) AS COLNAMES ,  
      OBJECT_NAME(I.ID) AS TABLENAME,  
      I.ID AS TABLEID,  
      I.INDID AS INDEXID,  
      I.NAME AS INDEXNAME,  
      I.STATUS,  
      INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,  
      INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,  
      INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR  
    FROM SYSINDEXES I  
    WHERE I.INDID > 0  
      AND I.INDID < 255  
      AND (I.STATUS & 64)=0  
      AND I.ID = @TABLE_ID  
      AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0    --already have unique indexes  
      AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0 --already have clustered/pk's indexes  
--USED FOR FORMATTING ONLY  
  SELECT  
    @MAXTABLELENGTH=MAX(LEN(TABLENAME))  
  FROM @INDEXES  
  SELECT  
    @MAXINDEXLENGTH=MAX(LEN(INDEXNAME))  
  FROM @INDEXES  
  DECLARE C1 CURSOR FOR  
    SELECT  
      TABLEID,INDEXID  
    FROM @INDEXES  
    OPEN C1  
    FETCH NEXT FROM C1 INTO @CNSTID,@INDID  
    WHILE @@FETCH_STATUS <> -1  
      BEGIN  
        SET @INDEXSTATEMENT = ''  
        SELECT  
          @INDEXSTATEMENT=@INDEXSTATEMENT + ISNULL(SYSCOLUMNS.NAME,'') + ','  
        FROM SYSINDEXES I  
          INNER JOIN SYSINDEXKEYS   
            ON  I.ID=SYSINDEXKEYS.ID  
            AND I.INDID = SYSINDEXKEYS.INDID  
          INNER JOIN SYSCOLUMNS   
            ON  SYSINDEXKEYS.ID = SYSCOLUMNS.ID  
            AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID  
        WHERE I.INDID > 0  
          AND I.INDID < 255  
          AND (I.STATUS & 64)=0  
          AND I.ID=@CNSTID  
          AND I.INDID=@INDID  
        ORDER BY SYSCOLUMNS.COLID  
        UPDATE @INDEXES   
          SET COLNAMES=@INDEXSTATEMENT  
        WHERE TABLEID=@CNSTID  
          AND INDEXID=@INDID  
        FETCH NEXT FROM C1 INTO @CNSTID,@INDID  
      END  
  CLOSE C1  
  DEALLOCATE C1  
--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA  
  UPDATE @INDEXES SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)  
  SET @INDEXSTATEMENT = ''  
  SELECT  
    @INDEXSTATEMENT = @INDEXSTATEMENT  
    + 'CREATE '  
    + CASE  
        WHEN ISUNIQUE = 1   
        THEN ' UNIQUE '  
        ELSE ' '  
      END  
    + CASE  
        WHEN ISCLUSTERED = 1   
        THEN ' CLUSTERED '  
        ELSE ' '  
      END  
    + ' INDEX ['   
    + UPPER(INDEXNAME)   
    + ']'  
    + SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))  
    +' ON [' + UPPER(TABLENAME) + '] '  
    + SPACE(@MAXTABLELENGTH - LEN(TABLENAME))  
    + '(' + UPPER(COLNAMES) + ')'  
    + CASE  
        WHEN INDEXFILLFACTOR = 0   
        THEN ''  
        ELSE ' WITH FILLFACTOR = '   
             + CONVERT(VARCHAR(10),INDEXFILLFACTOR)  
      END   
    + CHAR(13) --AS SQL  
  FROM @INDEXES  
--##############################################################################  
--FINAL CLEANUP AND PRESENTATION  
--##############################################################################  
--at this point, there is a trailing comma, or it blank  
  SET @MASTERSTATEMENTS = @MASTERSTATEMENTS + @CREATESTATEMENT;  
--note that this trims the trailing comma from the end of the statement  
  SET @MASTERSTATEMENTS = SUBSTRING(@MASTERSTATEMENTS,1,LEN(@MASTERSTATEMENTS) -1) ;  
  SET @MASTERSTATEMENTS = @MASTERSTATEMENTS + ')'+ CHAR(13) ;  
--update ##Hierarchy set SQL = @info  
--where [Name] = @TblName  
  SET @MASTERSTATEMENTS = @MASTERSTATEMENTS    
      + CHAR(13)   
      + @INDEXSTATEMENT   
      + CHAR(13)   
      + @RULESSTATEMENT   
      + CHAR(13)   
      +'GO'   
      + CHAR(13)   
      +  @TRIGGERSTATEMENT;  
  
  SELECT @MASTERSTATEMENTS;  
END