Discussion :

[bitwin]

Bonjour,

Pour un identifiant donné d'une table de ma base de données, je souhaite savoir si l'enregistrement correspondant à ce dernier est supprimable.
Plus simplement, je souhaite savoir si l'identifiant est présent dans d'autres tables liées via les contraintes.
Si l'enregistrement n'est pas supprimable, je dois renvoyer les liste des tables dans lesquelles l'identifiant est présent.

Via des recherches, j'ai trouvé le code suivant (merci elsuket) qui permet de retrouver les tables liées

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-------------------------------
-- Nicolas SOUQUET - 09/11/2009
-------------------------------
SELECT    PS.name + '.' + PT.name AS parent_table_name
    , PC.name AS parent_column_name
    , RS.name + '.' + RT.name AS referenced_table_name
    , RC.name AS referenced_column_name
    , FK.name AS foreign_key_name
FROM    sys.schemas AS PS
INNER JOIN  sys.foreign_keys AS FK
      ON PS.schema_id = FK.schema_id
INNER JOIN  sys.foreign_key_columns AS FKC
      ON FK.object_id = FKC.constraint_object_id
INNER JOIN  sys.tables AS PT
      ON FK.parent_object_id = PT.object_id
INNER JOIN  sys.columns AS PC
      ON FKC.parent_object_id = PC.object_id
      AND FKC.parent_column_id = PC.column_id
INNER JOIN  sys.tables AS RT
      ON FK.referenced_object_id = RT.object_id
INNER JOIN  sys.columns AS RC
      ON FKC.referenced_object_id = RC.object_id
      AND FKC.referenced_column_id = RC.column_id
INNER JOIN  sys.schemas AS RS
      ON RT.schema_id = RS.schema_id
WHERE    
    RT.name = 'NomDeMaTable'

Avec cette requête, je pensais faire quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT T.parent_table_name
FROM (
-- ...
-- Select du code précédent
-- ...
) AS T
WHERE T.parent_table_name + '.' + T.parent_column_name = MonIdentifiant

Cette requête me renvoi un échec de conversion de la valeur nvarchar en type de donnés int, ce que je comprend.

J'ai regardé le SQL dynamique mais je n'arrive pas à l'appliquer à cette requête.

Est-ce que ce traitement peut faire l'objet d'une requête ou dois-je impérativement passer par du SQL dynamique ?

Quelle piste pour le SQL dynamique ?

Merci.

Bitwin

[WOLO Laurent]

Quel est l'interret de cette requete, qu'est ce que vous allez faire ensuite.

[bitwin]

Bonjour,

Pour rentrer dans le détail, tous les enregistrements des tables de ma base de données contiennent un champ 'Obsolète'.
A chaque suppression d'un enregistrement, un choix est proposé à l'utilisateur via un menu :
- Rendre l'enregistrement 'Obsolète'
- Supprimer définitivement l'enregistrement de la base

A l'ouverture de ce menu, je souhaite griser l'option suppression définitive si cette dernière n'est pas réalisable. De plus, sur l'info bulle de l'option de suppression, j'indique dans quelle(s) table(s) liée(s) l'enregistrement est présent.

Je ne supporte pas qu'une application laisse un utilisateur effectuer une opération alors qu'il est possible de savoir si celle-ci n'est pas réalisable.

Je sais qu'évoluant dans un contexte non figé, il y aura toujours le risque qu'un enregistrement soir inséré avant le clique sur l'option de suppression mais ce risque est minimum dans ma configuration et je vais gérer ce cas.

C'est pourquoi je cherche le meilleur moyen de savoir si un enregistrement est supprimable.

Bitwin

[WOLO Laurent]

Si je comprends bien, tu veux donner la possibilite de supprimer un enregistrement au cas ou aucune donnee n'est referencee pour l'enregistrement vise ?
Donc une fois que tu as la liste des tables qui referencient cette valeur, tu dois compter chacune de ces occurences dans les tables correspondantes et ne suppimer que si tu as zero dans chacune des tables.

Cette operation peut devenir tres laborieuse et couteuse...

[bitwin]

