Sql-server – way to run this query in all databases ? ( SQL Server 2008 R2 )

sql-server-2008-r2t-sql

How can I use this query for each database?

I can easily use sp_msforeachdb but it breaks the results for every database. So I tried to insert the results in a temp table, but it's giving me syntax error.

It is a query to find a string inside the result set of a column. I have REDGATE SQL SEARCH but it only searches for objects. I need to find, inside every table, every column of every database a certain string.

This is the query:

DECLARE @SearchStr nvarchar(100) = 'SEARCH_STRING_HERE'
DECLARE @Results TABLE (TABELA nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

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

WHILE @TableName IS NOT NULL

BEGIN
    SET @TABELA = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                         QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@TABELA IS NOT NULL)

    BEGIN
        SET @TABELA =
        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         
                    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @TABELA
        )

        IF @TABELA IS NOT NULL

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

END

SELECT  distinct db_name(db_id()), * FROM @Results

EDIT1:

Just to show you, this is what I get with sp_msforeachdb ( I know it's a bad Idea to use this, if someone knows a better way to do this, I would be grateful):
enter image description here

I need something like a simple query result.

This is the query with sp_msforeachdb

exec sp_msforeachdb 'use [?]  ;
DECLARE @SearchStr nvarchar(100) = ''abortion''
DECLARE @Results TABLE (TABELA nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

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

WHILE @TableName IS NOT NULL

BEGIN
    SET @TABELA = ''''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = ''BASE TABLE''
            AND    QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                         QUOTENAME(TABLE_NAME)
                         ), ''IsMSShipped''
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@TABELA IS NOT NULL)

    BEGIN
        SET @TABELA =
        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         
                    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'', ''int'', ''decimal'')
                AND    QUOTENAME(COLUMN_NAME) > @TABELA
        )

        IF @TABELA IS NOT NULL

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

END

SELECT  distinct db_name(db_id()), * FROM @Results'

Best Answer

First, create a temporary table (not a table variable) to hold the results:

SET NOCOUNT ON;
GO
CREATE TABLE #results
(
  db SYSNAME,
  [schema] SYSNAME,
  [table] SYSNAME, 
  [column] SYSNAME,
  ColumnValue NVARCHAR(3640)
);
GO

Now, the following gets a little convoluted, because this is like peeling an onion. We need to build dynamic SQL commands that query directly against tables, based on the innermost scope (which is dynamic SQL against each database's metadata), and finally execute all of that for each database.

DECLARE @search NVARCHAR(100) = N'%foo%', -- N prefix is important!
  @sql          NVARCHAR(MAX) = N'',
  @cmd          NVARCHAR(MAX) = N'';

-- build a command per database
SELECT @sql += N'EXEC ' + QUOTENAME(name) 
  + '.sys.sp_executesql @cmd, N''@search NVARCHAR(MAX)'', @search;'
  FROM sys.databases 
  WHERE database_id > 4 AND [state] = 0 AND user_access = 0 AND is_read_only = 0;

-- need an inner command to build a separate query
-- for every string-based column
SET @cmd = N'DECLARE @inner NVARCHAR(MAX) = N''''; 
  SELECT @inner += CHAR(10) + N''SELECT 
        [db]     = DB_NAME(),
        [schema] = N'''''' + s.name + '''''', 
        [table]  = N'''''' + t.name + '''''',
        [column] = N'''''' + c.name + '''''',
        ColumnValue = LEFT('' + QUOTENAME(c.name) + '',3640)
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @search;'' 
  FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239);

  EXEC sys.sp_executesql @inner, N''@search NVARCHAR(100)'', @search;';

-- now multi-nested actual execution
INSERT #Results(db,[schema],[table],[column],ColumnValue) 
  EXEC [master].sys.sp_executesql @sql, 
  N'@cmd NVARCHAR(MAX), @search NVARCHAR(100)', @cmd, @search;

Now, select the results and clean up:

SELECT db,[schema],[table],[column],ColumnValue 
  FROM #Results 
  ORDER BY db,[schema],[table],[column],ColumnValue;

DROP TABLE #Results;

This solution only deals with string-based columns; in your question you list int and decimal, but that strikes me as odd...

Related Question