USE [DatabaseNameGoesHere]


;
WITH    cteFk
          AS ( SELECT   a.parent_object_id AS fkTableID
                      , a.object_id AS fkID
                      , a.name AS foreignKeyName
                      , c.column_id AS fkColumnID
                      , c.name AS fkColName
                      , b.referenced_object_id AS pkTableID
                      , d.column_id AS pkColumnID
                      , d.name AS pkColName
                      , delete_referential_action
                      , update_referential_action
                      , is_not_for_replication
               FROM     sys.foreign_keys a
                        JOIN sys.foreign_key_columns b
                            ON a.object_id = b.constraint_object_id
                        JOIN sys.columns c
                            ON b.parent_column_id = c.column_id
                               AND a.parent_object_id = c.object_id
                        JOIN sys.columns d
                            ON b.referenced_column_id = d.column_id
                               AND a.referenced_object_id = d.object_id
               WHERE    OBJECT_NAME(b.referenced_object_id) IN ( SELECT name
                                                                 FROM   sys.tables )
             )
    SELECT DISTINCT
            SCHEMA_NAME(tblColumns.Schema_ID) AS schemaName
          , tblColumns.tableName
          , tblColumns.columnName
          , tblColumns.dataType
          , tblColumns.max_length AS maxLegth
          , tblColumns.precision
          , tblColumns.scale
          , tblColumns.collation_name AS [collationName]
          , tblColumns.is_identity AS isIdenty
          , tblColumns.is_nullable AS isNullable
          , CASE WHEN tblForeignKeys.fkTableID IS NULL THEN 0
                 ELSE 1
            END AS isFkTable
          , CASE WHEN tblForeignKeys.fkColumnID IS NULL THEN 0
                 ELSE 1
            END AS isFkColumn
          , CASE WHEN tblPrimaryKeys.pkTableID IS NULL THEN 0
                 ELSE 1
            END AS isPkTable
          , CASE WHEN tblPrimaryKeys.pkColumnID IS NULL THEN 0
                 ELSE 1
            END AS isPkColumn
          , tblPrimaryKeys.ord AS colOrderInPK
          , CASE WHEN tblIndexes.columnID IS NULL THEN 0
                 ELSE 1
            END AS isIndexedColumn
          , tblIndexes.IndexName
          , tblindexes.indexType
          , tblindexes.is_unique AS isUniqueIndex
          , tblindexes.is_unique_constraint AS isUniqueConstraint
          , tblindexes.idxColOrder
          , CASE WHEN tblDefaults.tableId IS NULL THEN 0
                 ELSE 1
            END AS hasDefault
          , CASE WHEN tblCheckConstraints.columnId = 0 THEN 1
                 ELSE 0
            END AS hasTableCheck
          , CASE WHEN tblCheckConstraints.columnId IS NULL THEN 0
                 ELSE CASE WHEN tblCheckConstraints.columnId != 0 THEN 1
                           ELSE 0
                      END
            END AS hasColumnCheck
          , tblForeignKeys.foreignKeyName
          , LEFT(tblForeignKeys.fkCols, LEN(tblForeignKeys.fkCols) - 1) AS fkColumns
          , OBJECT_NAME(tblForeignKeys.pkTableID) AS pkTableName
          , LEFT(tblForeignKeys.pkCols, LEN(tblForeignKeys.pkCols) - 1) AS referencedPkColumns
          , 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = '
            + CAST(tblForeignKeys.fkID AS VARCHAR(16)) + ' AND parent_object_id = '
            + CAST(tblForeignKeys.fkTableID AS VARCHAR(16)) + ') ' + CHAR(13) + ' ALTER TABLE '
            + OBJECT_NAME(tblForeignKeys.fkTableID) + ' ADD CONSTRAINT ' + tblForeignKeys.foreignKeyName
            + ' FOREIGN KEY (' + LEFT(tblForeignKeys.fkCols, LEN(tblForeignKeys.fkCols) - 1) + ') REFERENCES '
            + OBJECT_NAME(tblForeignKeys.pkTableID) + ' (' + LEFT(tblForeignKeys.pkCols, LEN(tblForeignKeys.pkCols) - 1)
            + ') ' + ' ON DELETE' + CASE delete_referential_action
                                      WHEN 1 THEN N' CASCADE'
                                      WHEN 2 THEN N' SET NULL'
                                      WHEN 3 THEN N' SET DEFAULT'
                                      ELSE N' NO ACTION'
                                    END + ' ON UPDATE' + CASE UPDATE_REFERENTIAL_ACTION
                                                           WHEN 1 THEN N' CASCADE'
                                                           WHEN 2 THEN N' SET NULL'
                                                           WHEN 3 THEN N' SET DEFAULT'
                                                           ELSE N' NO ACTION'
                                                         END
            + CASE WHEN IS_NOT_FOR_REPLICATION = 1 THEN N' NOT FOR REPLICATION'
                   ELSE N''
              END AS fkCreateStmt
          , 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = ' + CAST(tblForeignKeys.fkID AS VARCHAR(16))
            + ' AND parent_object_id = ' + CAST(tblForeignKeys.fkTableID AS VARCHAR(16)) + ') ' + CHAR(13)
            + 'ALTER TABLE ' + OBJECT_NAME(tblForeignKeys.fkTableID) + ' DROP CONSTRAINT '
            + tblForeignKeys.foreignKeyName AS fkDropStmt
          , tblDefaults.defaultName
          , tblDefaults.definition AS defaultValue
          , tblDefaults.createStatement AS defaultCreateStatement
          , tblDefaults.dropStatement AS defaultDropStatement
          , tblCheckConstraints.checkName
          , tblCheckConstraints.checkDefinition
          , tblCheckConstraints.checkCreateStmt
          , tblCheckConstraints.checkDropStmt
    FROM    ( SELECT DISTINCT
                        innerFk2.fkID
                      , innerFk2.foreignKeyName
                      , innerFk2.fkTableID
                      , innerFk2.fkColumnID
                      , innerFk2.pkTableID
                      , innerFk2.delete_referential_action
                      , innerFk2.UPDATE_REFERENTIAL_ACTION
                      , innerFk2.IS_NOT_FOR_REPLICATION
                      , ( SELECT DISTINCT
                                    innerFk1.fkColName + ',' AS [text()]
                          FROM      cteFk innerFk1
                          WHERE     innerFk1.fkTableID = innerFk2.fkTableID
                                    AND innerFk1.pkTableID = innerFk2.pkTableID
                        FOR
                          XML PATH('')
                        ) [fkCols]
                      , ( SELECT DISTINCT
                                    innerFk1.pkColName + ',' AS [text()]
                          FROM      cteFk innerFk1
                          WHERE     innerFk1.fkTableID = innerFk2.fkTableID
                                    AND innerFk1.pkTableID = innerFk2.pkTableID
                        FOR
                          XML PATH('')
                        ) [pkCols]
              FROM      cteFk innerFk2
            ) [tblForeignKeys]
            RIGHT JOIN ( SELECT t.schema_id
                              , t.object_id AS tableId
                              , t.name AS tableName
                              , c.column_id AS columnId
                              , c.name AS columnName
                              , ty.user_type_id
                              , ty.name AS dataType
                              , c.max_length
                              , c.precision
                              , c.scale
                              , c.collation_name
                              , c.is_nullable
                              , c.is_identity
                         FROM   sys.columns c
                                INNER JOIN sys.tables t
                                    ON c.object_id = t.object_id
                                INNER JOIN sys.types ty
                                    ON c.user_type_id = ty.user_type_id
                       ) tblColumns
                ON tblColumns.tableId = tblForeignKeys.fkTableID
                   AND tblColumns.columnId = tblForeignKeys.fkColumnID
            LEFT JOIN ( SELECT  tblTables.object_id pkTableID
                              , tblIndexes.name
                              , tblIdxCols.key_ordinal Ord
                              , tblColumns.column_id pkColumnID
                              , tblIndexes.type_desc
                        FROM    sys.tables tblTables
                                INNER JOIN sys.indexes tblIndexes
                                    ON tblIndexes.object_id = tblTables.object_id
                                INNER JOIN sys.index_columns tblIdxCols
                                    ON tblIdxCols.object_id = tblTables.object_id
                                       AND tblIdxCols.index_id = tblIndexes.index_id
                                INNER JOIN sys.columns tblColumns
                                    ON tblColumns.object_id = tblTables.object_id
                                       AND tblColumns.column_id = tblIdxCols.column_id
                        WHERE   tblIndexes.is_primary_key = 1
                      ) tblPrimaryKeys
                ON tblColumns.tableId = tblPrimaryKeys.pkTableID
                   AND tblColumns.columnId = tblPrimaryKeys.pkColumnID
            LEFT JOIN ( SELECT  d.parent_object_id AS tableId
                              , c.column_id AS columnId
                              , d.name AS defaultName
                              , d.definition
                              , 'IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id ='
                                + CAST(d.object_ID AS VARCHAR(16)) + ' AND parent_object_id ='
                                + CAST(d.parent_object_id AS VARCHAR(16)) + ') ' + ' ALTER TABLE ['
                                + OBJECT_SCHEMA_NAME(d.parent_object_id) + '].[' + OBJECT_NAME(d.parent_object_id)
                                + '] DROP CONSTRAINT [' + d.name + '] ' AS dropStatement
                              , 'IF NOT EXISTS (SELECT * FROM sys.default_constraints WHERE object_id ='
                                + CAST(d.object_ID AS VARCHAR(16)) + ' AND parent_object_id ='
                                + CAST(d.parent_object_id AS VARCHAR(16)) + ') ' + ' ALTER TABLE ['
                                + OBJECT_SCHEMA_NAME(d.parent_object_id) + '].[' + OBJECT_NAME(d.parent_object_id)
                                + '] ADD CONSTRAINT [' + d.name + '] DEFAULT ' + d.definition + ' FOR ' + c.name AS createStatement
                        FROM    sys.default_constraints d
                                INNER JOIN sys.columns c
                                    ON d.parent_column_id = c.column_id
                                       AND d.parent_object_id = c.object_id
                      ) tblDefaults
                ON tblColumns.tableId = tblDefaults.tableId
                   AND tblColumns.columnId = tblDefaults.columnId
            LEFT JOIN ( SELECT  chks.object_id
                              , chks.parent_object_id AS tableId
                              , c.column_id AS columnId
                              , chks.name AS checkName
                              , chks.definition AS checkDefinition
                              , 'IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = 277576027 AND parent_object_id = '
                                + CAST(chks.parent_object_id AS VARCHAR(16)) + ') ' + CHAR(13) + 'ALTER TABLE ['
                                + OBJECT_SCHEMA_NAME(chks.parent_object_id) + '].[' + OBJECT_NAME(chks.parent_object_id)
                                + '] DROP CONSTRAINT [' + chks.name + '] ' AS checkDropStmt
                              , 'IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = 277576027 AND parent_object_id = '
                                + CAST(chks.parent_object_id AS VARCHAR(16)) + ') ' + CHAR(13) + 'ALTER TABLE ['
                                + OBJECT_SCHEMA_NAME(chks.parent_object_id) + '].[' + OBJECT_NAME(chks.parent_object_id)
                                + '] WITH NOCHECK ADD CONSTRAINT [' + chks.name + '] CHECK ' + chks.definition AS checkCreateStmt
                        FROM    sys.check_constraints chks
                                INNER JOIN sys.columns c
                                    ON chks.parent_column_id = c.column_id
                                       AND chks.parent_object_id = c.object_id
                      ) tblCheckConstraints
                ON tblCheckConstraints.tableId = tblColumns.tableId
                   AND tblCheckConstraints.columnId = tblColumns.columnId
            LEFT JOIN ( SELECT  obj.object_id AS tableID
                              , cols.column_id AS columnID
                              , idxCols.key_ordinal AS idxColOrder
                              , idxs.name AS indexName
                              , idxs.type_desc AS indexType
                              , idxs.is_primary_key
                              , idxs.is_unique
                              , idxs.is_unique_constraint
                        FROM    sys.objects obj
                                INNER JOIN sys.indexes idxs
                                    ON obj.object_id = idxs.object_id
                                INNER JOIN sys.index_columns idxCols
                                    ON idxCols.object_id = idxs.object_id
                                       AND idxCols.index_id = idxs.index_id
                                INNER JOIN sys.columns cols
                                    ON cols.object_id = idxCols.object_id
                                       AND cols.column_id = idxCols.column_id
                        WHERE   obj.type_desc = 'USER_TABLE'
                      ) tblIndexes
                ON tblIndexes.tableID = tblColumns.tableId
                   AND tblIndexes.columnID = tblColumns.columnId