Columns Explorer - With constraints create / drop scripts (no cursors)

  • lindbergh_ddv

    Mr or Mrs. 500

    Points: 505

    Comments posted to this topic are about the item Columns Explorer - With constraints create / drop scripts (no cursors)

  • rsorrell-839851

    SSC Rookie

    Points: 44

    when I attempt to run the script against a SQL 2005 database, I get the following errors

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 23

    Incorrect syntax near ')'.

    Msg 102, Level 15, State 1, Line 95

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 100

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 105

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 209

    Incorrect syntax near '?'.

  • mark.weiers

    SSC Rookie

    Points: 41

    There are probably some "hidden" characters in the text. Try copying the script into a text editor and you will see the characters that need to be removed.

  • lindbergh_ddv

    Mr or Mrs. 500

    Points: 505

    Mark, you're right. Please, clean all hidden chars by an editor and script will work.

    hi everybody

  • gsc_dba

    SSCertifiable

    Points: 5407

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • lindbergh_ddv

    Mr or Mrs. 500

    Points: 505

    Thank to you. Antonio

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply