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 .