Roba da smanettoni - Cluster

Inviato: **22/06/2024, 5:39**

Un saluto a tutti, ho scritto questa query SQL per cercare una stringa specifica in tutte le tabelle di un database SQL Server. Questa query è particolarmente utile per identificare rapidamente dove una determinata stringa appare nel database, senza dover eseguire frustranti ricerche manuali su ogni singola tabella.

Caratteristiche Principali:

Ricerca estesa: Cerca la stringa specificata in tutte le colonne di tipo char, varchar, nchar, nvarchar, text, int, bigint, decimal, numeric, float e real.
Utilizzo di tabelle temporanee: Utilizza una tabella temporanea #Results per memorizzare e visualizzare i risultati della ricerca.
Query dinamica: Costruisce ed esegue query dinamiche per cercare la stringa in ogni colonna, convertendo i tipi di dati numerici in nvarchar(MAX) per supportare l'operatore LIKE.
Sicurezza: Utilizza sp_executesql per eseguire le query dinamiche in modo sicuro con parametri.

Istruzioni per l'uso:

Sostituire Valore_Ricerca col valore che si desidera cercare.
Eseguire la query nell'ambiente SQL Server.
I risultati mostreranno tutte le occorrenze della stringa specificata, indicando la tabella e la colonna in cui è stata trovata.

Codice: Seleziona tutto

-- Autore: Lui Francesco
-- Versione: 1.0 (Rev. 07)
-- Data modifica: 18/06/2024
-- Testato su: SQL Server 2016, 2017, 2019, 2022
-- Note: Ricerca valore in tutte le tabelle

-- Licenza: Questo script è distribuito con licenza Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0).
--          È possibile utilizzarlo per scopi personali e non commerciali, ma non è consentito modificarlo o redistribuirlo senza il permesso dell'autore.
--          Per ulteriori informazioni, visitare https://creativecommons.org/licenses/by-nc-nd/4.0/

DECLARE @SearchValue nvarchar(100)
SET @SearchValue = 'Valore_Ricerca'

CREATE TABLE #TMP_RESULTS (NomeTabella nvarchar(370), ValoreRicerca nvarchar(MAX))

SET NOCOUNT ON

