/* (c) Gordon S. Linoff of Data Miners, Inc.
 * February 2010
 * gordon@data-miners.com
 * http://www.data-miners.com.
 */
 
/* @sql is a temporary table that stores the command lines. */
DECLARE @sql table(s varchar(1000), id int identity)

/* @EXCLUSIONLIST is a list of tables not included in the commands.
 * Note that valid tables start and end with vertical bars.
 */
DECLARE @EXCLUSIONLIST varchar(1000)
SET @EXCLUSIONLIST = '|sysdiagrams|aaadum|'

/* First, delete the tables */
INSERT INTO @sql(s)
SELECT (CASE WHEN rownum = 1 THEN 'IF EXISTS (SELECT 1 FROM sys.tables WHERE name = '''+name+''')'
             WHEN rownum = 2 THEN '    DROP TABLE '+name
             WHEN rownum = 3 THEN 'GO'
             WHEN rownum = 4 THEN ''
        END)
FROM (SELECT * FROM sys.tables WHERE CHARINDEX('|'+name+'|', @EXCLUSIONLIST) = 0 ) t CROSS JOIN
     (SELECT 1 as rownum UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) r
ORDER BY name, rownum
 
/* Next, create the tables */
INSERT INTO @sql(s)
SELECT (CASE WHEN rownum = 1 THEN 'CREATE TABLE ['+a.t_name+'] ('
             WHEN c.column_id IS NOT NULL
             THEN '    ['+c.c_name+'] ' +
                  (CASE WHEN CHARINDEX('CHAR', datatype) > 0 THEN datatype+'('+cast(length as varchar)+')'
                        WHEN CHARINDEX('BINARY', datatype) > 0 THEN datatype+'('+cast(length as varchar)+')'
                        WHEN datatype = 'float' AND precision <> 24 THEN datatype+'('+cast(precision as varchar)+')'
                        WHEN datatype IN ('numeric', 'decimal') AND scale = 0 THEN datatype+'('+cast(precision as varchar)+')'
                        WHEN datatype IN ('numeric', 'decimal') AND scale > 0 THEN datatype+'('+cast(precision as varchar)+','+cast(scale as varchar)+')'
                        ELSE datatype END)+' '+
                  (CASE WHEN c.identity_seed IS NOT NULL
                        THEN 'IDENTITY(' + CAST(identity_seed AS VARCHAR) + ',' + CAST(identity_increment AS VARCHAR) + ') '
                        ELSE '' END) +
                  (CASE WHEN c.is_nullable = 0 THEN 'NOT NULL ' ELSE '' END) +
                  (CASE WHEN c.default_definition IS NOT NULL
                        THEN 'DEFAULT '+default_definition ELSE '' END) +
                  (CASE WHEN max_column_id = column_id AND pk.pk_name IS NULL THEN '' ELSE ',' END)
            WHEN rownum = max_column_id + 2 and pk.pk_name IS NOT NULL
            THEN '    PRIMARY KEY ('+pk.pk_columns+')'
            WHEN rownum = max_column_id + 3 THEN ')  /* CREATE TABLE '+a.t_name+' */'
            WHEN rownum = max_column_id + 4 THEN 'GO'
            WHEN rownum = max_column_id + 5 THEN ''
        END)
 FROM (SELECT t.t_name, rownum, max_column_id
       FROM (SELECT t.name as t_name, MAX(c.column_id) as max_column_id
             FROM sys.columns c join
                  (SELECT * FROM sys.tables WHERE CHARINDEX('|'+name+'|', @EXCLUSIONLIST) = 0 ) t 
                  ON c.object_id = t.object_id
             GROUP BY t.name) t join
            (SELECT ROW_NUMBER() OVER (ORDER BY object_id) as rownum FROM sys.columns c) ctr
            ON ctr.rownum <= t.max_column_id + 5
      ) a LEFT OUTER JOIN
      (SELECT t.name as t_name, c.column_id, c.name AS c_name, u.name as datatype,
              ISNULL(baset.name, N'') AS systemtype,
              CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND c.max_length <> -1
                        THEN c.max_length/2 ELSE c.max_length END AS INT) AS length,
              c.precision AS precision,
              c.scale as scale,
              c.is_nullable,
              dc.definition as default_definition,
              idc.seed_value as identity_seed, idc.increment_value as identity_increment
       FROM sys.tables t JOIN
            sys.all_columns AS c
            ON c.object_id = t.object_id LEFT OUTER JOIN
            sys.types u
            ON u.user_type_id = c.user_type_id LEFT OUTER JOIN
            sys.types baset
            ON baset.user_type_id = c.system_type_id AND 
               baset.user_type_id = baset.system_type_id LEFT OUTER JOIN
            sys.default_constraints dc
            ON c.object_id = dc.parent_object_id AND
               c.column_id = dc.parent_column_id LEFT OUTER JOIN
            sys.identity_columns idc
            ON c.object_id = idc.object_id AND
               c.column_id = idc.column_id
      ) c
      ON a.t_name = c.t_name AND
         c.column_id + 1 = a.rownum LEFT OUTER JOIN
      (SELECT t.name as t_name, kc.name as pk_name, 
              (MAX(CASE WHEN index_column_id = 1 THEN '['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 2 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 3 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 4 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 5 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 6 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 7 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 8 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 9 THEN ','+'['+c.name+']' ELSE '' END) +
               MAX(CASE WHEN index_column_id = 10 THEN ','+'['+c.name+']' ELSE '' END)
              ) as pk_columns
       FROM sys.indexes i JOIN
            sys.key_constraints kc
            ON i.name = kc.name AND
               kc.type = 'PK' JOIN
            sys.tables t
            ON i.object_id = t.object_id JOIN
            sys.index_columns ic
            ON i.object_id = ic.object_id AND
               i.index_id = ic.index_id JOIN
            sys.columns c
            ON ic.index_column_id = c.column_id AND
               ic.object_id = c.object_id
       GROUP BY t.name, kc.name
      ) pk
      ON pk.t_name = a.t_name 
ORDER BY a.t_name, rownum



/* Now add in foreign key constraints */
INSERT INTO @sql
SELECT (CASE WHEN rownum = 1 THEN 'ALTER TABLE ['+t_name+'] WITH CHECK ADD CONSTRAINT ['+fk_name+'] FOREIGN KEY(['+ref_c_name+']) REFERENCES ['+ref_t_name+'] (['+ref_c_name+'])'
             WHEN rownum = 2 THEN 'GO'
             WHEN rownum = 3 THEN 'ALTER TABLE ['+t_name+'] CHECK CONSTRAINT ['+fk_name+']'
             WHEN rownum = 4 THEN 'GO'
        END)
FROM (SELECT rownum, fk.name as fk_name, t.name as t_name, c.name as c_name, ref_t.name as ref_t_name, ref_c.name as ref_c_name
      FROM sys.foreign_keys fk JOIN
           sys.foreign_key_columns fkc
           ON fk.object_id = fkc.constraint_object_id JOIN
           (SELECT * FROM sys.tables WHERE CHARINDEX('|'+name+'|', @EXCLUSIONLIST) = 0 ) t
           ON fkc.parent_object_id = t.object_id JOIN
           sys.columns c
           ON c.object_id = t.object_id AND
              c.column_id = fkc.parent_column_id JOIN
           sys.tables ref_t
           ON fkc.referenced_object_id = ref_t.object_id JOIN
           sys.columns ref_c
           on ref_c.object_id = ref_t.object_id and
              ref_c.column_id = fkc.referenced_column_id
           CROSS JOIN
           (SELECT 1 as rownum UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) b
      WHERE fk.type = 'F'
     ) a
ORDER BY t_name, fk_name


INSERT INTO @sql
SELECT (CASE WHEN index_column_id = 1
             THEN 'CREATE '+(CASE WHEN is_unique = 0 THEN '' ELSE 'UNIQUE ' END)+
                  'INDEX ['+i_name+'] ON ['+t_name+'](['+c_name +'] ' +
                  (CASE WHEN is_descending_key = 1 THEN 'DESC ' ELSE 'ASC ' END)+
                  (CASE WHEN is_last_column = 1 THEN ')' ELSE '' END)
             ELSE '    , ['+c_name +'] ' +
                  (CASE WHEN is_descending_key = 1 THEN 'DESC ' ELSE 'ASC ' END)+
                  (CASE WHEN is_last_column = 1 THEN ')' ELSE '' END)
        END)
FROM (SELECT i.name as i_name, t.name as t_name, i.index_id, c.name as c_name, index_column_id, is_descending_key, is_unique,
             (CASE WHEN MAX(index_column_id) OVER (PARTITION BY t.name, i.name) = index_column_id THEN 1 ELSE 0 END) as is_last_column
      FROM sys.indexes i JOIN
           (SELECT * FROM sys.tables WHERE CHARINDEX('|'+name+'|', @EXCLUSIONLIST) = 0 ) t
           ON i.object_id = t.object_id AND
              i.is_primary_key = 0 JOIN
           sys.index_columns ic
           ON i.object_id = ic.object_id AND
              i.index_id = ic.index_id JOIN
           sys.columns c
           ON ic.object_id = c.object_id AND
              ic.column_id = c.column_id
     ) a
ORDER BY t_name, i_name, index_column_id


-- result!
SELECT s
FROM @sql
WHERE s IS NOT NULL
ORDER BY id