Search all tables in all databases on server for a string

Question

Edit: This question was flagged as a duplicate, but it is not. The other answers on SO show how to search all tables in a single database, I need to search all tables in EVERY database on a given server.

I need to search all tables for all databases on a server for a search string. I've got email address littered throughout tables that are going to have a change of domain and I need to prepare a report that shows where these email addresses are located. I am not going to be able to add a stored procedure to all the databases so I need a query to do this that's doesn't involve exec-ing a sp repeatedly. I pulled this code off the net and was using it to search all tables but I haven't been able to figure out how to run it on all databases.

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

SET NOCOUNT ON

DECLARE @SearchStr nvarchar(100), @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @SearchStr = '@domaintobereplaced.com'
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 top 10 ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results

Find `sp_MSforeachdb` at your sql server and use it directly or see its code. — Serg, Aug 08 '16 at 15:16
run on all databases, get them by querying sys.databases and use a cursor, but can you do a USE on a field? Or will you have to build DB name into dynamic SQL - not sure. — Cato, Aug 08 '16 at 15:43
I sure hope this is a one-time investigative thing. If you have to run this routinely there is something horribly wrong. This is not going to be fast no matter how you slice it. You are asking sql server to look in every single column or every single row of every single table in ever single database on your entire server for a specific value. Depending on the system this could literally take hours to run each and every time. — Sean Lange, Aug 08 '16 at 16:41
Possible duplicate of [Find a value and shows me database,table,column,primary key of the found value](http://stackoverflow.com/questions/43385508/find-a-value-and-shows-me-database-table-column-primary-key-of-the-found-value) — Mohammad Reza Shahrestani, May 17 '17 at 19:37
Checkout my answer , It was a stored procedure I changed that for you to a query does the same thing. — Mohammad Reza Shahrestani, May 18 '17 at 04:33

score 1 · Answer 1 · answered Aug 08 '16 at 15:22

First, you have to collect list of all databases' names from sys.databases.
Then you have to create dynamic SQL to extract names of all tables in all databases in format like [database].[schema].[table name]'. You can do it by linking following tables:[database].sys.schemas' & [database].sys.tables'<BR/> Then you get list of all text columns by linking found tables to[database].sys.columns'
When you get all of that, you can create dynamic queries to all your tables and text columns.

BTW If somebody hide data inside of TEXT column you have to include it in your search and do a conversion.

score 1 · Answer 2 · edited Nov 10 '18 at 21:57

Assuming this is a one time thing here is an alternative to the cursor approach. This will build a dynamic string that you can then execute. It does NOT work for every database but you could either run this for each database manually or modify this to use sp_msforeachdb. You may find that this approach is better though since the undocumented foreachdb will sometimes miss databases. https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

DECLARE @MySearchCriteria VARCHAR(500)
SET @MySearchCriteria = '''YourSearchStringHere''' --you do need all these quotation marks because this string is injected to another string.

SELECT 'SELECT ''' + t.name + ''' as TableName, ' + c.columnlist + '] FROM [' + s.name + '].[' + t.name + '] WHERE ' + w.whereclause  as SelectStatement
FROM sys.tables t 
join sys.schemas s on s.schema_id = t.schema_id
CROSS APPLY (
    SELECT STUFF((    
        SELECT '], [' + c.Name AS [text()]
        FROM sys.columns c
        join sys.types t2 on t2.user_type_id = c.user_type_id
        WHERE t.object_id = c.object_id 
            AND c.collation_name IS NOT NULL
            AND c.max_length > 6
            and t2.name not in ('text', 'ntext')
        FOR XML PATH('') 
    ), 1, 2, '' )
) c (columnlist)
CROSS APPLY (
    SELECT STUFF((    
        SELECT ' OR [' + c.Name + '] IN (' + @MySearchCriteria + ')' AS [text()]
        FROM sys.columns c
        join sys.types t2 on t2.user_type_id = c.user_type_id
        WHERE t.object_id = c.object_id 
            AND c.collation_name IS NOT NULL
            AND c.max_length > 6
            and t2.name not in ('text', 'ntext')
        FOR XML PATH('') 
    ), 1, 4, '' )
) w (whereclause)
where c.columnlist is not null
ORDER BY t.name

Mohammad Reza Shahrestani · Answer 3 · 2017-05-18T04:32:21.543

Edited :

My answer was a stored procedure . here the query for you :

Change @SearchTerm for your search.

declare @SearchTerm nvarchar(12)
set @SearchTerm = 'WORD'
CREATE TABLE #results
  (
    [database]   SYSNAME,
    [schema]     SYSNAME,
    [table]      SYSNAME,
    [column]     SYSNAME,
    ExampleValue NVARCHAR(1000)
  );

  DECLARE
    @DatabaseCommands  NVARCHAR(MAX) = N'', 
    @ColumnCommands NVARCHAR(MAX) = N'';

  SELECT @DatabaseCommands = @DatabaseCommands + N'
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql
        @ColumnCommands, N''@SearchTerm NVARCHAR(MAX)'', @SearchTerm;'
    FROM sys.databases
    WHERE database_id  > 4  -- non-system databases
      AND[state]      = 0-- online
      AND user_access  = 0; -- multi-user

    SET @ColumnCommands = N'DECLARE @q NCHAR(1),
          @SearchCommands NVARCHAR(MAX);

SELECT @q = NCHAR(39),
  @SearchCommands = N''DECLARE @VSearchTerm VARCHAR(255) = @SearchTerm;'';

    SELECT @SearchCommands = @SearchCommands + CHAR(10) + N''

      SELECT TOP(1)
        [db]     = DB_NAME(),
        [schema] = N'' + @q + s.name + @q + '', 
        [table]  = N'' + @q + t.name + @q + '',
        [column] = N'' + @q + c.name + @q + '',
        ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 1000)
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE
        WHEN c.system_type_id IN(35, 167, 175) THEN ''V'' 
        ELSE '''' END + ''SearchTerm;'' 

    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)
      AND c.max_length >= LEN(@SearchTerm);

