una - script tamaño base de datos sql server

Conocer las relaciones entre todas las tablas de la base de datos en SQL Server

Question

Deseo que todos sepan cómo las tablas de mi base de datos están relacionadas entre sí (es decir, PK / FK / UK) y, por lo tanto, creé un diagrama de base de todas mis tablas en SQL Server. El diagrama que se creó no era fácil de leer y tuvo que desplazarse (horizontalmente y algunas veces verticalmente) para ver la tabla en el otro extremo.

En resumen, el diagrama db de SQL no es amigable para la interfaz de usuario cuando se trata de conocer las relaciones entre muchas tablas.

Mi (simple) Pregunta: ¿Hay algo así como el diagrama de la base de datos que pueda hacer lo que hizo el diagrama de DB pero de una "buena" manera?

Answer 1

A veces, una representación textual también puede ayudar; con esta consulta en las vistas del catálogo del sistema, puede obtener una lista de todas las relaciones FK y cómo el enlace dos tablas (y en qué columnas operan).

SELECT
    fk.name ''FK Name'',
    tp.name ''Parent table'',
    cp.name, cp.column_id,
    tr.name ''Refrenced table'',
    cr.name, 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
ORDER BY
    tp.name, cp.column_id

Vuelca esto a Excel, y puedes dividir y dividir según la tabla principal, la tabla a la que se hace referencia o cualquier otra cosa.

Encuentro útiles las guías visuales, pero a veces, la documentación textual es igual de buena (o incluso mejor), solo mis 2 centavos .....

Answer 2

Este procedimiento almacenado le proporcionará un árbol jerárquico de relación. Basado en este article de Technet. También le proporcionará opcionalmente una consulta para leer o eliminar todos los datos relacionados.

IF OBJECT_ID(''GetForeignKeyRelations'',''P'') IS NOT NULL 
    DROP PROC GetForeignKeyRelations 
GO 

CREATE PROC GetForeignKeyRelations 
@Schemaname Sysname = ''dbo'' 
,@Tablename Sysname 
,@WhereClause NVARCHAR(2000) = '''' 
,@GenerateDeleteScripts bit  = 0  
,@GenerateSelectScripts bit  = 0 

AS 

SET NOCOUNT ON 

DECLARE @fkeytbl TABLE 
( 
ReferencingObjectid        int NULL 
,ReferencingSchemaname  Sysname NULL 
,ReferencingTablename   Sysname NULL  
,ReferencingColumnname  Sysname NULL 
,PrimarykeyObjectid     int  NULL 
,PrimarykeySchemaname   Sysname NULL 
,PrimarykeyTablename    Sysname NULL 
,PrimarykeyColumnname   Sysname NULL 
,Hierarchy              varchar(max) NULL 
,level                  int NULL 
,rnk                    varchar(max) NULL 
,Processed                bit default 0  NULL 
); 



 WITH fkey (ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname 
            ,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk) 
    AS 
    ( 
        SELECT                   
                               soc.object_id 
                              ,scc.name 
                              ,soc.name 
                              ,convert(sysname,null) 
                              ,convert(int,null) 
                              ,convert(sysname,null) 
                              ,convert(sysname,null) 
                              ,convert(sysname,null) 
                              ,CONVERT(VARCHAR(MAX), scc.name + ''.'' + soc.name  ) as Hierarchy 
                              ,0 as level 
                              ,rnk=convert(varchar(max),soc.object_id) 
        FROM SYS.objects soc 
        JOIN sys.schemas scc 
          ON soc.schema_id = scc.schema_id 
       WHERE scc.name =@Schemaname 
         AND soc.name =@Tablename 
      UNION ALL 
      SELECT                   sop.object_id 
                              ,scp.name 
                              ,sop.name 
                              ,socp.name 
                              ,soc.object_id 
                              ,scc.name 
                              ,soc.name 
                              ,socc.name 
                              ,CONVERT(VARCHAR(MAX), f.Hierarchy + '' --> '' + scp.name + ''.'' + sop.name ) as Hierarchy 
                              ,f.level+1 as level 
                              ,rnk=f.rnk + ''-'' + convert(varchar(max),sop.object_id) 
        FROM SYS.foreign_key_columns sfc 
        JOIN Sys.Objects sop 
          ON sfc.parent_object_id = sop.object_id 
        JOIN SYS.columns socp 
          ON socp.object_id = sop.object_id 
         AND socp.column_id = sfc.parent_column_id 
        JOIN sys.schemas scp 
          ON sop.schema_id = scp.schema_id 
        JOIN SYS.objects soc 
          ON sfc.referenced_object_id = soc.object_id 
        JOIN SYS.columns socc 
          ON socc.object_id = soc.object_id 
         AND socc.column_id = sfc.referenced_column_id 
        JOIN sys.schemas scc 
          ON soc.schema_id = scc.schema_id 
        JOIN fkey f 
          ON f.ReferencingObjectid = sfc.referenced_object_id 
        WHERE ISNULL(f.PrimarykeyObjectid,0) <> f.ReferencingObjectid 
      ) 

     INSERT INTO @fkeytbl 
     (ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname 
            ,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk) 
     SELECT ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname 
            ,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk 
       FROM fkey 

        SELECT F.Relationshiptree 
         FROM 
        ( 
        SELECT DISTINCT Replicate(''------'',Level) + CASE LEVEL WHEN 0 THEN '''' ELSE ''>'' END +  ReferencingSchemaname + ''.'' + ReferencingTablename ''Relationshiptree'' 
               ,RNK 
          FROM @fkeytbl 
          ) F 
        ORDER BY F.rnk ASC 

