SQL SERVER 2005 : RECHERCHE DE CHAMPS PAR LEURS NOMS MULTI BASES DE DONNÉES sqlserver, recherche, nom, multibase, champ, Source N°42129

Information sur la source

Catégorie :Procédure Classé sous : sqlserver, recherche, nom, multibase, champ Niveau : Initié Date de création : 08/04/2007 Date de mise à jour : 12/04/2007 21:35:32 Vu : 3 402

Auteur : skweeky

Site perso

Ecrire un message privé

Ce membre participe au partage de revenus publicitaires

Note :

10,00 / 10

1
2
3
4
5
6
7
8
9
10

Commentaire sur cette source (4)

Ajouter un commentaire et/ou une note

Description

Pour SQL Server 2005 uniquement. Permet de rechercher un champ par son nom dans toutes les bases de données d'une instance de SQL Server 2005.
Renvoie une table contenant le nom et l'id de la base de données, le nom et l'id du schéma, le nom et l'id de l'objet et idem pour le champ.

Il est possible de filtrer la recherche avec les 4 derniers critères.

le premier est le texte recherché (attention c'est un LIKE '%xxxx%' on recherche donc une portion de la châine du nom du champ

Les 3 suivant permettent de renvoyer ou non certains objets (une table est un des objets existant, tout comme l une vue, une procédure stockées).
@TableUniquement - Si 1 Renvoie uniquement les objets de type table
@ObjetSysteme - Si 0 ne renvoie pas les objets systèmes (ceux taggés ms_shipped)
@ObjetDependantDautres - Si 1 renvoie les objet qui dépendent d'autres tels que les CHECK / DEFAULT / ...

Source

ALTER PROCEDURE sql_RechercheChamp
@recherche nvarchar(128) = '',
@TableUniquement bit = 0,
@ObjetSysteme bit = 1,
@ObjetDependantDautres bit = 0
AS
BEGIN
DECLARE @BaseDonneesNom nvarchar(128)
DECLARE @BaseDonneesId int
DECLARE @sql nvarchar(max)
SET NOCOUNT ON
-- Création de la table temporaire qui contiendra les résultats
IF OBJECT_ID('tempdb..#resultat') IS NOT NULL
DROP TABLE #resultat
CREATE TABLE #resultat
(
BaseDonneesNom nvarchar(128),
BaseDonneesId int,
SchemaNom nvarchar(128),
SchemaId int,
ObjetType nvarchar(60),
ObjetNom nvarchar(128),
ObjetId int,
ChampNom nvarchar(128),
ChampType nvarchar(128),
ChampId int
)
-- Curseur bouclant sur les Bases de données
DECLARE BaseDonnees_curseur CURSOR
FOR SELECT name,
database_id
FROM sys.databases ;
OPEN BaseDonnees_curseur ;
FETCH NEXT FROM BaseDonnees_curseur INTO @BaseDonneesNom,
@BaseDonneesId ;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Création de la requête à executer
SET @sql = 'SELECT ''' + @BaseDonneesNom
+ ''' AS BaseDonneesNom, '
+ CAST(@BaseDonneesId as nvarchar(11))
+ ' AS BaseDonneesId, SCHEMA_NAME(obj.schema_id), obj.schema_id, obj.type_desc, obj.name, obj.object_id, col.name, typ.name, col.column_id FROM '
+ QUOTENAME(@BaseDonneesNom)
+ '.sys.columns AS col JOIN sys.objects AS obj ON col.object_id = obj.object_id JOIN sys.types AS typ ON col.system_type_id = typ.system_type_id WHERE col.name like ''%'
+ @recherche + '%'''
+ CASE @ObjetSysteme
WHEN 0 THEN ' AND obj.is_ms_shipped = 0 '
ELSE ''
END + CASE @TableUniquement
WHEN 1 THEN ' AND obj.type IN (''S'', ''U'') '
ELSE ''
END
+ CASE @ObjetDependantDautres
WHEN 0 THEN ' AND obj.parent_object_id = 0 '
ELSE ''
END ;
INSERT INTO #resultat
EXEC ( @sql ) ;
FETCH NEXT FROM BaseDonnees_curseur INTO @BaseDonneesNom,
@BaseDonneesId ;
END ;
CLOSE BaseDonnees_curseur ;
DEALLOCATE BaseDonnees_curseur ;
SET NOCOUNT OFF
-- Renvoie le résultat de la recherche
SELECT BaseDonneesNom,
BaseDonneesId,
SchemaNom,
SchemaId,
ObjetType,
ObjetNom,
ObjetId,
ChampNom,
ChampType,
ChampId
FROM #resultat ;
END
GO
-- Recherche le texte 'recherche' dans les champs de tous les objets, systèmes ou non
EXEC sql_RechercheChamp 'recherche'
-- Recherche le texte 'recherche' dans les champs des tables uniquement, systèmes ou non
EXEC sql_RechercheChamp 'recherche', 1
-- Recherche le texte 'recherche' dans les champs des objets non systèmes
EXEC sql_RechercheChamp 'recherche', 0, 0
-- Recherche le texte 'recherche' dans les champs des objets dépendant d'autres (inclus entre autres les DEFAULT et CHECK)
EXEC sql_RechercheChamp @recherche = 'recherche', @ObjetDependantDautres = 1

ALTER PROCEDURE sql_RechercheChamp
    @recherche nvarchar(128) = '',
    @TableUniquement bit = 0,
    @ObjetSysteme bit = 1,
    @ObjetDependantDautres bit = 0
AS 
    BEGIN
        DECLARE @BaseDonneesNom nvarchar(128)
        DECLARE @BaseDonneesId int
        DECLARE @sql nvarchar(max)

        SET NOCOUNT ON

	-- Création de la table temporaire qui contiendra les résultats
        IF OBJECT_ID('tempdb..#resultat') IS NOT NULL 
            DROP TABLE #resultat
        CREATE TABLE #resultat
            (
              BaseDonneesNom nvarchar(128),
              BaseDonneesId int,
              SchemaNom nvarchar(128),
              SchemaId int,
              ObjetType nvarchar(60),
              ObjetNom nvarchar(128),
              ObjetId int,
              ChampNom nvarchar(128),
			  ChampType nvarchar(128),
              ChampId int
            )

        -- Curseur bouclant sur les Bases de données
		DECLARE BaseDonnees_curseur CURSOR
            FOR SELECT  name,
                        database_id
                FROM    sys.databases ;
        OPEN BaseDonnees_curseur ;
        FETCH NEXT FROM BaseDonnees_curseur INTO @BaseDonneesNom,
            @BaseDonneesId ;
        WHILE @@FETCH_STATUS = 0
            BEGIN
				
		-- Création de la requête à executer
                SET @sql = 'SELECT ''' + @BaseDonneesNom
                    + ''' AS BaseDonneesNom, '
                    + CAST(@BaseDonneesId as nvarchar(11))
                    + ' AS BaseDonneesId, SCHEMA_NAME(obj.schema_id), obj.schema_id, obj.type_desc, obj.name, obj.object_id, col.name, typ.name, col.column_id FROM '
                    + QUOTENAME(@BaseDonneesNom)
                    + '.sys.columns AS col JOIN sys.objects AS obj ON col.object_id = obj.object_id JOIN sys.types AS typ ON col.system_type_id = typ.system_type_id WHERE col.name like ''%'
                    + @recherche + '%'''
                    + CASE @ObjetSysteme
                        WHEN 0 THEN ' AND obj.is_ms_shipped = 0 '
                        ELSE ''
                      END + CASE @TableUniquement
                              WHEN 1 THEN ' AND obj.type IN (''S'', ''U'') '
                              ELSE ''
                            END
                    + CASE @ObjetDependantDautres
                        WHEN 0 THEN ' AND obj.parent_object_id = 0 '
                        ELSE ''
                      END ;

                INSERT  INTO #resultat
                        EXEC ( @sql ) ;

                FETCH NEXT FROM BaseDonnees_curseur INTO @BaseDonneesNom,
                    @BaseDonneesId ;
            END ;
        CLOSE BaseDonnees_curseur ;
        DEALLOCATE BaseDonnees_curseur ;

        SET NOCOUNT OFF

	-- Renvoie le résultat de la recherche
        SELECT  BaseDonneesNom,
                BaseDonneesId,
                SchemaNom,
                SchemaId,
                ObjetType,
                ObjetNom,
                ObjetId,
                ChampNom,
				ChampType,
                ChampId
        FROM    #resultat ;
    END
GO

-- Recherche le texte 'recherche' dans les champs de tous les objets, systèmes ou non
EXEC sql_RechercheChamp 'recherche'

-- Recherche le texte 'recherche' dans les champs des tables uniquement, systèmes ou non
EXEC sql_RechercheChamp 'recherche', 1

-- Recherche le texte 'recherche' dans les champs des objets non systèmes
EXEC sql_RechercheChamp 'recherche', 0, 0

-- Recherche le texte 'recherche' dans les champs des objets dépendant d'autres (inclus entre autres les DEFAULT et CHECK)
EXEC sql_RechercheChamp @recherche = 'recherche', @ObjetDependantDautres = 1

Conclusion

Je vais essayer de faire un Proc stock équivalente sur SQL Server 7 / 2000... Et eventuellement ajouter des critères de recherche.

Historique

10 avril 2007 09:36:38 :: Ajout de la description
10 avril 2007 09:44:02 :: correction de la description
12 avril 2007 21:35:32 :: Ajout du QUOTENAME sur le nom de la base de données et du type dans le résultat

Sources du même auteur

Toutes les sources de skweeky

Sources de la même categorie

Toutes les sources de la catégorie Procédure

Sources en rapport avec celle ci

Commentaires

Commentaire de bad_smi le 11/04/2007 12:42:06

très bon script, moyen de ne plus se perdre;)
il sert à quoi le NOCOUNT ?
Commentaire de skweeky le 11/04/2007 18:50:14 administrateur CS

Il permet de ne pas renvoyé le xx enregitrements affectées pour les INSERT / SELECT entre le SET NOCOUNT ON et SET NOCOUNT OFF... Ca fait un peut plus propre
Commentaire de bad_smi le 11/04/2007 18:59:34

cela veut dire qu'on peut l'utiliser à chaque fois qu'on exécute des ordres INSERT/SELECT ?
Commentaire de skweeky le 14/04/2007 18:05:30 administrateur CS

On l'utilse pas systématiquement c'est pour masquer le fonctionnement interne de la procédure, on fait souvent de même dans un TRIGGER.