Si je comprends bien, tu veux donner la possibilite de supprimer un enregistrement au cas ou aucune donnee n'est referencee pour l'enregistrement vise ?
Donc une fois que tu as la liste des tables qui referencient cette valeur, tu dois compter chacune de ces occurences dans les tables correspondantes et ne suppimer que si tu as zero dans chacune des tables.

C'est exactement ça !

Cette operation peut devenir tres laborieuse et couteuse...

Bien sur, je le conçois, mais cependant :
- je n'estime pas avoir une structure de données si importante que ça (ni le volume)
- cette opération n'est pas réalisée sur toutes les tables de ma structure.
- Cette opération est gérée uniquement par des utilisateurs 'Responsable données'.
- Les opérations concernant cette fonctionnalité sont peu courante dans l'application.
- Je n'ai pas envie d'entendre : "L'application m'a laissé le choix de supprimer mais en fait je peux pas ! Elle aurait du le savoir puisqu'elle me le dit !"

Et avant de déterminer si cette opération pourra être laborieuse et couteuse, j'aurais souhaité tester

Bitwin

[aieeeuuuuu]

Bonjour,

Vous ne pouvez pas faire la requête tel que vous vouliez la faire (utiliser le résultat d'une autre requête comme nom d'objet -table ou colonne ou quoi que ce soit- dans une sous requete)

La solution serait en effet en effet le SQL dynamique.
Mais dans votre cas présent, je pense que vous pouvez écrire la requête une fois pour toutes (a modifier seulement si vous ajoutez des tables en dépendance de la table en question) plutôt que d'aller chercher à chaque fois dans les vues d'information schéma...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT 'Commande' AS TableDependante
WHERE EXISTS(
  SELECT 1
  FROM T_COMMANDE
  WHERE CLI_ID  = @IdClient
) 
 
UNION ALL
 
SELECT 'Adresse' 
WHERE EXISTS(
  SELECT 1
  FROM  T_ADRESSE
  WHERE CLI_ID = @IdClient
)
 
UNION ALL
...

[bitwin]

Suite aux différentes pistes évoquées, voici la procédure que j'ai pu établir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
USE MaBase
GO
 
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'bDroitSupprimer' AND type = 'P')
   DROP PROCEDURE bDroitSupprimer
GO
 
USE MaBase
GO
CREATE PROCEDURE bDroitSupprimer
	@MaTable nvarchar(128),
	@Id	INT
AS
 
DECLARE @ReqSQL varchar(max)
 
