Query pour rechercher dans toutes les tables d’une base de données

Pratique cette procédure stockée de Narayana Vyas Kondreddi.

Elle permet de faire une recherche de chaine dans toutes les colonnes de toutes les tables d’une base de données.

L’exécution se fait comme ceci : exec SearchAllTables ‘ce que je cherche’

Pour créer la procédure, voici le code :

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

 J’ai essayé cette procédure sur un serveur SQL server 2008R2 et elle fonctionne parfaitement bien.

A propos Martial Auroy

Mon goût pour les défis et pour la création m’ont amené à la direction de trois services informatiques. Dans le domaine industriel, dans la gestion de production, dans les ressources humaines et la paie. Créatif et Ingénieur de formation, j’ai pu développer et mettre en place des systèmes informatiques divers. Gestion de paies internationale, appareils de mesure dans les ateliers de l’aérospatiale ou des robots pour la fabrication de cosmétiques. Le Web m’est bien connu. La sécurité et la communication ont toujours été parties intégrantes des projets mis en place : Sécurité d’intrusion, d’accès et de traçabilité. Je suis curieux et toujours à la recherche de connaissance. J’aime le cinéma, la philosophie, voyager.

Je vous propose également...

Importer du contenu XML dans une table SQL serveur

Depuis MS SQL 7, il est possible d’utiliser du XML en résultat de requêtes.  Le …

Microsoft SQL 2014 est sorti !

Microsoft sort sa dernière version de SQL Server, alors quoi de neuf au niveau des améliorations et la gestion des licences.

1 commentaire

  1. Toute petite correction, sur les lignes 33 et 50 , le “>” il aime pas trop SQL Server (à remplacer par ‘>’)
    🙂

    Sinon merci beaucoup pour la proc , et surtout merci à l’auteur Narayana Vyas Kondreddi.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.