texto - Busque una cadena buscando todas las tablas en SQL Server Management Studio 2008

ejemplos de consultas en sql

Question

¿Hay alguna manera de buscar una cadena en todas las tablas de una base de datos en SQL Server Management Studio 2008?

Quiero buscar una cuerda, di john . El resultado debe mostrar las tablas y sus respectivas filas que contienen john .

Answer 1

He escrito un SP para esto que devuelve los resultados de búsqueda en forma de nombre de tabla, los nombres de columna en los que se encontró la cadena de palabras clave de búsqueda y las búsquedas en las filas correspondientes, como se muestra en la siguiente captura de pantalla.

Puede que esta no sea la solución más eficiente, pero siempre puede modificarla y usarla de acuerdo con sus necesidades.

IF OBJECT_ID(''sp_KeywordSearch'', ''P'') IS NOT NULL
    DROP PROC sp_KeywordSearch
GO

CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
    DECLARE @Result TABLE
        (TableName NVARCHAR(300),
         ColumnName NVARCHAR(MAX))

    DECLARE @Sql NVARCHAR(MAX),
        @TableName NVARCHAR(300),
        @ColumnName NVARCHAR(300),
        @Count INT

    DECLARE @tableCursor CURSOR

    SET @tableCursor = CURSOR LOCAL SCROLL FOR
    SELECT  N''SELECT @Count = COUNT(1) FROM [dbo].['' + T.TABLE_NAME + ''] WITH (NOLOCK) WHERE CAST(['' + C.COLUMN_NAME +
            ''] AS NVARCHAR(MAX)) LIKE ''''%'' + @KeyWord + N''%'''''',
            T.TABLE_NAME,
            C.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
    ON      T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
            T.TABLE_NAME = C.TABLE_NAME
    WHERE   T.TABLE_TYPE = ''BASE TABLE'' AND
            C.TABLE_SCHEMA = ''dbo'' AND
            C.DATA_TYPE NOT IN (''image'', ''timestamp'')

    OPEN @tableCursor
    FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Count = 0

        EXEC sys.sp_executesql
            @Sql,
            N''@Count INT OUTPUT'',
            @Count OUTPUT

        IF @Count > 0
        BEGIN
            INSERT  INTO @Result
                    (TableName, ColumnName)
            VALUES  (@TableName, @ColumnName)
        END

        FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
    END

    CLOSE @tableCursor
    DEALLOCATE @tableCursor

    SET @tableCursor = CURSOR LOCAL SCROLL FOR
    SELECT  SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
    FROM    (SELECT R.TableName, (SELECT R2.ColumnName + '', '' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('''')) AS Columns,
                    ''SELECT * FROM '' + R.TableName + '' WITH (NOLOCK) WHERE '' +
                    (SELECT ''CAST('' + R2.ColumnName + '' AS NVARCHAR(MAX)) LIKE ''''%'' + @KeyWord + ''%'''' OR ''
                     FROM   @Result AS R2
                     WHERE  R.TableName = R2.TableName
                    FOR
                     XML PATH('''')) AS Sql
             FROM   @Result AS R
             GROUP BY R.TableName) TB
    ORDER BY TB.Sql

    OPEN @tableCursor
    FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @Sql
        SELECT  @TableName AS [Table],
                @ColumnName AS Columns
        EXEC(@Sql)

        FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
    END

    CLOSE @tableCursor
    DEALLOCATE @tableCursor

END

Answer 2

Intente el procedimiento para buscar todas las columnas de todas las tablas para una cadena de búsqueda determinada:

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

    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'', ''int'', ''decimal'')
                    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

Answer 3

Para actualizar la respuesta de para el servidor SQL 2012. ''FROM '' + @TableName + '' (NOLOCK) '' cambiar: ''FROM '' + @TableName + '' (NOLOCK) '' a FROM '' + @TableName + ''WITH (NOLOCK) '' +

De lo contrario, obtendrá el siguiente error: la Deprecated feature ''Table hint without WITH'' is not supported in this version of SQL Server.

A continuación se muestra el procedimiento almacenado actualizado completo:

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

    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'', ''int'', ''decimal'')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    ''SELECT '''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630) 
                    FROM '' + @TableName + ''WITH (NOLOCK) '' +
                    '' WHERE '' + @ColumnName + '' LIKE '' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Answer 4

Si es como yo y tiene ciertas restricciones en un entorno de producción, puede utilizar una variable de tabla en lugar de tabla temporal, y una consulta ad-hoc en lugar de un procedimiento de creación.

Por supuesto, dependiendo de su instancia de servidor SQL, debe admitir variables de tabla.

También agregué una declaración USE para reducir el alcance de la búsqueda

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = ''SEARCH_TEXT''
DECLARE @Results TABLE (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'', ''int'', ''decimal'')
                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

Answer 5

Un poco tarde pero con suerte útil.

¿Por qué no probar algunas de las herramientas de terceros que se pueden integrar en SSMS?

He trabajado con ApexSQL Search (100% gratis) con buen éxito tanto para la búsqueda de esquemas como de datos y también hay un paquete de herramientas SSMS que tiene esta característica (no gratuita para SQL 2012 pero bastante asequible).

El procedimiento almacenado arriba es realmente genial; es solo que esto es mucho más conveniente en mi opinión. Además, requeriría algunas modificaciones pequeñas si desea buscar columnas de fecha y hora o columnas GUID y tal ...

Answer 6

Un poco tarde, pero puedes encontrar fácilmente una cadena con esta consulta

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = ''StringtoSearch''

DECLARE tables_cur CURSOR FOR SELECT ss.name +''.''+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = ''U''

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = ''IF EXISTS (SELECT * FROM '' + @table_name + '' WHERE ['' + @column_name + ''] 
            LIKE ''''%'' + @search_string + ''%'''') PRINT '''''' + @table_name + '', '' + @column_name + ''''''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur