How to Identify, Delete, and Recreate Foreign Keys (FK) from a Table in SQL Server

Views: 12.802 views

In this article

Reading Time: 7 minutes

Hello people,
Good Morning!

Introduction

In this post I will demonstrate to you how to identify, delete and recreate Foreign Keys (FK) from a table in SQL Server. We often need to delete a table or just change a column, but the bank sends us an error message stating that there is a foreign key constraint for that column, making it impossible for the DBA to make the requested change.

Msg 5074, Level 16, State 1, Line 57
The object 'PK__Clientes__3214EC07F3BD01EC' is dependent on column 'Id'.
Msg 5074, Level 16, State 1, Line 57
The object 'FK_Cliente_Telephone' is dependent on column 'Id'.
Msg 5074, Level 16, State 1, Line 57
The object 'FK_Cliente_Endereco' is dependent on column 'Id'.
Msg 4922, Level 16, State 9, Line 57
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.

To resolve this issue, you must remove the FK contraints, make the change to the table, and then create again. When the table has many references, this task becomes a little laborious, especially if multiple tables are involved.

To facilitate this activity, which is very common on a daily basis, I decided to create and make available to you, a Stored Procedure that checks the FK's in the SQL Server catalog views and brings the complete listing.

Important: For technical reasons, SQL Server allows you to create Foreign Keys only between objects in the same database.

Creating the test mass

To demonstrate the use of this procedure, I created this simple script so that you generate a mass of tests and can test the SP.

IF (OBJECT_ID('dbo.Clientes') IS NOT NULL) DROP TABLE dbo.Clientes
CREATE TABLE dbo.Clientes (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Nome VARCHAR(50)
)

INSERT INTO dbo.Clientes
SELECT 'Dirceu'
UNION ALL
SELECT 'Patricia'
UNION ALL
SELECT 'Lucas'
UNION ALL
SELECT 'Leandro'
UNION ALL
SELECT 'Richardson'


IF (OBJECT_ID('dbo.Enderecos') IS NOT NULL) DROP TABLE dbo.Enderecos
CREATE TABLE dbo.Enderecos (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Id_Cliente INT,
    Ds_Endereco VARCHAR(50)
)

ALTER TABLE dbo.Enderecos ADD CONSTRAINT FK_Cliente_Endereco FOREIGN KEY (Id_Cliente) REFERENCES dbo.Clientes(Id)


INSERT INTO dbo.Enderecos (Id_Cliente, Ds_Endereco)
SELECT 1, 'Endereço Teste Cliente 1'
UNION ALL
SELECT 2, 'Endereço Teste Cliente 2'
UNION ALL
SELECT 3, 'Endereço Teste Cliente 3'
        



IF (OBJECT_ID('tempdb..dbo.Telefones') IS NOT NULL) DROP TABLE dbo.Telefones
CREATE TABLE dbo.Telefones (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Id_Cliente INT,
    Nr_Telefone VARCHAR(11)
)

ALTER TABLE dbo.Telefones ADD CONSTRAINT FK_Cliente_Telefone FOREIGN KEY (Id_Cliente) REFERENCES dbo.Clientes(Id)


INSERT INTO dbo.Telefones (Id_Cliente, Nr_Telefone)
SELECT 1, '27999998888'
UNION ALL
SELECT 4, '27999997777'
UNION ALL
SELECT 5, '27999996666'


IF (OBJECT_ID('dbo.Teste1') IS NOT NULL) DROP TABLE dbo.Teste1
CREATE TABLE dbo.Teste1 (
    Cod1 INT NOT NULL,
    Cod2 int NOT NULL,
    Cod3 int NOT NULL,
    Nome VARCHAR(200)
)

IF (OBJECT_ID('dbo.Teste2') IS NOT NULL) DROP TABLE dbo.Teste2
CREATE TABLE dbo.Teste2 (
    Codigo1 INT NOT NULL,
    Codigo2 int NOT NULL,
    Codigo3 int NOT NULL,
    Nome VARCHAR(200)
)

ALTER TABLE dbo.Teste2 ADD CONSTRAINT [PK_Teste2] PRIMARY KEY (Codigo1, Codigo2, Codigo3)
ALTER TABLE dbo.Teste1 ADD CONSTRAINT [FK_Teste1] FOREIGN KEY (Cod1, Cod2, Cod3) REFERENCES dbo.Teste2(Codigo1, Codigo2, Codigo3)

IF (OBJECT_ID('dbo.Clientes') IS NOT NULL) DROP TABLE dbo.Clientes

