décembre
2010
Un article de zinzineti
Ces procédures stockées permettent d’afficher les clés étrangères (FK) relatives à une clé primaire (PK). Ces procédures stockées permettent de présenter les liens entre tables sans utiliser le diagramme designer. Une aide donc pour identifier rapidement les colonnes à utiliser dans les jointures entre tables.
/*===========================================================================================
– Procdure Stocke permettant d'afficher les cls trangres(FK) relatives une cl primaire (PK)
– SQL SERVER 2005/2008 – Version1
– Auteur : Etienne ZINZINDOHOUE
—————— Exemple d'utilisation ———————————————–
P_Affiche_Liens_PK_FK_sql2005_2008 @schemaName = N'HumanResources', @tableName = N'Employee'
–===========================================================================================*/
USE AdventureWorks
go
CREATE PROCEDURE P_Affiche_Liens_PK_FK_sql2005_2008
@schemaName VARCHAR(50),@tableName VARCHAR(50)
AS
DECLARE @pk VARCHAR(max),@fk VARCHAR(max)
DECLARE @maTableVar table (
PKTABLE_QUALIFIER sysname
,PKTABLE_OWNER sysname
,PKTABLE_NAME sysname
,PKCOLUMN_NAME sysname
,FKTABLE_QUALIFIER sysname
,FKTABLE_OWNER sysname
,FKTABLE_NAME sysname
,FKCOLUMN_NAME sysname
,KEY_SEQ smallint
,UPDATE_RULE smallint
,DELETE_RULE smallint
,FK_NAME sysname
,PK_NAME sysname
,DEFERRABILITY smallint)
INSERT INTO @maTableVar
EXEC ('EXEC sp_fkeys @pktable_name = N'''+@tableName +''', @pktable_owner = N'''+@schemaName +'''');
SELECT PKTABLE_QUALIFIER+ '.'+ PKTABLE_OWNER + '.' +PKTABLE_NAME AS [Base.Schema.Table_Parent]
,PKCOLUMN_NAME AS [PK]
,FKTABLE_QUALIFIER + '.' + FKTABLE_OWNER + '.' + FKTABLE_NAME AS [Base.Schema.Table_Enfant]
,FKCOLUMN_NAME AS [FK]
FROM @maTableVar
ORDER BY [Base.Schema.Table_Parent],[PK],[Base.Schema.Table_Enfant],[FK]
GO
/*===========================================================================================
– Procdure Stocke permettant d'afficher les cls trangres(FK) relatives une cl primaire (PK)
– SQL SERVER 2000 – Version1
– Auteur : Etienne ZINZINDOHOUE
—————— Exemple d'utilisation ———————————————–
EXEC P_Affiche_Liens_PK_FK_sql2000 @schemaName = N'dbo',@tableName = N'Employees'
===========================================================================================*/
USE Northwind
GO
CREATE PROCEDURE P_Affiche_Liens_PK_FK_sql2000
@schemaName VARCHAR(50),@tableName VARCHAR(50)
AS
SELECT t1.TABLE_CATALOG +'.'+t1.TABLE_SCHEMA + '.'+ t1.TABLE_NAME AS [Base.Schema.Table_Parent]
,c1.name AS [PK]
,t2.TABLE_CATALOG +'.'+t2.TABLE_SCHEMA + '.'+ t2.TABLE_NAME AS [Base.Schema.Table_Enfant]
,c2.name AS [FK]
FROM sysforeignkeys f
INNER JOIN syscolumns c1 ON f.rkeyid = c1.id AND f.rkey = c1.colid
INNER JOIN syscolumns c2 ON f.fkeyid = c2.id AND f.fkey = c2.colid
INNER JOIN INFORMATION_SCHEMA.TABLES t1 ON t1.TABLE_NAME = object_name(f.rkeyid)
INNER JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_NAME = object_name(f.fkeyid)
WHERE object_name(f.rkeyid) = + @tableName + ' ' AND t1.TABLE_SCHEMA = + @schemaName
ORDER BY [Base.Schema.Table_Parent],[PK],[Base.Schema.Table_Enfant],[FK]
GO
=============================================================
Pour une purge saine [je parle des données ;-)]
=============================================================
Ces procédures stockées peuvent être utiles par exemple lors de la mis en place de batch de purge. Une des principales étapes de la mise en place de la purge d’une base de données est d’identifier les liens PK-FK entre tables. Ceci afin d’éviter les lignes orphelines (dans les « tables Enfant »). La conséquence logique de cette précaution est d’assurer la consistance et la qualité des données dans la base, lorsque l’option ON DELETE CASCADE n’est pas implémentée.
——————————————————————
— Etienne ZINZINDOHOUE
——————————————————————