Pregunta ¿Cómo puedo enumerar todas las claves externas que hacen referencia a una tabla determinada en SQL Server?


Necesito eliminar una tabla altamente referenciada en una base de datos de SQL Server. ¿Cómo puedo obtener una lista de todas las restricciones de clave externa que necesitaré eliminar para poder descartar la tabla?

(Las respuestas SQL son preferibles al hacer clic sobre en la GUI del estudio de administración).


564
2018-01-27 12:17


origen


Respuestas:


No estoy seguro de por qué nadie sugirió, pero yo uso sp_fkeys para consultar claves foráneas para una tabla dada:

EXEC sp_fkeys 'TableName'

863
2017-10-18 13:53



Utilizaría la función de Diagramación de la base de datos en SQL Server Management Studio, pero ya que descartó eso, esto funcionó para mí en SQL Server 2008 (no tengo 2005).

Para obtener una lista de nombres de columnas y tablas de referencia ...

select 
    t.name as TableWithForeignKey, 
    fk.constraint_column_id as FK_PartNo, c.
    name as ForeignKeyColumn 
from 
    sys.foreign_key_columns as fk
inner join 
    sys.tables as t on fk.parent_object_id = t.object_id
inner join 
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where 
    fk.referenced_object_id = (select object_id 
                               from sys.tables 
                               where name = 'TableOthersForeignKeyInto')
order by 
    TableWithForeignKey, FK_PartNo

Para obtener nombres de restricciones de clave externa

select distinct name from sys.objects where object_id in 
(   select fk.constraint_object_id from sys.foreign_key_columns as fk
    where fk.referenced_object_id = 
        (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)

193
2018-01-27 12:43



Esto te da:

  • El propio FK
  • Esquema al que pertenece el FK
  • Los "tabla de referencia"o la mesa que tiene el FK
  • Los "columna de referencia"o la columna dentro de la tabla de referencia que apunta al FK
  • Los "tabla de referencia"o la tabla que tiene la columna clave a la que apunta su FK
  • Los "columna referenciada"o la columna que es la clave a la que apunta su FK

Código a continuación:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

133
2017-09-21 07:14



Prueba esto :

sp_help 'TableName'

121
2018-01-27 12:41



También debería considerar las referencias a otros objetos.

Si la tabla fue altamente referenciada por otras tablas, probablemente también sea altamente referenciada por otros objetos como vistas, procedimientos almacenados, funciones y más.

Realmente recomendaría la herramienta GUI como el diálogo 'ver dependencias' en SSMS o herramienta gratuita como Búsqueda ApexSQL para esto porque la búsqueda de dependencias en otros objetos puede ser propensa a errores si desea hacerlo solo con SQL.

Si SQL es la única opción, puedes intentar hacerlo así.

select O.name as [Object_Name], C.text as [Object_Definition]
from sys.syscomments C
inner join sys.all_objects O ON C.id = O.object_id
where C.text like '%table_name%'

45
2018-04-01 09:10



La pregunta original solicitó obtener una lista de todas las claves externas en una tabla altamente referenciada para que la tabla se pueda eliminar.

Esta pequeña consulta devuelve todos los comandos de 'soltar clave externa' necesarios para colocar todas las claves externas en una tabla particular:

SELECT 
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'

Ejemplo de salida:

[DropCommand]
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable]
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]

Omita la cláusula WHERE para obtener los comandos de colocar para todas las claves externas en la base de datos actual.


17
2018-02-12 00:23



SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

12
2017-08-06 13:52