------------------------------------------------------------------------------------------------------------------------------- 
-- Generate the Delete / Select script 
------------------------------------------------------------------------------------------------------------------------------- 

    DECLARE @Sql VARCHAR(MAX) 
    DECLARE @RnkSql VARCHAR(MAX) 

    DECLARE @Jointables TABLE 
    ( 
    ID INT IDENTITY 
    ,Object_id int 
    ) 

    DECLARE @ProcessTablename SYSNAME 
    DECLARE @ProcessSchemaName SYSNAME 

    DECLARE @JoinConditionSQL VARCHAR(MAX) 
    DECLARE @Rnk VARCHAR(MAX) 
    DECLARE @OldTablename SYSNAME 

    IF @GenerateDeleteScripts = 1 or @GenerateSelectScripts = 1  
    BEGIN 

          WHILE EXISTS ( SELECT 1 
                           FROM @fkeytbl 
                          WHERE Processed = 0 
                            AND level > 0 ) 
          BEGIN 

            SELECT @ProcessTablename = '''' 
            SELECT @Sql                 = '''' 
            SELECT @JoinConditionSQL = '''' 
            SELECT @OldTablename     = '''' 


            SELECT TOP 1 @ProcessTablename = ReferencingTablename 
                  ,@ProcessSchemaName  = ReferencingSchemaname 
                  ,@Rnk = RNK  
              FROM @fkeytbl 
             WHERE Processed = 0 
              AND level > 0  
             ORDER BY level DESC 


            SELECT @RnkSql =''SELECT '' + REPLACE (@rnk,''-'','' UNION ALL SELECT '')  

            DELETE FROM @Jointables 

            INSERT INTO @Jointables 
            EXEC(@RnkSql) 

            IF @GenerateDeleteScripts = 1 
                SELECT @Sql = ''DELETE ['' + @ProcessSchemaName + ''].['' + @ProcessTablename + '']'' + CHAR(10) + '' FROM ['' + @ProcessSchemaName + ''].['' + @ProcessTablename + '']'' + CHAR(10) 

            IF @GenerateSelectScripts = 1 
                SELECT @Sql = ''SELECT  ['' + @ProcessSchemaName + ''].['' + @ProcessTablename + ''].*'' + CHAR(10) + '' FROM ['' + @ProcessSchemaName + ''].['' + @ProcessTablename + '']'' + CHAR(10) 

            SELECT @JoinConditionSQL = @JoinConditionSQL  
                                           + CASE  
                                             WHEN @OldTablename <> f.PrimarykeyTablename THEN  ''JOIN [''  + f.PrimarykeySchemaname  + ''].['' + f.PrimarykeyTablename + ''] '' + CHAR(10) + '' ON '' 
                                             ELSE '' AND ''  
                                             END 
                                           + '' [''  + f.PrimarykeySchemaname  + ''].['' + f.PrimarykeyTablename + ''].['' + f.PrimarykeyColumnname + ''] =  [''  + f.ReferencingSchemaname  + ''].['' + f.ReferencingTablename + ''].['' + f.ReferencingColumnname + '']'' + CHAR(10)  
                     , @OldTablename = CASE  
                                         WHEN @OldTablename <> f.PrimarykeyTablename THEN  f.PrimarykeyTablename 
                                         ELSE @OldTablename 
                                         END 

                  FROM @fkeytbl f 
                  JOIN @Jointables j 
                    ON f.Referencingobjectid  = j.Object_id 
                 WHERE charindex(f.rnk + ''-'',@Rnk + ''-'') <> 0 
                   AND F.level > 0 
                 ORDER BY J.ID DESC 

            SELECT @Sql = @Sql +  @JoinConditionSQL 

            IF LTRIM(RTRIM(@WhereClause)) <> ''''  
                SELECT @Sql = @Sql + '' WHERE ('' + @WhereClause + '')'' 

            PRINT @SQL 
            PRINT CHAR(10) 

            UPDATE @fkeytbl 
               SET Processed = 1 
             WHERE ReferencingTablename = @ProcessTablename 
               AND rnk = @Rnk 

          END 

          IF @GenerateDeleteScripts = 1 
            SELECT @Sql = ''DELETE FROM ['' + @Schemaname + ''].['' + @Tablename + '']'' 

          IF @GenerateSelectScripts = 1 
            SELECT @Sql = ''SELECT * FROM ['' + @Schemaname + ''].['' + @Tablename + '']'' 

          IF LTRIM(RTRIM(@WhereClause)) <> ''''  
                SELECT @Sql = @Sql  + '' WHERE '' + @WhereClause 

         PRINT @SQL 
     END 