PRINT @SearchCommands;
EXEC sys.sp_executesql @SearchCommands,
  N''@SearchTerm NVARCHAR(255)'', @SearchTerm;';

  INSERT #Results
  (
    [database],
    [schema],
    [table],
    [column],
    ExampleValue
  )
  EXEC[master].sys.sp_executesql @DatabaseCommands,
   N'@ColumnCommands NVARCHAR(MAX), @SearchTerm NVARCHAR(255)', 
    @ColumnCommands, @SearchTerm;

  SELECT[Searched for] = @SearchTerm;

  SELECT[database],[schema],[table],[column],ExampleValue
FROM #Results 
    ORDER BY[database],[schema],[table],[column];

score 0 · Answer 4 · answered Aug 08 '16 at 15:41

I don't know if this would help, it's vb.net code I wrote, it finds the table and field a string is in, potentially searching all DB's - it's just an investigation aid, I've added field types as they've cropped up, but it might give you an idea how I did it anyway

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

    Dim scon As String = String.Format("Data Source={0};Initial Catalog=MASTER;Integrated Security=True", cmbInstance.SelectedItem.ToString)

    Button1.Enabled = False
    Dim Matches As New List(Of Locator)

    With ListVresults

        .Clear()

        .View = View.Details


        .Columns.Add("Database", 200)
        .Columns.Add("Table", 200)
        .Columns.Add("Field", 200)

    End With


    Using con As New SqlConnection(scon),
            da As New SqlDataAdapter("select *, SCHEMA_NAME(schema_id )  as SkeemaName from sys.tables where type = 'U'", con),
            daDB As New SqlDataAdapter("select * from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and state = 0 ORDER BY NAME", con),
            dtDB As New DataTable

        Dim UseLike As Boolean = chkLIKE.Checked
        Dim CompOperator As String = If(chkLIKE.Checked, "LIKE", "=")

        con.Open()


        daDB.Fill(dtDB)

        For Each drdb As DataRow In dtDB.Select(If(cmbDB.SelectedIndex = 0, "", String.Format("name = '{0}'", cmbDB.SelectedItem.ToString)), "name")
            'For Each drdb As DataRow In dtDB.Rows

            con.ChangeDatabase(drdb!name.ToString)

            Using dt As New DataTable
                da.Fill(dt)
                For Each dr As DataRow In dt.Select("", "name")
                    lblProc.Text = String.Format("({2} - {0} - {1}", drdb!name.ToString, dr!name.ToString, Matches.Count)
                    Application.DoEvents()
                    Dim sql As New System.Text.StringBuilder
                    Using dsDatNull As New DataSet,
                        daDatNull As New SqlDataAdapter(String.Format("SELECT * FROM [{0}].[{1}] WHERE 1=0; SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", dr!skeemaName, dr!name), con)

                        daDatNull.Fill(dsDatNull)

                        'SELECT * FROM(INFORMATION_SCHEMA.COLUMNS) WHERE TABLE_NAME = 'absence'

                        sql.AppendFormat("SELECT * FROM [{0}].[{1}] WHERE ", dr!skeemaname, dr!name)



                        Dim bOR As Boolean = False

                        Dim col As DataColumn
                        Dim Q As System.Data.EnumerableRowCollection(Of System.Data.DataRow) = (From x As DataRow In dsDatNull.Tables(1) Where String.Compare(x!column_name.ToString, col.ColumnName, True) = 0 Select x)

                        For Each col In dsDatNull.Tables(0).Columns

                            Dim drColType As DataRow = Q(0)
                            Dim dataType As String = drColType!Data_Type.ToString


                            If "x".GetType Is col.DataType AndAlso (String.Compare(dataType, "nvarchar", True) = 0 _
                                                                        OrElse String.Compare(dataType, "varchar", True) = 0 _
                                                                        OrElse String.Compare(dataType, "nchar", True) = 0 _
                                                                        OrElse String.Compare(dataType, "char", True) = 0) Then



                                If bOR Then sql.Append(" OR ")
                                bOR = True
                                sql.Append("[" + col.ColumnName + "]").Append(If(UseLike, " LIKE ", " = ")).AppendFormat("'{0}{1}{2}'", If(UseLike, "%", ""), txtCode.Text.Replace("'", "''"), If(UseLike, "%", ""))


                            ElseIf (New Guid).GetType Is col.DataType Then

                                If bOR Then sql.Append(" OR ")
                                bOR = True
                                sql.Append("cast([" + col.ColumnName + "] as nvarchar(80))").Append(If(UseLike, " LIKE ", " = ")).AppendFormat("'{0}{1}{2}'", If(UseLike, "%", ""), txtCode.Text.Replace("'", "''"), If(UseLike, "%", ""))


                            End If

                        Next

                        If Not bOR Then sql.Append(" 1=0 ")

                    End Using



                    Using dtDat As New DataTable,
                        daDat As New SqlDataAdapter(sql.ToString, con)
                        daDat.SelectCommand.CommandTimeout = 3600

                        daDat.Fill(dtDat)

                        For Each drDat As DataRow In dtDat.Rows
                            For Each col As DataColumn In dtDat.Columns

                                Dim obj = drDat(col)

                                If String.Compare(obj.ToString, txtCode.Text, True) = 0 OrElse UseLike And obj.ToString.ToLower.Contains(txtCode.Text.ToLower) Then

                                    If Not (From x As Locator In Matches Where x.DB.ToString = drdb!name.ToString And x.Table = dr!name.ToString And x.Column = col.ColumnName).Any Then
                                        Dim newOne As New Locator(drdb!name.ToString, dr!name.ToString, col.ToString)
                                        Matches.Add(newOne)
                                        With newOne
                                            ListVresults.Items.Add(New ListViewItem({.DB, .Table, .Column}))
                                        End With

                                    End If
                                End If

                            Next
                        Next


                    End Using


                Next
            End Using

        Next
    End Using

    lblProc.Text = ""


    Button1.Enabled = True
