Como identificar, apagar e recriar Foreign Keys (FK) de uma tabela no SQL Server

Post Views 14,321 views

Neste artigo

Reading time 7 minutes

Olá pessoal,
Bom dia!

Introduction

Neste post vou demonstrar para vocês como identificar, apagar e recriar Foreign Keys (FK) de uma tabela no SQL Server. Muitas vezes precisamos excluir uma tabela ou apenas alterar uma coluna, mas o banco nos envia uma mensagem de erro informando que existe uma constraint de chave estrangeira para essa coluna, impossibilitando o DBA de realizar a alteração solicitada.

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_Telefone’ 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.

Para resolver esse problema, deve-se remover as contraints de FK, realizar a alteração na tabela e depois criar novamente. Quando a tabela possui muitas referências, essa tarefa se torna um pouco trabalhosa, principalmente se várias tabelas estão envolvidas.

Para facilitar essa atividade, que é bem comum no dia a dia, resolvi criar e disponibilizar para vocês, uma Stored Procedure que verifica as FK’s nas views de catálogo do SQL Server e traz a listagem completa.

Importante: Por restrição técnica, o SQL Server permite criar Foreign Keys apenas entre objetos do mesmo database.

Criando a massa de testes

Para demonstrar a utilização dessa procedure, criei esse script simples para que você gere uma massa de testes e possa testar a 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,

Name 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,

Name 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,

Name 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)

Como Remover as FK’s que Referenciam uma Tabela

Com o código abaixo, você poderá criar a SP citada no tópico, que permite a fácil visualização das FK’s que referenciam uma determinada tabela, e já informa o script para a remoção e criação dessa FK:

Visualizar código-fonte

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

Agora vamos dar uma olhada no resultado:

Lembrando que quando você for apagar as FK’s, copie ANTES os scripts para gerar novamente. Uma vez que as FK’s forem removidas, você não irá conseguir gerar novamente esses scripts utilizando a SP.

Como Remover as FK’s que referenciam uma tabela (FK composta)

Com a procedure abaixo, você pode facilmente identificar e remover/recriar as FK’s de uma determinada tabela, que possuem referências de chaves estrangeiras para outros objetos, mesmo que utilizem uma chave composta (mais de uma coluna formando a FK).

Visualizar código-fonte

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

Resultado da procedure:

Como Remover as FK’s de uma tabela

Com a procedure abaixo, você pode facilmente identificar e remover/recriar as FK’s de uma determinada tabela, que possuem referências de chaves estrangeiras para outros objetos.

Visualizar código-fonte

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

Resultado da procedure:

And that's it, folks!
Até a próxima!

sql, sql server, transact sql, tsql, fk, foreign key, chave estrangeira, erro, como identificar, como remover, como recriar, query, procedure

neemias fonseca neto disse:

May 10, 2019 às 11:55

Script da FK composta com problema ao executar que não retorna consulta. Resolvido editando e colocando o select para setar em @query e não em @cmd.

Responder
- Dirceu Resende disse:
  
  May 11, 2019 às 20:42
  
  Obrigado pela dica Neemias, já fiz a correção no script. Obrigado 🙂
  
  Responder
Legionario disse:

December 5, 2016 às 16:36

Script muito útil principalmente pra quem tem que fazer carga de dados no dia a dia . Muito grato por essa excelente postagem.

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

Como identificar, apagar e recriar Foreign Keys (FK) de uma tabela no SQL Server

Introduction

Criando a massa de testes

Como Remover as FK’s que Referenciam uma Tabela

Como Remover as FK’s que referenciam uma tabela (FK composta)

Como Remover as FK’s de uma tabela

sql, sql server, transact sql, tsql, fk, foreign key, chave estrangeira, erro, como identificar, como remover, como recriar, query, procedure

You may also like...

3 Responses

Deixe uma respostaCancelar resposta

Introduction

Criando a massa de testes

Como Remover as FK’s que Referenciam uma Tabela

Como Remover as FK’s que referenciam uma tabela (FK composta)

Como Remover as FK’s de uma tabela

sql, sql server, transact sql, tsql, fk, foreign key, chave estrangeira, erro, como identificar, como remover, como recriar, query, procedure

You may also like...

Como recuperar o maior valor entre múltiplas colunas em uma tabela do SQL Server

SQL Server - How to generate a script with all database indexes

SQL Server Configuration Manager gone? Learn how to retrieve the shortcut

3 Responses

Deixe uma respostaCancelar resposta