Sql server 2005 : recherche de champs par leurs noms multi bases de données

Commenter

Contenu du snippet

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 / Exemple : 


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.

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (cs_skweeky)