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

Soyez le premier à donner votre avis sur cette source.

Snippet vu 15 941 fois - Téléchargée 18 fois

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

Ajouter un commentaire

Commentaires

Signaler
Michibulle
Messages postés
1
Date d'inscription
mardi 13 décembre 2011
Statut
Membre
Dernière intervention
13 décembre 2011
 -
Bonjour,

Un tout grand merci pour ce script. Il fait exactement ce dont j'avais besoin.

Bien à vous,

Michel.
Signaler
cs_skweeky
Messages postés
259
Date d'inscription
mercredi 3 mai 2006
Statut
Modérateur
Dernière intervention
11 janvier 2010
 3 -
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.
Signaler
bad_smi
Messages postés
17
Date d'inscription
lundi 6 décembre 2004
Statut
Membre
Dernière intervention
26 avril 2008
 -
cela veut dire qu'on peut l'utiliser à chaque fois qu'on exécute des ordres INSERT/SELECT ?
Signaler
cs_skweeky
Messages postés
259
Date d'inscription
mercredi 3 mai 2006
Statut
Modérateur
Dernière intervention
11 janvier 2010
 3 -
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
Signaler
bad_smi
Messages postés
17
Date d'inscription
lundi 6 décembre 2004
Statut
Membre
Dernière intervention
26 avril 2008
 -
très bon script, moyen de ne plus se perdre;)
il sert à quoi le NOCOUNT ?
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)