CREATE TABLE dbo.Clientes (

Id INT IDENTITY(1,1) PRIMARY KEY,

Nome VARCHAR(50)

)

INSERT INTO dbo.Clientes

SELECT 'Dirceu'

UNION ALL

SELECT 'Patricia'

UNION ALL

SELECT 'Lucas'

UNION ALL

SELECT 'Leandro'

UNION ALL

SELECT 'Richardson'

IF (OBJECT_ID('dbo.Enderecos') IS NOT NULL) DROP TABLE dbo.Enderecos

CREATE TABLE dbo.Enderecos (

Id INT IDENTITY(1,1) PRIMARY KEY,

Id_Cliente INT,

Ds_Endereco VARCHAR(50)

)

ALTER TABLE dbo.Enderecos ADD CONSTRAINT FK_Cliente_Endereco FOREIGN KEY (Id_Cliente) REFERENCES dbo.Clientes(Id)

INSERT INTO dbo.Enderecos (Id_Cliente, Ds_Endereco)

SELECT 1, 'Endereço Teste Cliente 1'

UNION ALL

SELECT 2, 'Endereço Teste Cliente 2'

UNION ALL

SELECT 3, 'Endereço Teste Cliente 3'

IF (OBJECT_ID('tempdb..dbo.Telefones') IS NOT NULL) DROP TABLE dbo.Telefones

CREATE TABLE dbo.Telefones (

Id INT IDENTITY(1,1) PRIMARY KEY,

Id_Cliente INT,

Nr_Telefone VARCHAR(11)

)

ALTER TABLE dbo.Telefones ADD CONSTRAINT FK_Cliente_Telefone FOREIGN KEY (Id_Cliente) REFERENCES dbo.Clientes(Id)

INSERT INTO dbo.Telefones (Id_Cliente, Nr_Telefone)

SELECT 1, '27999998888'

UNION ALL

SELECT 4, '27999997777'

UNION ALL

SELECT 5, '27999996666'

IF (OBJECT_ID('dbo.Teste1') IS NOT NULL) DROP TABLE dbo.Teste1

CREATE TABLE dbo.Teste1 (

Cod1 INT NOT NULL,

Cod2 int NOT NULL,

Cod3 int NOT NULL,

Nome VARCHAR(200)

)

IF (OBJECT_ID('dbo.Teste2') IS NOT NULL) DROP TABLE dbo.Teste2

CREATE TABLE dbo.Teste2 (

Codigo1 INT NOT NULL,

Codigo2 int NOT NULL,

Codigo3 int NOT NULL,

Nome VARCHAR(200)

)

ALTER TABLE dbo.Teste2 ADD CONSTRAINT [PK_Teste2] PRIMARY KEY (Codigo1, Codigo2, Codigo3)

ALTER TABLE dbo.Teste1 ADD CONSTRAINT [FK_Teste1] FOREIGN KEY (Cod1, Cod2, Cod3) REFERENCES dbo.Teste2(Codigo1, Codigo2, Codigo3)

How to Remove FK's Referencing a Table

With the code below, you can create the SP mentioned in the topic, which allows easy viewing of the FK's that reference a certain table, and already informs the script for removing and creating that FK:

View source

CREATE PROCEDURE [dbo].[stpRecria_FK]
    @Database [sysname],
    @Objeto [sysname] = NULL,
    @Schema [sysname] = 'dbo'