SELECT @ReqSQL = ISNULL(@ReqSQL, '') + CHAR(10) + ' UNION ALL ' + CHAR(10) + T.ReqSQL FROM (
SELECT 'SELECT ''' + PT.name + ''' AS IDPresentDans WHERE EXISTS(SELECT 1 FROM ' + PS.name + '.' + PT.name + ' WHERE ' + PC.name + ' = ' + CONVERT(varchar(20), @Id) + ')' AS ReqSQL
FROM    sys.schemas AS PS
INNER JOIN  sys.foreign_keys AS FK
      ON PS.schema_id = FK.schema_id
INNER JOIN  sys.foreign_key_columns AS FKC
      ON FK.object_id = FKC.constraint_object_id
INNER JOIN  sys.tables AS PT
      ON FK.parent_object_id = PT.object_id
INNER JOIN  sys.columns AS PC
      ON FKC.parent_object_id = PC.object_id
      AND FKC.parent_column_id = PC.column_id
INNER JOIN  sys.tables AS RT
      ON FK.referenced_object_id = RT.object_id
INNER JOIN  sys.columns AS RC
      ON FKC.referenced_object_id = RC.object_id
      AND FKC.referenced_column_id = RC.column_id
INNER JOIN  sys.schemas AS RS
      ON RT.schema_id = RS.schema_id
WHERE    1 = 1
AND    RT.name = '' + @MaTable + '') AS T;
 
--PRINT @ReqSQL;
 
SET @ReqSQL = RIGHT(@ReqSQL, LEN(@ReqSQL) - LEN(' UNION ALL ' + char(10)) - 1);
 
--PRINT @ReqSQL;
 
execute (@ReqSQL)
GO

Comme il s'agit de ma première procédure réalisée en Transact-SQL, il me reste 2 questions :
Est-ce que je peux éviter l'injection de code SQL ? (et si oui, comment)
Est-ce que je dois prévoir une gestion d'erreur ? (et si oui, laquelle)

Pour info, suite aux tests réalisés sur ma base de tests, le cout de cette opération me semble négligeable (réponse immédiate pour la vérification d'un ID relié à 26 tables avec des dizaines de milliers d'enregistrements par table).

Je le testerais plus tard en grandeur nature quand j'aurais récupéré une base de production (dont certaines tables dépassent le million d'enregistrements).

Bitwin

[elsuket]

Bonjour,

N'aurait-il pas été plus simple de gérer une exception sur violation de clé étrangère, à l'aide d'un TRY CATCH ?

Sinon, tu n'es pas loin du but; voici le code qui fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
USE MaBase
GO
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.bDroitSupprimer'), 'IsProcedure') = 1
BEGIN
	DROP PROCEDURE dbo.bDroitSupprimer
END
GO
 
CREATE PROCEDURE dbo.bDroitSupprimer
	@MaTable nvarchar(128)
	, @Id int
	, @MonSchema nvarchar(128) = 'dbo'
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @ReqSql nvarchar(max)
		, @cr nchar(2) = NCHAR(13) + NCHAR(10)
 
	SELECT	@ReqSql = CASE
			WHEN @ReqSql IS NULL THEN ReqSQL
			ELSE @ReqSql  + @cr + 'UNION ALL ' + @cr + ReqSQL
		END
	FROM	(
			SELECT		'SELECT ''' + PT.name + ''' AS IDPresentDans WHERE EXISTS(SELECT 1 FROM ' + PS.name + '.' + PT.name + ' WHERE ' + PC.name + ' = ' + CONVERT(varchar(20), @Id) + ')' AS ReqSQL
			FROM		sys.schemas AS PS
			INNER JOIN	sys.foreign_keys AS FK
						ON PS.schema_id = FK.schema_id
			INNER JOIN	sys.foreign_key_columns AS FKC
						ON FK.object_id = FKC.constraint_object_id
			INNER JOIN	sys.TABLES AS PT
						ON FK.parent_object_id = PT.object_id
			INNER JOIN	sys.COLUMNS AS PC
						ON FKC.parent_object_id = PC.object_id
						AND FKC.parent_column_id = PC.column_id
			INNER JOIN	sys.TABLES AS RT
						ON FK.referenced_object_id = RT.object_id
			INNER JOIN	sys.COLUMNS AS RC
						ON FKC.referenced_object_id = RC.object_id
						AND FKC.referenced_column_id = RC.column_id
			INNER JOIN	sys.schemas AS RS
						ON RT.schema_id = RS.schema_id
			WHERE		RT.name = @MaTable
			AND		RS.name = @MonSchema
		) AS T
 
	EXEC sp_executesql
		@ReqSQL
END

Est-ce que je peux éviter l'injection de code SQL ? (et si oui, comment)

Oui, mais ce n'est pas simple. Il est nécessaire de vérifier qu'aucun des paramètres de contient d'instruction autre que SELECT (donc pas de CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, MERGE, EXEC, GRANT, REVOKE, ...).

Est-ce que je dois prévoir une gestion d'erreur ? (et si oui, laquelle)

Dans le cas présent, tu peux le faire si tu souhaites enregistrer l'erreur dans une table. Sinon, tu peux te contenter de gérer l'exception dans l'application.

Pour info, suite aux tests réalisés sur ma base de tests, le cout de cette opération me semble négligeable (réponse immédiate pour la vérification d'un ID relié à 26 tables avec des dizaines de milliers d'enregistrements par table).

Je le testerais plus tard en grandeur nature quand j'aurais récupéré une base de production (dont certaines tables dépassent le million d'enregistrements).

Les problèmes de performance surviennent rarement avec un jeu de données à petite cardinalité

@++

[bitwin]

Bonjour,

Merci elsucket pour ces informations complémentaires.

Effectivement, la procédure est beaucoup plus propre de cette manière.

La gestion d'erreur et l'injection de code SQL sont actuellement gérés dans mon application pour les appels à cette procédure.

[aieeeuuuuu]

Concernant l'injection SQL, il n'y pas de risque à mon avis : vous n'incluez pas les paramètres dans votre requête finale envoyée à l'EXEC (à l'exception de @Id qui n'est qu'un INT).