DECLARE @tblName nvarchar(256), @colName nvarchar(128), @SearchPattern nvarchar(110)
SET @tblName = ''
SET @SearchPattern = '%' + @SearchValue + '%'

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

    WHILE (@tblName IS NOT NULL) AND (@colName IS NOT NULL)
    BEGIN
        SET @colName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@tblName, 2)
              AND TABLE_NAME = PARSENAME(@tblName, 1)
              AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'int', 'bigint', 'decimal', 'numeric', 'float', 'real')
              AND QUOTENAME(COLUMN_NAME) > @colName
        )

        IF @colName IS NOT NULL
        BEGIN
            DECLARE @DynamicSQL nvarchar(MAX)
            SET @DynamicSQL = 
            'INSERT INTO #TMP_RESULTS (NomeTabella, ValoreRicerca) ' +
            'SELECT ''' + @tblName + '.' + @colName + ''', CAST(' + @colName + ' AS nvarchar(MAX)) ' +
            'FROM ' + @tblName + ' (NOLOCK) ' +
            'WHERE CAST(' + @colName + ' AS nvarchar(MAX)) LIKE @SearchPattern'

            EXEC sp_executesql @DynamicSQL, N'@SearchPattern nvarchar(110)', @SearchPattern
        END
    END
END

SELECT NomeTabella, ValoreRicerca FROM #TMP_RESULTS

DROP TABLE #TMP_RESULTS

Prima di pubblicare questa query, vista la complessità, sono stati effettuati i seguenti controlli:

Variabili: Le variabili sono dichiarate e inizializzate correttamente.
Tabella temporanea: La tabella temporanea #Results viene creata correttamente e ha le colonne appropriate.
Ciclo esterno (tabelle): Il ciclo WHILE esterno itera correttamente su tutte le tabelle di base non di sistema.
Ciclo interno (colonne): Il ciclo WHILE interno itera correttamente su tutte le colonne con tipi di dati specificati.
Query dinamica: La query dinamica è costruita correttamente utilizzando sp_executesql per eseguire la ricerca e inserire i risultati nella tabella temporanea.
Conversione dei tipi di dati: I tipi di dati numerici vengono convertiti in nvarchar(MAX) per garantire che l'operatore LIKE funzioni correttamente.
Selezione dei risultati: I risultati vengono selezionati e visualizzati correttamente.
Pulizia: La tabella temporanea #Results viene eliminata alla fine.

Inviato: **22/06/2024, 5:39**

Versione rivista con qualche upgrade al codice originale:

Codice: Seleziona tutto

-- Autore: Lui Francesco
-- Versione: 2.0 (Rev. 01)
-- Data modifica: 21/06/2024
-- Testato su: SQL Server 2016, 2017, 2019, 2022
-- Note: Ricerca valore in tutte le tabelle

-- Licenza: Questo script è distribuito con licenza Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0).
--          È possibile utilizzarlo per scopi personali e non commerciali, ma non è consentito modificarlo o redistribuirlo senza il permesso dell'autore.
--          Per ulteriori informazioni, visitare https://creativecommons.org/licenses/by-nc-nd/4.0/

DECLARE @SearchValue nvarchar(100)
SET @SearchValue = 'Valore_Ricerca'

CREATE TABLE #TMP_RESULTS (
    NomeTabella nvarchar(370), 
    NomeColonna nvarchar(128), 
    TipoDato nvarchar(128), 
    ValoreRicerca nvarchar(MAX)
)

SET NOCOUNT ON

DECLARE @tblName nvarchar(256), @colName nvarchar(128), @dataType nvarchar(128)
DECLARE @SearchPattern nvarchar(110)
SET @tblName = ''
SET @SearchPattern = '%' + @SearchValue + '%'

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

    WHILE (@tblName IS NOT NULL) AND (@colName IS NOT NULL)
    BEGIN
        SET @colName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = PARSENAME(@tblName, 2)
              AND TABLE_NAME = PARSENAME(@tblName, 1)
              AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext', 'int', 'bigint', 'decimal', 'numeric', 'float', 'real')
              AND QUOTENAME(COLUMN_NAME) > @colName
        )

        IF @colName IS NOT NULL
        BEGIN
            SET @dataType =
            (
                SELECT DATA_TYPE
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = PARSENAME(@tblName, 2)
                  AND TABLE_NAME = PARSENAME(@tblName, 1)
                  AND COLUMN_NAME = PARSENAME(@colName, 1)
            )
            
            DECLARE @DynamicSQL nvarchar(MAX)
            SET @DynamicSQL = 
            'INSERT INTO #TMP_RESULTS (NomeTabella, NomeColonna, TipoDato, ValoreRicerca) ' +
            'SELECT ''' + @tblName + ''', ''' + @colName + ''', ''' + @dataType + ''', CAST(' + @colName + ' AS nvarchar(MAX)) ' +
            'FROM ' + @tblName + ' (NOLOCK) ' +
            'WHERE CAST(' + @colName + ' AS nvarchar(MAX)) LIKE @SearchPattern'

            EXEC sp_executesql @DynamicSQL, N'@SearchPattern nvarchar(110)', @SearchPattern
        END
    END
END

SELECT NomeTabella, NomeColonna, TipoDato, ValoreRicerca FROM #TMP_RESULTS

DROP TABLE #TMP_RESULTS

Versione aggiornata

Codice: Seleziona tutto

-- Autore: Lui Francesco
-- Versione: 2.0 (Rev. 01)
-- Data modifica: 22/06/2024
-- Testato su: SQL Server 2016, 2017, 2019, 2022
-- Note: Ricerca valore in tutte le tabelle

-- Licenza: Questo script è distribuito con licenza Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0).
--          È possibile utilizzarlo per scopi personali e non commerciali, ma non è consentito modificarlo o redistribuirlo senza il permesso dell'autore.
--          Per ulteriori informazioni, visitare https://creativecommons.org/licenses/by-nc-nd/4.0/

-- Dichiarazione dei parametri
DECLARE @SearchValue NVARCHAR(100)
SET @SearchValue = 'Valore_Ricerca'

-- Tabella temporanea per i risultati
CREATE TABLE #TMP_RESULTS (
    NomeTabella NVARCHAR(370), 
    NomeColonna NVARCHAR(128), 
    TipoDato NVARCHAR(128), 
    ValoreRicerca NVARCHAR(MAX)
)

-- Variabile per la query dinamica
DECLARE @DynamicSQL NVARCHAR(MAX)

-- Query principale per ottenere le tabelle e colonne di interesse
SELECT @DynamicSQL = COALESCE(@DynamicSQL + '; ', '') + 
    'INSERT INTO #TMP_RESULTS (NomeTabella, NomeColonna, TipoDato, ValoreRicerca) ' +
    'SELECT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''', ''' + QUOTENAME(c.name) + ''', ''' + c.system_type_name + ''', ' +
    'CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)) ' +
    'FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WITH (NOLOCK) ' +
    'WHERE CAST(' + QUOTENAME(c.name) + ' AS NVARCHAR(MAX)) LIKE @SearchPattern'
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.type = 'U' -- Solo tabelle utente
    AND c.system_type_id IN (167, 175, 231, 239) -- Tipi di dati considerati per la ricerca: 167 (varchar), 175 (char), 231 (nvarchar) e 239 (nchar)
    AND s.name + '.' + t.name > ISNULL(@tblName, '')

-- Esecuzione della query dinamica con parametro
EXEC sp_executesql @DynamicSQL, N'@SearchPattern NVARCHAR(100)', @SearchPattern = '%' + @SearchValue + '%'

-- Selezione dei risultati dalla tabella temporanea
SELECT NomeTabella, NomeColonna, TipoDato, ValoreRicerca FROM #TMP_RESULTS

-- Pulizia della tabella temporanea
DROP TABLE #TMP_RESULTS

Roba da smanettoni - Cluster

Query per cercare qualunque valore in tutte le tabelle di un database SQL Server

Query per cercare qualunque valore in tutte le tabelle di un database SQL Server

Re: Query per cercare qualunque valore in tutte le tabelle di un database SQL Server