WITH EXECUTE AS CALLER
AS
BEGIN
    
    
    DECLARE @Db_Id sysname = (SELECT database_id FROM sys.databases WHERE name = @Database)
    DECLARE @query VARCHAR(MAX)

    
    SET @query = '
    SELECT
        FK.[name] AS Ds_Nome_FK,
        schema_ori.[name] + ''.'' + objeto_ori.[name] AS Ds_Objeto,
        coluna_ori.[name] AS Ds_Coluna,
        schema_dest.[name] + ''.'' + objeto_dest.[name] AS Ds_Objeto_Referencia,
        coluna_dest.[name] AS Ds_Coluna_Referencia,
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.[name] + ''].['' + objeto_ori.[name] + ''] DROP CONSTRAINT ['' + FK.[name]  + '']'' AS Dropar_FK,
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.[name] + ''].['' + objeto_ori.[name] + ''] ADD CONSTRAINT ['' + FK.[name] + ''] FOREIGN KEY ('' + coluna_ori.[name] + '') REFERENCES [' + @Database + '].['' + schema_dest.[name] + ''].['' + objeto_dest.[name] + ''] ('' + coluna_dest.[name] + '')'' AS Criar_FK
    FROM 
        [' + @Database + '].sys.foreign_keys                    AS FK            WITH(NOLOCK)
        JOIN [' + @Database + '].sys.foreign_key_columns        AS FK_Coluna     WITH(NOLOCK)    ON FK.[object_id] = FK_Coluna.constraint_object_id
    
        JOIN [' + @Database + '].sys.objects                    AS objeto_ori    WITH(NOLOCK)    ON FK.parent_object_id = objeto_ori.[object_id]
        JOIN [' + @Database + '].sys.objects                    AS objeto_dest   WITH(NOLOCK)    ON FK.referenced_object_id = objeto_dest.[object_id]
 
        JOIN [' + @Database + '].sys.schemas                    AS schema_ori    WITH(NOLOCK)    ON objeto_ori.[schema_id] = schema_ori.[schema_id]
        JOIN [' + @Database + '].sys.schemas                    AS schema_dest   WITH(NOLOCK)    ON objeto_dest.[schema_id] = schema_dest.[schema_id]
    
        JOIN [' + @Database + '].sys.columns                    AS coluna_ori    WITH(NOLOCK)    ON FK_Coluna.parent_object_id = coluna_ori.[object_id] AND FK_Coluna.parent_column_id = coluna_ori.[column_id]
        JOIN [' + @Database + '].sys.columns                    AS coluna_dest   WITH(NOLOCK)    ON FK_Coluna.referenced_object_id = coluna_dest.[object_id] AND FK_Coluna.referenced_column_id = coluna_dest.[column_id]'
 
 
    IF (NULLIF(LTRIM(RTRIM(@Objeto)), '') IS NOT NULL)
    BEGIN
 
        SET @query = @query + '
    WHERE
        objeto_dest.[name] = ''' + @Objeto + ''''
 
    END
    
 
    IF (NULLIF(LTRIM(RTRIM(@Schema)), '') IS NOT NULL)
    BEGIN
 
        IF (@Objeto IS NULL) 
            SET @query = @query + ' 
    WHERE 1=1'
 
        SET @query = @query + '
        AND schema_dest.[name] = ''' + @Schema + ''''
 
    END
 
 
    SET @query = @query + '
    ORDER BY
        schema_dest.[name], objeto_dest.[name]'

    
    EXEC(@query)


END

CREATE PROCEDURE [dbo].[stpRecria_FK]

@Database [sysname],

@Objeto [sysname] = NULL,

@Schema [sysname] = 'dbo'

WITH EXECUTE AS CALLER

BEGIN

DECLARE @Db_Id sysname = (SELECT database_id FROM sys.databases WHERE name = @Database)

DECLARE @query VARCHAR(MAX)

SET @query = '

SELECT

FK.[name] AS Ds_Nome_FK,

schema_ori.[name] + ''.'' + objeto_ori.[name] AS Ds_Objeto,

coluna_ori.[name] AS Ds_Coluna,

schema_dest.[name] + ''.'' + objeto_dest.[name] AS Ds_Objeto_Referencia,

coluna_dest.[name] AS Ds_Coluna_Referencia,

''ALTER TABLE [' + @Database + '].['' + schema_ori.[name] + ''].['' + objeto_ori.[name] + ''] DROP CONSTRAINT ['' + FK.[name] + '']'' AS Dropar_FK,

''ALTER TABLE [' + @Database + '].['' + schema_ori.[name] + ''].['' + objeto_ori.[name] + ''] ADD CONSTRAINT ['' + FK.[name] + ''] FOREIGN KEY ('' + coluna_ori.[name] + '') REFERENCES [' + @Database + '].['' + schema_dest.[name] + ''].['' + objeto_dest.[name] + ''] ('' + coluna_dest.[name] + '')'' AS Criar_FK

FROM

[' + @Database + '].sys.foreign_keys AS FK WITH(NOLOCK)

JOIN [' + @Database + '].sys.foreign_key_columns AS FK_Coluna WITH(NOLOCK) ON FK.[object_id] = FK_Coluna.constraint_object_id

JOIN [' + @Database + '].sys.objects AS objeto_ori WITH(NOLOCK) ON FK.parent_object_id = objeto_ori.[object_id]

JOIN [' + @Database + '].sys.objects AS objeto_dest WITH(NOLOCK) ON FK.referenced_object_id = objeto_dest.[object_id]