SET NOCOUNT OFF 


go 

Answer 3

Mi solución se basa en la solución @marc_s, solo concatenado columnas en los casos en que una restricción se basa en más de una columna:

SELECT
   FK.[name] AS ForeignKeyConstraintName
  ,SCHEMA_NAME(FT.schema_id) + ''.'' + FT.[name] AS ForeignTable
  ,STUFF(ForeignColumns.ForeignColumns, 1, 2, '''') AS ForeignColumns
  ,SCHEMA_NAME(RT.schema_id) + ''.'' + RT.[name] AS ReferencedTable
  ,STUFF(ReferencedColumns.ReferencedColumns, 1, 2, '''') AS ReferencedColumns
FROM
  sys.foreign_keys FK
  INNER JOIN sys.tables FT
  ON FT.object_id = FK.parent_object_id
  INNER JOIN sys.tables RT
  ON RT.object_id = FK.referenced_object_id
  CROSS APPLY
  (
    SELECT
      '', '' + iFC.[name] AS [text()]
    FROM
      sys.foreign_key_columns iFKC
      INNER JOIN sys.columns iFC
      ON iFC.object_id = iFKC.parent_object_id
        AND iFC.column_id = iFKC.parent_column_id
    WHERE
      iFKC.constraint_object_id = FK.object_id
    ORDER BY
      iFC.[name]
    FOR XML PATH('''')
  ) ForeignColumns (ForeignColumns)
  CROSS APPLY
  (
    SELECT
      '', '' + iRC.[name]AS [text()]
    FROM
      sys.foreign_key_columns iFKC
      INNER JOIN sys.columns iRC
      ON iRC.object_id = iFKC.referenced_object_id
        AND iRC.column_id = iFKC.referenced_column_id
    WHERE
      iFKC.constraint_object_id = FK.object_id
    ORDER BY
      iRC.[name]
    FOR XML PATH('''')
  ) ReferencedColumns (ReferencedColumns)

Answer 4

Microsoft Visio es probablemente el mejor que he encontrado, aunque hasta donde yo sé, no se generará automáticamente en función de sus relaciones.

EDITAR: intente esto en Visio, podría darle lo que necesita http://office.microsoft.com/en-us/visio-help/reverse-engineering-an-existing-database-HA001182257.aspx

Answer 5

O puedes mirar schemacrawler

Answer 6

Otra forma de recuperar los mismos datos utilizando INFORMATION_SCHEMA

Las vistas del esquema de información incluidas en SQL Server cumplen con la definición estándar ISO para INFORMATION_SCHEMA.

camino de sqlauthority

SELECT
K_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
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME=''something''WHERE FK.TABLE_NAME=''something''
WHERE PK.TABLE_NAME IN (''one_thing'', ''another'')
WHERE FK.TABLE_NAME IN (''one_thing'', ''another'')

Answer 7

select * from information_schema.REFERENTIAL_CONSTRAINTS where 
UNIQUE_CONSTRAINT_SCHEMA = ''TABLE_NAME'' 

Esto mostrará una lista de la columna con TABLE_NAME y REFERENCED_COLUMN_NAME .