tipos - ¿Cómo puedo enumerar todas las claves foráneas que hacen referencia a una tabla dada en SQL Server?

primary key y foreign key ejemplos

Question

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 tendré que eliminar para eliminar la tabla?

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

Answer 1

Algunas buenas respuestas arriba. Pero prefiero tener la respuesta con una consulta. Este fragmento de código se toma de sys.sp_helpconstraint (sys proc)

Esa es la forma en que Microsoft busca si hay claves foráneas asociadas al tbl.

--setup variables. Just change ''Customer'' to tbl you want
declare @objid int,
@objname nvarchar(776)
select @objname = ''Customer''
select @objid = object_id(@objname)
if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
select ''Table is referenced by foreign key'' =
db_name() + ''.''
+ rtrim(schema_name(ObjectProperty(parent_object_id,''schemaid'')))
+ ''.'' + object_name(parent_object_id)
+ '': '' + object_name(object_id)
from sys.foreign_keys
where referenced_object_id = @objid
order by 1

La respuesta se verá así: test_db_name.dbo.Account: FK_Account_Customer

Answer 2

Aquí está el código SQL que usaría.

SELECT
f.name AS ''Name of Foreign Key'',
OBJECT_NAME(f.parent_object_id) AS ''Table name'',
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ''Fieldname'',
OBJECT_NAME(t.object_id) AS ''References Table name'',
COL_NAME(t.object_id,fc.referenced_column_id) AS ''References fieldname'',
''ALTER TABLE ['' + OBJECT_NAME(f.parent_object_id) + ''] DROP CONSTRAINT ['' + f.name + '']'' AS ''Delete foreign key'',
''ALTER TABLE ['' + OBJECT_NAME(f.parent_object_id) + ''] WITH NOCHECK ADD CONSTRAINT ['' +
f.name + ''] FOREIGN KEY(['' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + '']) REFERENCES '' +
''['' + OBJECT_NAME(t.object_id) + ''] (['' +
COL_NAME(t.object_id,fc.referenced_column_id) + ''])'' AS ''Create foreign key''
-- , delete_referential_action_desc AS ''UsesCascadeDelete''
FROM sys.foreign_keys AS f,
sys.foreign_key_columns AS fc,
sys.tables t
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
AND OBJECT_NAME(t.object_id) = ''Employees'' -- Just show the FKs which reference a particular table
ORDER BY 2

No es particularmente claro SQL, así que veamos un ejemplo.

Entonces, suponiendo que quisiera eliminar la tabla de Employees en la querida base de datos Northwind Microsoft, pero SQL Server me dijo que una o más Claves foráneas me impedían hacer esto.

El comando SQL anterior devolvería estos resultados ...

Me muestra que hay 3 claves foráneas que hacen referencia a la tabla Employees . En otras palabras, no se me permitiría eliminar (eliminar) esta tabla hasta que estas tres Claves externas se eliminen por primera vez.

En los resultados, la primera fila es cómo se mostraría la siguiente restricción de clave externa en los resultados.

ALTER TABLE [dbo].[Employees] WITH NOCHECK
ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

La segunda a la última columna muestra el comando SQL que necesitaría usar para eliminar una de estas claves foráneas, por ejemplo:

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]

... y la columna de la derecha muestra el SQL para crearlo ...

ALTER TABLE [Employees] WITH NOCHECK
ADD CONSTRAINT [FK_Employees_Employees]
FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])

Con todos estos comandos, tiene todo lo que necesita para eliminar las claves foráneas relevantes para permitirle eliminar una tabla y luego volver a crearlas.

Uf. Espero que esto ayude.

Answer 3

El más simple es usar sys.foreign_keys_columns en SQL. Aquí, la tabla contiene los ID de objeto de todas las claves foráneas con su ID de columna referenciada ID de tabla referenciada, así como las columnas y tablas de referencia. Como los Id permanecen constantes, el resultado será confiable para futuras modificaciones en el esquema y en las tablas.

Consulta:

SELECT
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id)
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys

También podemos agregar filtro usando ''donde''

WHERE OBJECT_NAME(fkeys.parent_object_id) = ''table_name'' AND
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = ''schema_name''

Answer 4

Esto obtiene cualquier clave externa que involucra la tabla elegida. * Asume un formato _FIRSTABLENAME_SECONDTABLENAME.

 declare @tablename as varchar(MAX)
SET @tablename = ''yourtablename''
SELECT name
FROM YOURDATABASE.sys.objects
WHERE type_desc = ''FOREIGN_KEY_CONSTRAINT'' and (name LIKE ''%_'' + @tablename + ''empdb_%'' or name LIKE ''%_'' + @tablename )