JOIN [' + @Database + '].sys.schemas AS schema_ori WITH(NOLOCK) ON objeto_ori.[schema_id] = schema_ori.[schema_id]

JOIN [' + @Database + '].sys.schemas AS schema_dest WITH(NOLOCK) ON objeto_dest.[schema_id] = schema_dest.[schema_id]

JOIN [' + @Database + '].sys.columns AS coluna_ori WITH(NOLOCK) ON FK_Coluna.parent_object_id = coluna_ori.[object_id] AND FK_Coluna.parent_column_id = coluna_ori.[column_id]

JOIN [' + @Database + '].sys.columns AS coluna_dest WITH(NOLOCK) ON FK_Coluna.referenced_object_id = coluna_dest.[object_id] AND FK_Coluna.referenced_column_id = coluna_dest.[column_id]'

IF (NULLIF(LTRIM(RTRIM(@Objeto)), '') IS NOT NULL)

BEGIN

SET @query = @query + '

WHERE

objeto_dest.[name] = ''' + @Objeto + ''''

END

IF (NULLIF(LTRIM(RTRIM(@Schema)), '') IS NOT NULL)

BEGIN

IF (@Objeto IS NULL)

SET @query = @query + '

WHERE 1=1'

SET @query = @query + '

AND schema_dest.[name] = ''' + @Schema + ''''

END

SET @query = @query + '

ORDER BY

schema_dest.[name], objeto_dest.[name]'

EXEC(@query)

END

Now let's take a look at the result:

Remembering that when you are going to delete the FK's, copy the scripts BEFORE generating them again. Once the FK's are removed, you will not be able to regenerate these scripts using the SP.

How to Remove FK's that reference a table (composite FK)

With the procedure below, you can easily identify and remove / recreate the FK's of a given table, which have foreign key references for other objects, even if they use a composite key (more than one column forming the FK).

View source

CREATE PROCEDURE [dbo].[stpRecria_FK_Composta] (
    @Database [sysname],
    @Objeto [sysname] = NULL,
    @Schema [sysname] = 'dbo'
)
AS
BEGIN
    
    DECLARE @query VARCHAR(MAX) = '
    
