.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