Esta es una forma más general:

 SELECT name
FROM YOURDATABASE_PROD.sys.objects
WHERE type_desc = ''FOREIGN_KEY_CONSTRAINT'' and name LIKE ''%'' + @tablename + ''%'' and
name NOT LIKE ''[a-zA-Z0-9]'' + @tablename + ''%'' and name NOT LIKE ''%'' + @tablename + ''[a-zA-Z0-9]''

Answer 5

Esto te da:

• El propio FK en sí.
• Esquema al que pertenece el FK
• La " tabla de referencia " o la tabla que tiene el FK
• La " columna de referencia " o la columna dentro de la tabla de referencia que apunta al FK
• La " tabla referenciada " o la tabla que tiene la columna clave a la que apunta su FK
• La " columna referenciada " o la columna que es la clave a la que apunta su FK

Código abajo:

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

Answer 6

Estoy usando este script para encontrar todos los detalles relacionados con la clave externa. Estoy usando INFORMATION.SCHEMA. A continuación se muestra un script SQL:

SELECT
ccu.table_name AS SourceTable
,ccu.constraint_name AS SourceConstraint
,ccu.column_name AS SourceColumn
,kcu.table_name AS TargetTable
,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
ORDER BY ccu.table_name

Answer 7

Hay cómo obtener el recuento de todas las responsabilidades para la identificación seleccionada. Simplemente cambie el valor de @dbTableName, el valor de @dbRowId y su tipo (si es necesario, debe eliminar '''' en la línea no 82 (..SET @SQL = ..)). Disfrutar.

DECLARE @dbTableName varchar(max) = ''User''
DECLARE @dbRowId uniqueidentifier = ''21d34ecd-c1fd-11e2-8545-002219a42e1c''
DECLARE @FK_ROWCOUNT int
DECLARE @SQL nvarchar(max)
DECLARE @PKTABLE_QUALIFIER sysname
DECLARE @PKTABLE_OWNER sysname
DECLARE @PKTABLE_NAME sysname
DECLARE @PKCOLUMN_NAME sysname
DECLARE @FKTABLE_QUALIFIER sysname
DECLARE @FKTABLE_OWNER sysname
DECLARE @FKTABLE_NAME sysname
DECLARE @FKCOLUMN_NAME sysname
DECLARE @UPDATE_RULE smallint
DECLARE @DELETE_RULE smallint
DECLARE @FK_NAME sysname
DECLARE @PK_NAME sysname
DECLARE @DEFERRABILITY sysname
IF OBJECT_ID(''tempdb..#Temp1'') IS NOT NULL
DROP TABLE #Temp1;
CREATE TABLE #Temp1 (
PKTABLE_QUALIFIER sysname,
PKTABLE_OWNER sysname,
PKTABLE_NAME sysname,
PKCOLUMN_NAME sysname,
FKTABLE_QUALIFIER sysname,
FKTABLE_OWNER sysname,
FKTABLE_NAME sysname,
FKCOLUMN_NAME sysname,
UPDATE_RULE smallint,
DELETE_RULE smallint,
FK_NAME sysname,
PK_NAME sysname,
DEFERRABILITY sysname,
FK_ROWCOUNT int
);
DECLARE FK_Counter_Cursor CURSOR FOR
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
AND O1.NAME = @dbTableName
OPEN FK_Counter_Cursor;
FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ''SELECT @dbCountOut = COUNT(*) FROM ['' + @FKTABLE_NAME + ''] WHERE ['' + @FKCOLUMN_NAME + ''] = '''''' + CAST(@dbRowId AS varchar(max)) + '''''''';
EXECUTE sp_executesql @SQL, N''@dbCountOut int OUTPUT'', @dbCountOut = @FK_ROWCOUNT OUTPUT;
INSERT INTO #Temp1 (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, FK_ROWCOUNT) VALUES (@FKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY, @FK_ROWCOUNT)
FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
END;
CLOSE FK_Counter_Cursor;
DEALLOCATE FK_Counter_Cursor;
GO
SELECT * FROM #Temp1
GO

Answer 8

He estado usando esto en 2008 y hasta Es similar a algunas otras soluciones enumeradas, pero los nombres de los campos son adecuados para manejar las intercalaciones de casos específicos (LatBin). Además, puede darle un solo nombre de tabla y recuperar solo la información de esa tabla.

-->>SPECIFY THE DESIRED DB
USE ???
GO
/*********************************************************************************************
LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE
*********************************************************************************************/
DECLARE @tblName VARCHAR(255)
/*******************/
SET @tblName = NULL-->NULL will return all PK/FK constraints for every table in the database
/*******************/
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),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
AND ( O1.name = @tblName
OR O2.name = @tblName
OR @tblName IS null)
ORDER BY PKTABLE_NAME,FKTABLE_NAME

Answer 9

La pregunta original solicitada para 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 ''soltar clave externa'' necesarios para soltar 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 soltar para todas las claves externas en la base de datos actual.

Answer 10

La siguiente solución funciona para mí:

--Eliminar las llaves foraneas
declare @query varchar(8000)
declare cursorRecorrerTabla cursor for
SELECT ''ALTER TABLE [PoaComFinH].[''+sch.name+''].[''+referencingTable.Name+''] DROP CONSTRAINT [''+foreignKey.name+'']'' ''query''
FROM PoaComFinH.sys.foreign_key_columns fk
JOIN PoaComFinH.sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
JOIN PoaComFinH.sys.schemas sch ON referencingTable.schema_id = sch.schema_id
JOIN PoaComFinH.sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
JOIN PoaComFinH.sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
--3ro. abrir el cursor.
open cursorRecorrerTabla
fetch next from cursorRecorrerTabla
into @query
while @@fetch_status = 0
begin
--inicio cuerpo del cursor
print @query
exec(@query)
--fin cuerpo del cursor
fetch next from cursorRecorrerTabla
into @query
end
--cerrar cursor
close cursorRecorrerTabla
deallocate cursorRecorrerTabla

Answer 11

Prueba esto :

sp_help ''TableName''

Answer 12

También debe prestar atención a las referencias a otros objetos.

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

Realmente recomiendo una herramienta GUI como el cuadro de diálogo ''ver dependencias'' en SSMS o una herramienta gratuita como ApexSQL. Busque 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, podrías 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%''

Answer 13

Trabajando con lo que hizo @Gishu, pude producir y usar el siguiente SQL en SQL Server 2005

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo,
c.name AS ForeignKeyColumn, o.name AS FK_Name
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
INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables
WHERE name = ''TableOthersForeignKeyInto'')
ORDER BY TableWithForeignKey, FK_PartNo;

Que muestra los nombres de tablas, columnas y claves externas en una sola consulta.

Answer 14

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

Para obtener la lista de nombres de tabla y columna 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'')
)

Answer 15

primero

EXEC sp_fkeys ''Table'', ''Schema''

Luego usa NimbleText para jugar con tus resultados

Answer 16

Determine claves primarias y claves únicas para todas las tablas en una base de datos ...

Esto debería enumerar todas las restricciones y al final puedes poner tus filtros.

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='''' ,
REFERENCE_COL_NAME=''''
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=''FK'',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME in (''YOUR_TABLE_NAME'')
or REFERENCE_TABLE_NAME in (''YOUR_TABLE_NAME'')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

Para referencia, lea a través de http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

Answer 17

No estoy seguro de por qué nadie sugirió, pero uso sp_fkeys para consultar claves externas para una tabla determinada:

EXEC sp_fkeys ''TableName''

Answer 18

 SELECT OBJECT_NAME(fk.parent_object_id) as ReferencingTable,
OBJECT_NAME(fk.constraint_object_id) as [FKContraint]
FROM sys.foreign_key_columns as fk
WHERE fk.referenced_object_id = OBJECT_ID(''ReferencedTable'', ''U'')

Esto solo muestra la relación si son restricciones de clave externa. Mi base de datos aparentemente es anterior a la restricción FK. Algunas tablas usan los desencadenantes para imponer la integridad referencial, y a veces no hay nada más que una columna de nombre similar para indicar la relación (y no hay integridad referencial).

Afortunadamente, tenemos una escena de nomenclatura coherente, por lo que puedo encontrar tablas de referencia y vistas como esta:

SELECT OBJECT_NAME(object_id) from sys.columns where name like ''client_id''

Utilicé esta selección como base para generar un script; hace lo que necesito hacer en las tablas relacionadas.

Answer 19

SELECT
object_name(parent_object_id),
object_name(referenced_object_id),
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id(''Table Name'')

Answer 20

SELECT
OBJECT_NAME(parent_object_id) ''Parent table'',
c.NAME ''Parent column name'',
OBJECT_NAME(referenced_object_id) ''Referenced table'',
cref.NAME ''Referenced column name''
FROM
sys.foreign_key_columns fkc
INNER JOIN
sys.columns c
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.object_id
INNER JOIN
sys.columns cref
ON fkc.referenced_column_id = cref.column_id
AND fkc.referenced_object_id = cref.object_id where OBJECT_NAME(parent_object_id) = ''tablename''

Si desea obtener la relación de clave externa de todas las tablas, excluya la cláusula where o escriba su nombre de tabla en lugar de nombre de tablename

Answer 21

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