;WITH CTE
AS (
    SELECT
        FK.[object_id] AS Id_FK,
        FK.[name] AS Ds_Nome_FK,
        schema_ori.[name] AS Ds_Schema_Origem,
        schema_dest.[name] AS Ds_Schema_Destino,
        objeto_ori.[object_id] AS Id_Objeto_Origem,
        objeto_dest.[object_id] AS Id_Objeto_Destino,
        objeto_ori.[name] AS Ds_Tabela,
        objeto_dest.[name] AS Ds_Tabela_Referencia,
        schema_ori.[name] + ''.'' + objeto_ori.[name] AS Ds_Objeto,
        schema_dest.[name] + ''.'' + objeto_dest.[name] AS Ds_Objeto_Referencia,

        STUFF((
            SELECT '', '' + ISNULL(C1.[name], '''')
            FROM [' + @Database + '].sys.foreign_keys A1 WITH(NOLOCK)
            JOIN [' + @Database + '].sys.foreign_key_columns B1 WITH(NOLOCK) ON A1.[object_id] = B1.constraint_object_id
            JOIN [' + @Database + '].sys.columns C1 WITH(NOLOCK) ON B1.parent_object_id = C1.[object_id] AND B1.parent_column_id = C1.column_id
            WHERE ISNULL(C1.[object_id], '''') = ISNULL(objeto_ori.[object_id], '''')
            AND ISNULL(A1.[object_id], '''') = ISNULL(FK.[object_id], '''')
            ORDER BY C1.[name]
            FOR XML PATH('''')), 1, 2, ''''
        ) AS Colunas_Origem,

        STUFF((
            SELECT '', '' + ISNULL(C1.[name], '''')
            FROM [' + @Database + '].sys.foreign_keys A1 WITH(NOLOCK)
            JOIN [' + @Database + '].sys.foreign_key_columns B1 WITH(NOLOCK) ON A1.[object_id] = B1.constraint_object_id
            JOIN [' + @Database + '].sys.columns C1 WITH(NOLOCK) ON B1.referenced_object_id = C1.[object_id] AND B1.referenced_column_id = C1.column_id
            WHERE ISNULL(C1.[object_id], '''') = ISNULL(objeto_dest.[object_id], '''')
            AND ISNULL(A1.[object_id], '''') = ISNULL(FK.[object_id], '''')
            ORDER BY C1.[name]
            FOR XML PATH('''')), 1, 2, ''''
        ) AS Colunas_Destino
    FROM 
        [' + @Database + '].sys.foreign_keys				    AS FK		WITH(NOLOCK)
        JOIN [' + @Database + '].sys.foreign_key_columns	            AS FK_Coluna	WITH(NOLOCK)	ON FK.[object_id] = FK_Coluna.constraint_object_id
    
        JOIN [' + @Database + '].sys.objects				    AS objeto_ori	WITH(NOLOCK)	ON FK.parent_object_id = objeto_ori.[object_id]
        JOIN [' + @Database + '].sys.objects				    AS objeto_dest	WITH(NOLOCK)	ON FK.referenced_object_id = objeto_dest.[object_id]

        JOIN [' + @Database + '].sys.schemas				    AS schema_ori	WITH(NOLOCK)	ON objeto_ori.[schema_id] = schema_ori.[schema_id]
        JOIN [' + @Database + '].sys.schemas				    AS schema_dest	WITH(NOLOCK)	ON objeto_dest.[schema_id] = schema_dest.[schema_id]
    
        JOIN [' + @Database + '].sys.columns				    AS coluna_ori	WITH(NOLOCK)	ON FK_Coluna.parent_object_id = coluna_ori.[object_id] AND FK_Coluna.parent_column_id = coluna_ori.column_id
    GROUP BY
        FK.[object_id],
        FK.[name],
        objeto_ori.[object_id],
        objeto_dest.[object_id],
        schema_ori.[name],
        schema_dest.[name],
        schema_ori.[name] + ''.'' + objeto_ori.[name],
        schema_dest.[name] + ''.'' + objeto_dest.[name],
        objeto_ori.[name],
        objeto_dest.[name]
)
SELECT 
    CTE.Ds_Nome_FK,
    CTE.Ds_Objeto,
    CTE.Ds_Objeto_Referencia,
    CTE.Colunas_Origem,
    CTE.Colunas_Destino,
    ''ALTER TABLE [' + @Database + '].['' + CTE.Ds_Schema_Origem + ''].['' + CTE.Ds_Tabela + ''] DROP CONSTRAINT ['' + CTE.Ds_Nome_FK  + '']'' AS Dropar_FK,
    ''ALTER TABLE [' + @Database + '].['' + CTE.Ds_Schema_Origem + ''].['' + CTE.Ds_Tabela + ''] ADD CONSTRAINT ['' + CTE.Ds_Nome_FK + ''] FOREIGN KEY ('' + CTE.Colunas_Origem + '') REFERENCES [' + @Database + '].['' + CTE.Ds_Schema_Destino + ''].['' + CTE.Ds_Tabela_Referencia + ''] ('' + CTE.Colunas_Destino + '')'' AS Criar_FK
FROM 
    CTE'


    IF (NULLIF(LTRIM(RTRIM(@Objeto)), '') IS NOT NULL)
    BEGIN

        SET @query = @query + '
WHERE
    Ds_Tabela_Referencia = ''' + @Objeto + ''''

    END
    

    IF (NULLIF(LTRIM(RTRIM(@Schema)), '') IS NOT NULL)
    BEGIN

        IF (@Objeto IS NULL) 
            SET @query = @query + ' 
WHERE 
    1=1'

        SET @query = @query + '
    AND Ds_Schema_Destino = ''' + @Schema + ''''

    END


    
    SET @query = @query + '
ORDER BY
    Ds_Objeto_Referencia,
    Ds_Nome_FK'

    
    EXEC(@query)


END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

CREATE PROCEDURE [dbo].[stpRecria_FK_Composta] (

@Database [sysname],

@Objeto [sysname] = NULL,

@Schema [sysname] = 'dbo'

)

BEGIN

DECLARE @query VARCHAR(MAX) = '

;WITH CTE

AS (

SELECT

FK.[object_id] AS Id_FK,

FK.[name] AS Ds_Nome_FK,

schema_ori.[name] AS Ds_Schema_Origem,

schema_dest.[name] AS Ds_Schema_Destino,

objeto_ori.[object_id] AS Id_Objeto_Origem,

objeto_dest.[object_id] AS Id_Objeto_Destino,

objeto_ori.[name] AS Ds_Tabela,

objeto_dest.[name] AS Ds_Tabela_Referencia,

schema_ori.[name] + ''.'' + objeto_ori.[name] AS Ds_Objeto,

schema_dest.[name] + ''.'' + objeto_dest.[name] AS Ds_Objeto_Referencia,

STUFF((

SELECT '', '' + ISNULL(C1.[name], '''')

FROM [' + @Database + '].sys.foreign_keys A1 WITH(NOLOCK)

JOIN [' + @Database + '].sys.foreign_key_columns B1 WITH(NOLOCK) ON A1.[object_id] = B1.constraint_object_id

JOIN [' + @Database + '].sys.columns C1 WITH(NOLOCK) ON B1.parent_object_id = C1.[object_id] AND B1.parent_column_id = C1.column_id

WHERE ISNULL(C1.[object_id], '''') = ISNULL(objeto_ori.[object_id], '''')

AND ISNULL(A1.[object_id], '''') = ISNULL(FK.[object_id], '''')

ORDER BY C1.[name]

FOR XML PATH('''')), 1, 2, ''''

) AS Colunas_Origem,

STUFF((

SELECT '', '' + ISNULL(C1.[name], '''')

FROM [' + @Database + '].sys.foreign_keys A1 WITH(NOLOCK)

JOIN [' + @Database + '].sys.foreign_key_columns B1 WITH(NOLOCK) ON A1.[object_id] = B1.constraint_object_id

JOIN [' + @Database + '].sys.columns C1 WITH(NOLOCK) ON B1.referenced_object_id = C1.[object_id] AND B1.referenced_column_id = C1.column_id

WHERE ISNULL(C1.[object_id], '''') = ISNULL(objeto_dest.[object_id], '''')

AND ISNULL(A1.[object_id], '''') = ISNULL(FK.[object_id], '''')

ORDER BY C1.[name]

FOR XML PATH('''')), 1, 2, ''''

) AS Colunas_Destino

FROM

[' + @Database + '].sys.foreign_keys AS FK WITH(NOLOCK)

JOIN [' + @Database + '].sys.foreign_key_columns AS FK_Coluna WITH(NOLOCK) ON FK.[object_id] = FK_Coluna.constraint_object_id

JOIN [' + @Database + '].sys.objects AS objeto_ori WITH(NOLOCK) ON FK.parent_object_id = objeto_ori.[object_id]

JOIN [' + @Database + '].sys.objects AS objeto_dest WITH(NOLOCK) ON FK.referenced_object_id = objeto_dest.[object_id]

JOIN [' + @Database + '].sys.schemas AS schema_ori WITH(NOLOCK) ON objeto_ori.[schema_id] = schema_ori.[schema_id]

JOIN [' + @Database + '].sys.schemas AS schema_dest WITH(NOLOCK) ON objeto_dest.[schema_id] = schema_dest.[schema_id]

JOIN [' + @Database + '].sys.columns AS coluna_ori WITH(NOLOCK) ON FK_Coluna.parent_object_id = coluna_ori.[object_id] AND FK_Coluna.parent_column_id = coluna_ori.column_id

GROUP BY

FK.[object_id],

FK.[name],

objeto_ori.[object_id],

objeto_dest.[object_id],

schema_ori.[name],

schema_dest.[name],

schema_ori.[name] + ''.'' + objeto_ori.[name],

schema_dest.[name] + ''.'' + objeto_dest.[name],

objeto_ori.[name],

objeto_dest.[name]

)

SELECT

CTE.Ds_Nome_FK,

CTE.Ds_Objeto,

CTE.Ds_Objeto_Referencia,

CTE.Colunas_Origem,

CTE.Colunas_Destino,

''ALTER TABLE [' + @Database + '].['' + CTE.Ds_Schema_Origem + ''].['' + CTE.Ds_Tabela + ''] DROP CONSTRAINT ['' + CTE.Ds_Nome_FK + '']'' AS Dropar_FK,

''ALTER TABLE [' + @Database + '].['' + CTE.Ds_Schema_Origem + ''].['' + CTE.Ds_Tabela + ''] ADD CONSTRAINT ['' + CTE.Ds_Nome_FK + ''] FOREIGN KEY ('' + CTE.Colunas_Origem + '') REFERENCES [' + @Database + '].['' + CTE.Ds_Schema_Destino + ''].['' + CTE.Ds_Tabela_Referencia + ''] ('' + CTE.Colunas_Destino + '')'' AS Criar_FK

FROM

CTE'

IF (NULLIF(LTRIM(RTRIM(@Objeto)), '') IS NOT NULL)

BEGIN

SET @query = @query + '

WHERE

Ds_Tabela_Referencia = ''' + @Objeto + ''''

END

IF (NULLIF(LTRIM(RTRIM(@Schema)), '') IS NOT NULL)

BEGIN

IF (@Objeto IS NULL)

SET @query = @query + '

WHERE

1=1'

SET @query = @query + '

AND Ds_Schema_Destino = ''' + @Schema + ''''

END

SET @query = @query + '

ORDER BY

Ds_Objeto_Referencia,

Ds_Nome_FK'

EXEC(@query)

END

Result of the procedure:

How to Remove FK's from a Table

With the procedure below, you can easily identify and remove / recreate the FK's of a given table, which have foreign key references for other objects.

View source

CREATE PROCEDURE [dbo].[stpRecria_FK_Tabela]
    @Database [sysname],
    @Objeto [sysname] = NULL,
    @Schema [sysname] = NULL
WITH EXECUTE AS CALLER
AS
BEGIN
    
    --
    -- DROP CONSTRAINTS 
    --

    DECLARE @Db_Id sysname = (SELECT database_id FROM sys.databases WHERE name = @Database)
    DECLARE @query VARCHAR(MAX), @cmd VARCHAR(MAX)

    SET @query = '
    SELECT 
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto.name + ''] DROP CONSTRAINT ['' + FK.name  + '']'' AS Dropar_FKs
    FROM 
        ' + @Database + '.sys.foreign_keys				AS FK
        JOIN ' + @Database + '.sys.foreign_key_columns	AS FK_Coluna ON FK.object_id = FK_Coluna.constraint_object_id
        JOIN ' + @Database + '.sys.objects				AS objeto ON FK.parent_object_id = objeto.object_id
        JOIN ' + @Database + '.sys.schemas				AS schema_ori ON objeto.schema_id = schema_ori.schema_id'


    IF (@Objeto IS NOT NULL)
    BEGIN

        SET @query = @query + '
    WHERE
        objeto.name = ''' + @Objeto + ''''

    END


    IF (@Schema IS NOT NULL)
    BEGIN

        IF (@Objeto IS NULL) 
            SET @query = @query + ' 
    WHERE 1=1'

        SET @query = @query + '
        AND schema_ori.name = ''' + @Schema + ''''

    END


    EXEC(@query)



    -- 
    -- RECREATE CONSTRAINTS
    --

    SET @query = '
    SELECT 
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto.name + ''] '' + 
        ''ADD CONSTRAINT ['' + fk.name + ''] FOREIGN KEY ('' + colunas.name + '') '' + 
        ''REFERENCES ['' + schema_ref.name + ''].['' + objeto_ref.name + ''] ('' + colunas_ref.name + '')'' as Recriar_FKs
    FROM 
        ' + @Database + '.sys.foreign_keys AS fk
        JOIN ' + @Database + '.sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id

        JOIN ' + @Database + '.sys.objects objeto ON fk.parent_object_id = objeto.object_id
        JOIN ' + @Database + '.sys.columns colunas ON fc.parent_column_id = colunas.column_id AND fk.parent_object_id = colunas.object_id
        JOIN ' + @Database + '.sys.schemas schema_ori ON objeto.schema_id = schema_ori.schema_id

        JOIN ' + @Database + '.sys.objects objeto_ref ON fc.referenced_object_id = objeto_ref.object_id
        JOIN ' + @Database + '.sys.columns colunas_ref ON fc.referenced_column_id = colunas_ref.column_id AND fk.referenced_object_id = colunas_ref.object_id
        JOIN ' + @Database + '.sys.schemas schema_ref ON objeto_ref.schema_id = schema_ref.schema_id'
    

    IF (@Objeto IS NOT NULL)
    BEGIN

        SET @query = @query + '
    WHERE
        objeto.name = ''' + @Objeto + ''''

    END


    IF (@Schema IS NOT NULL)
    BEGIN

        IF (@Objeto IS NULL) 
            SET @query = @query + ' 
    WHERE 1=1'

        SET @query = @query + '
        AND schema_ori.name = ''' + @Schema + ''''

    END


    EXEC(@query)


END

100

101

CREATE PROCEDURE [dbo].[stpRecria_FK_Tabela]

@Database [sysname],

@Objeto [sysname] = NULL,

@Schema [sysname] = NULL

WITH EXECUTE AS CALLER

BEGIN

-- DROP CONSTRAINTS

DECLARE @Db_Id sysname = (SELECT database_id FROM sys.databases WHERE name = @Database)

DECLARE @query VARCHAR(MAX), @cmd VARCHAR(MAX)

SET @query = '

SELECT

''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto.name + ''] DROP CONSTRAINT ['' + FK.name + '']'' AS Dropar_FKs

FROM

' + @Database + '.sys.foreign_keys AS FK

JOIN ' + @Database + '.sys.foreign_key_columns AS FK_Coluna ON FK.object_id = FK_Coluna.constraint_object_id

JOIN ' + @Database + '.sys.objects AS objeto ON FK.parent_object_id = objeto.object_id

JOIN ' + @Database + '.sys.schemas AS schema_ori ON objeto.schema_id = schema_ori.schema_id'

IF (@Objeto IS NOT NULL)

BEGIN

SET @query = @query + '

WHERE

objeto.name = ''' + @Objeto + ''''

END

IF (@Schema IS NOT NULL)

BEGIN

IF (@Objeto IS NULL)

SET @query = @query + '

WHERE 1=1'

SET @query = @query + '

AND schema_ori.name = ''' + @Schema + ''''

END

EXEC(@query)

-- RECREATE CONSTRAINTS

SET @query = '

SELECT

''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto.name + ''] '' +

''ADD CONSTRAINT ['' + fk.name + ''] FOREIGN KEY ('' + colunas.name + '') '' +

''REFERENCES ['' + schema_ref.name + ''].['' + objeto_ref.name + ''] ('' + colunas_ref.name + '')'' as Recriar_FKs

FROM

' + @Database + '.sys.foreign_keys AS fk

JOIN ' + @Database + '.sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id

JOIN ' + @Database + '.sys.objects objeto ON fk.parent_object_id = objeto.object_id

JOIN ' + @Database + '.sys.columns colunas ON fc.parent_column_id = colunas.column_id AND fk.parent_object_id = colunas.object_id

JOIN ' + @Database + '.sys.schemas schema_ori ON objeto.schema_id = schema_ori.schema_id

JOIN ' + @Database + '.sys.objects objeto_ref ON fc.referenced_object_id = objeto_ref.object_id

JOIN ' + @Database + '.sys.columns colunas_ref ON fc.referenced_column_id = colunas_ref.column_id AND fk.referenced_object_id = colunas_ref.object_id

JOIN ' + @Database + '.sys.schemas schema_ref ON objeto_ref.schema_id = schema_ref.schema_id'

IF (@Objeto IS NOT NULL)

BEGIN

SET @query = @query + '

WHERE

objeto.name = ''' + @Objeto + ''''

END

IF (@Schema IS NOT NULL)

BEGIN

IF (@Objeto IS NULL)

SET @query = @query + '

WHERE 1=1'

SET @query = @query + '

AND schema_ori.name = ''' + @Schema + ''''

END

EXEC(@query)

END

Result of the procedure:

That's it folks!
To the next!

sql, sql server, transact sql, tsql, fk, foreign key, foreign key, error, how to identify, how to remove, how to recreate, query, procedure

neemias fonseca grandson said:

10 May 2019 to 11: 55

FK script composed with problem executing that does not return query. Solved by editing and placing select to set to @query and not @cmd.

- Dirceu Resende said:
  
  11 May 2019 to 20: 42
  
  Thanks for the tip Nehemiah, I already made the correction in the script. Thanks 🙂
  
Legionary said:

December 5 from 2016 to 16: 36

Very useful script especially for those who have to load data on a daily basis. Very grateful for this excellent post.

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

How to identify, delete, and recreate Foreign Keys (FK) from a table in SQL Server

Introduction

Creating the test mass

How to Remove FK's Referencing a Table

How to Remove FK's that reference a table (composite FK)

How to Remove FK's from a Table

sql, sql server, transact sql, tsql, fk, foreign key, foreign key, error, how to identify, how to remove, how to recreate, query, procedure

You may also like ...

Comments

Leave a Comment Cancel reply

Introduction

Creating the test mass

How to Remove FK's Referencing a Table

How to Remove FK's that reference a table (composite FK)

How to Remove FK's from a Table

sql, sql server, transact sql, tsql, fk, foreign key, foreign key, error, how to identify, how to remove, how to recreate, query, procedure

You may also like ...

How to identify inactive sessions with open transactions in SQL Server

SQL Server Management Studio (SSMS) - How to activate the Dark theme (Dark Theme Support)

Compressing all tables in a database in SQL Server

Comments

Leave a Comment Cancel reply