End Sub

score 0 · Answer 5 · answered Aug 08 '16 at 17:01

This is the script I always use... set the string to search for at the top (see comment) and let it run.

DECLARE @tableName sysname
DECLARE @columnName sysname
DECLARE @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)
DECLARE @minLength int;

SET @value = '%SomeString%' -- *** Set this to the value you're searching for *** --
SET @minLength = LEN(REPLACE(@value, '%', ''));

SET @sqlPreamble = 'IF EXISTS (SELECT 1 FROM '

DECLARE theTableCursor CURSOR FAST_FORWARD FOR 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' 
       AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
     ORDER BY TABLE_NAME

OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
    DECLARE theColumnCursor CURSOR FAST_FORWARD FOR 
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = @tableName 
           AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
           AND (CHARACTER_MAXIMUM_LENGTH >= @minlength OR CHARACTER_MAXIMUM_LENGTH = -1)
         ORDER BY ORDINAL_POSITION

    OPEN theColumnCursor
    FETCH NEXT FROM theColumnCursor INTO @columnName

    WHILE @@FETCH_STATUS = 0 -- spin through Column entries
    BEGIN
        SET @sql = N'[' + @tableName + N'] (nolock) WHERE [' + @columnName + N'] LIKE ''' + @value + 
                   N''') PRINT ''Value found in Table: ' + @tableName + N', Column: ' +  @columnName + N''''
        EXEC (@sqlPreamble + @sql)
        FETCH NEXT FROM theColumnCursor INTO @columnName
    END
    CLOSE theColumnCursor
    DEALLOCATE theColumnCursor

    FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor

score 0 · Accepted Answer · answered Nov 10 '17 at 18:55

This question is a bit old at this point and most of the answers seem to miss the mark in terms of searching ALL servers and ALL databases on each server. Turned out my manager was just trying to give me an impossible and time consuming task aka busy work to stall me because our team was getting disbanded in a re-org and we were all being sent to different parts in the company.

So, for the sake of writing a comprehensive answer I will say my approach now would be to create a console application that accepts a comma separated list of server names. It would loop through all the server names and connect to them one by one. Once in a server I would query for and loop through all the user created databases and run the search all tables query on each one. If results were found I would write them to a file stored in a directory with a [ServerName]_[DatabaseName]_Results title and then use the outputted list of files to review and develop and action plan for further querying/remediation. It doesn't appear from the answers above that there is a way to do this in SQL Server.

Search all tables in all databases on server for a string

6 Answers6

Linked

Related