.net - referencial - relacionar tablas en sql server por codigo

¿Cómo encontrar dependencias de claves foráneas en SQL Server?

Question

¿Cómo puedo encontrar todas las dependencias de clave externa en una columna en particular?

¿Cuáles son las diferentes alternativas (gráficamente en SSMS, consultas / vistas en SQL Server, herramientas de bases de datos de terceros, código en .NET)?

Answer 1

Creo que este script es menos caro:

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Answer 2

¡Muchas gracias a John Sansom, su consulta es excelente!

Además: debe agregar "AND PT.ORDINAL_POSITION = CU.ORDINAL_POSITION" al final de su consulta.

Si tiene varios campos en la clave principal, esta declaración coincidirá con los campos correspondientes (tuve el caso, su consulta creó todas las combinaciones, por lo que para 2 campos en la clave principal, tuve 4 resultados para la clave externa correspondiente) .

(Lo siento, no puedo comentar la respuesta de John, ya que no tengo suficientes puntos de reputación).

Answer 3

Como su pregunta está orientada a una sola tabla, puede usar esto:

EXEC sp_fkeys ''TableName''

Lo encontré en SO aquí:

https://.com/a/12956348/652519

Encontré la información que necesitaba bastante rápido. Enumera la tabla, columna y nombre de la clave foránea.

EDITAR

Aquí hay un enlace a la documentación que detalla los diferentes parámetros que se pueden usar: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

Answer 4

Después de una larga búsqueda encontré una solución de trabajo. Mi base de datos no usa sys.foreign_key_columns y information_schema.key_column_usage solo contiene claves principales.

Yo uso SQL Server 2015

SOLUCIÓN 1 (raramente utilizada)

Si otras soluciones no funcionan, esto funcionará bien:

 WITH CTE AS
(
SELECT
TAB.schema_id,
TAB.name,
COL.name AS COLNAME,
COl.is_identity
FROM
sys.tables TAB INNER JOIN sys.columns COL
ON TAB.object_id = COL.object_id
)
SELECT
DB_NAME() AS [Database],
SCHEMA_NAME(Child.schema_id) AS ''Schema'',
Child.name AS ''ChildTable'',
Child.COLNAME AS ''ChildColumn'',
Parent.name AS ''ParentTable'',
Parent.COLNAME AS ''ParentColumn''
FROM
cte Child INNER JOIN CTE Parent
ON
Child.COLNAME=Parent.COLNAME AND
Child.name<>Parent.name AND
Child.is_identity+1=Parent.is_identity

SOLUCIÓN 2 (comúnmente utilizado)

En la mayoría de los casos, esto funcionará bien:

 SELECT
DB_NAME() AS [Database],
SCHEMA_NAME(fk.schema_id) AS ''Schema'',
fk.name ''Name'',
tp.name ''ParentTable'',
cp.name ''ParentColumn'',
cp.column_id,
tr.name ''ChildTable'',
cr.name ''ChildColumn'',
cr.column_id
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE
-- CONCAT(SCHEMA_NAME(fk.schema_id), ''.'', tp.name, ''.'', cp.name) LIKE ''%my_table_name%'' OR
-- CONCAT(SCHEMA_NAME(fk.schema_id), ''.'', tr.name, ''.'', cr.name) LIKE ''%my_table_name%''
ORDER BY
tp.name, cp.column_id

Answer 5

Esta consulta devolverá detalles sobre claves externas en una tabla, admite múltiples claves de columna.

 SELECT *
FROM
(
SELECT
T1.constraint_name ConstraintName,
T2.COLUMN_NAME ColumnName,
T3.TABLE_NAME RefTableName,
T3.COLUMN_NAME RefColumnName,
T1.MATCH_OPTION MatchOption,
T1.UPDATE_RULE UpdateRule,
T1.DELETE_RULE DeleteRule
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2
ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3
ON T1.UNIQUE_CONSTRAINT_NAME = T3.CONSTRAINT_NAME
AND T2.ORDINAL_POSITION = T3.ORDINAL_POSITION) A
WHERE A.ConstraintName = ''table_name''

Answer 6

La siguiente consulta lo ayudará a comenzar. Enumera todas las Relaciones de claves extranjeras dentro de la base de datos actual.

SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = ''PRIMARY KEY''
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME

También puede ver las relaciones gráficamente dentro del estudio de SQL Server Management dentro de los Diagramas de la base de datos.

Answer 7

Si planea eliminar o cambiar el nombre de una tabla o columna, encontrar solo dependencias de claves externas puede no ser suficiente.

Hacer referencia a tablas no conectadas con clave externa : también deberá buscar tablas de referencia que no estén conectadas con clave externa (he visto muchas bases de datos con diseño incorrecto que no tenían claves foráneas definidas pero que sí tenían datos relacionados) ) La solución podría ser buscar el nombre de columna en todas las tablas y buscar columnas similares.

Otros objetos de la base de datos : probablemente esté un poco fuera de tema, pero si busca todas las referencias, también es importante verificar los objetos dependientes.

Herramientas GUI: pruebe la opción o herramientas de SSMS "Buscar objetos relacionados", como ApexSQL Search (herramienta gratuita, se integra en SSMS) para identificar todos los objetos dependientes, incluidas las tablas conectadas con la clave externa.

Answer 8

Solo una nota para la respuesta @ "John Sansom",

Si se buscan las dependencias de clave externa , creo que la cláusula PT Where debería ser:

i1.CONSTRAINT_TYPE = ''FOREIGN KEY'' -- instead of ''PRIMARY KEY''

y es la condición ON :

ON PT.TABLE_NAME = FK.TABLE_NAME – instead of PK.TABLE_NAME

Como comúnmente se usa la clave principal de la tabla externa, creo que este problema no se ha notado antes.

Answer 9

USE information_schema;

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE (table_name = *tablename*) AND NOT (REFERENCED_TABLE_NAME IS NULL)

Answer 10

Uno que realmente me gusta usar se llama SQL Dependency Tracker de Red Gate Software . Puede colocar cualquier objeto de base de datos, como tablas, procedimientos almacenados, etc., y luego dibujará automáticamente las líneas de relación entre todos los demás objetos que dependen de los elementos seleccionados.

Proporciona una muy buena representación gráfica de las dependencias en su esquema.

Answer 11

try: sp_help [table_name]

obtendrá toda la información sobre la tabla, incluidas todas las claves externas