Very useful - thanks for sharing!
"Clean" version of code:
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
gsc_dba