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