Script foreign keys related to main primary table

by Amol 15. February 2011 18:28

You cannot do some operations if you have foreign keys attached to primary table.  For example, you cannot truncate table, you cannot modify column data type. To do such operation we need to drop foreign keys first and then recreate the same.  Manually finding all the foreign keys are time consuming process. To avoid manual interaction I have created script to print out all the Keys related to the main primary table and its child tables.

WITH ParentTable 
AS
(
    SELECT '[dbo].[Catalog]' AS ObjectName  
)
,MainQuery
AS
(
    SELECT
fkc.Constraint_Object_Id,
        QUOTENAME(OBJECT_NAME(fkc.constraint_object_id)) AS ConstraintName,
        QUOTENAME(Objectsc.name) AS SchemaName,
        QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) AS ObjectName,
        QUOTENAME(ObjectColumn.name) AS ColumnName,
        QUOTENAME(ObjectColumnType.name) AS ColumnType,      
QUOTENAME(referencedSc.name) AS ReferencedSchemaName,
                   
QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS ReferencedObjectName,      
QUOTENAME(ReferencedColumn.name) AS ReferencedColumnName,
      
QUOTENAME(ReferencedObjectColumnType.name) AS ReferencedObjectColumnType

    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns ObjectColumn
        ON (fkc.parent_object_id = ObjectColumn.object_id
            AND fkc.parent_column_id = ObjectColumn.column_id)
    INNER JOIN sys.columns ReferencedColumn
        ON (fkc.referenced_object_id = ReferencedColumn.object_id
            AND fkc.referenced_column_id = ReferencedColumn.column_id)
    INNER JOIN sys.types ObjectColumnType
        ON (ObjectColumn.User_type_id = ObjectColumnType.User_type_id)
    INNER JOIN sys.types ReferencedObjectColumnType
        ON (ReferencedColumn.User_type_id = ReferencedObjectColumnType.User_type_id)
    INNER JOIN sys.tables ObjectT
        ON (fkc.parent_object_id = ObjectT.object_id)
    INNER JOIN sys.schemas ObjectSc
        ON (ObjectT.schema_id = ObjectSc.schema_id)
    INNER JOIN sys.tables referencedT
        ON (fkc.parent_object_id = referencedT.object_id)
    INNER JOIN sys.schemas referencedSc
        ON (referencedT.schema_id = referencedSc.schema_id)
)

,CTE

AS
(
    SELECT
        constraint_object_id,
        ConstraintName,
        SchemaName,
        ObjectName,
        ColumnName,
        ColumnType,
        ReferencedSchemaName,
        ReferencedObjectName,
        ReferencedColumnName,
        ReferencedObjectColumnType,
        1 AS Lvl
    FROM MainQuery
    WHERE ReferencedSchemaName + '.' + ReferencedObjectName = (SELECT ObjectName FROM ParentTable)
                      AND SchemaName + '.' + ObjectName <> (SELECT ObjectName FROM ParentTable)

    UNION ALL

    SELECT 
        MainQuery.constraint_object_id,
        MainQuery.ConstraintName,
        MainQuery.SchemaName,
        MainQuery.ObjectName,
        MainQuery.ColumnName,
        MainQuery.ColumnType,
        MainQuery.ReferencedSchemaName,
        MainQuery.ReferencedObjectName,
        MainQuery.ReferencedColumnName,
        MainQuery.ReferencedObjectColumnType,
        CTE.Lvl + 1 AS lvl
    FROM MainQuery
    INNER JOIN CTE
        ON (MainQuery.ReferencedObjectName = CTE.ObjectName )

)

,ALLColumnsInLine

AS

(
    SELECT
        Constraint_object_id,
        ConstraintName,
        SchemaName,
        ObjectName,
        ColumnName =
            STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ColumnName
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ),
        ColumnType =
            STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ColumnType
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ),
        ReferencedSchemaName,
        ReferencedObjectName,
        ReferencedColumnName =
            STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ReferencedColumnName
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ), 
     ReferencedObjectColumnType =
          STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ReferencedObjectColumnType
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ),
      Lvl
    FROM CTE t1
    GROUP BY
        Constraint_object_id,
        ConstraintName,
        SchemaName,
        ObjectName,
        ReferencedSchemaName,
        ReferencedObjectName,
        Lvl
)

,FinalQuery

AS

(

     SELECT *,
        ' IF EXISTS (SELECT * ' + CHAR(13) +
        '           FROM sys.foreign_keys ' + CHAR(13) +
        '           WHERE object_id = OBJECT_ID(N''' + ConstraintName + ''') ' + CHAR(13) +
        '                 AND parent_object_id = OBJECT_ID(N''' + SchemaName + '.' + ObjectName + ''')) ' + CHAR(13) +
        '   ALTER TABLE ' + SchemaName + '.' + ObjectName +
        ' DROP CONSTRAINT ' + ConstraintName + CHAR(10) + CHAR(13)
        + ' GO ' + CHAR(10) + CHAR(13) AS DropStatement,
        ' ALTER TABLE ' + SchemaName + '.' + ObjectName + CHAR(13) +
        ' WITH CHECK ADD CONSTRAINT ' + ConstraintName + CHAR(13) +
        ' FOREIGN KEY(' + ColumnName + ') ' + CHAR(13) +
        ' REFERENCES ' + ReferencedSchemaName + '.' + ReferencedObjectName + ' (' + ReferencedColumnName + ') '
        + CHAR(10) + CHAR(13) + ' GO ' + CHAR(10) + CHAR(13) +
|        ' ALTER TABLE ' + SchemaName + '.' + ObjectName + CHAR(13) + 
        ' CHECK CONSTRAINT ' + ConstraintName
        + CHAR(10) + CHAR(13) + ' GO ' + CHAR(10) + CHAR(13) AS CreateStatement
    FROM ALLColumnsInLine
)

SELECT
    Constraint_object_id,
    ConstraintName,
    SchemaName,
    ObjectName,
    ColumnName,
    ColumnType,
    ReferencedSchemaName,
    ReferencedObjectName,
    ReferencedColumnName,
    ReferencedObjectColumnType,
    DropStatement + CreateStatement AS DropAndCreate
FROM FinalQuery
ORDER BY Lvl DESC, ReferencedObjectName 

Tags:

SQL Server Scripts

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Category

Recent Posts

Tag cloud