question

Wes Winkler avatar image
Wes Winkler asked

Finding text somewhere in a database

I am trying to find all occurrences of a string, say "foo", within a database. The problem arises when working on an older database, where I've forgotten details of the database structure. But, I know the data is in there somewhere!

The database is NOT configured for Full Text indexing, nor can it be.

Is there an efficient way to query against any/all text or character columns of ALL tables within a database?

My approach would be to build cursors against tables from sys.objects and the tables' columns from sys.columns, determining the column type. If the column type is compatible with a string search, query that table and column for my search string.

Is there a better way?

t-sqlsearch
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

This article I wrote some time ago may be of help: http://www.sqlservercentral.com/articles/Search/64877/ That is about finding text anywhere within a single table. You can combine that with sp_msforeachtable (though note that is undocumented) in order to search all the tables in the database.

There are some limitations as it does not work well with text/ntext/xml fields, and be aware that this can take a very long time indeed with a large database.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Wes, can you offer any details on the database - size, number of tables, no of rows etc? This sounds like it could be a server killer... Is the server running other databases/systems too? Are you aiming to run this script during 'out of hours' times? Do you have a test rig you can wreck while you are testing?

As Timothy has mentioned, there are certain types of column that dont play nicely when you are looking to find matching strings. You may be able to CAST or CONVERT them to something that will but that is more overhead on your search process.

If I HAD to do this sort of job I think I would look to build a list of the columns that it could possibly be in ie There is no point trying to search a salary column for a string value so work from syscolumns to get the columns of the right data type into a table somewheres, along with the table and schema they belong to. Once the list was built then I would use that to simply generate a script that I want to run. By that I mean, if the column details were in table ColDetails,

USE [tempdb]
go
--  DROP TABLE [dbo].[ColDetails]

CREATE TABLE ColDetails
    (
      ColSchema NVARCHAR(30) ,
      ColTable NVARCHAR(30) ,
      ColName NVARCHAR(30)
    )

INSERT INTO [dbo].[ColDetails]
        ( [ColSchema] ,
          [ColTable] ,
          [ColName]
        )
VALUES
        ( 'dbo' , -- ColSchema - NVARCHAR(30)
          'Peoples' , -- ColTable - NVARCHAR(30)
          'Description'  -- ColName - NVARCHAR(30)

        )

SELECT
    'SELECT ' + ColDetails.colName + ' FROM [' + ColDetails.ColSchema
    + '].[' + ColDetails.ColTable + '] WHERE ' + ColDetails.ColName
    + ' LIKE yourstringhere' AS TSQL
FROM
    coldetails

This will give you a select statement per column you want to query and can be used to group these queries by table and or schema so that you can break down the work needed. Simply use the above to generate the script, copy the output into a new script window and execute it in manageable chunks.

Sorry its not a complete answer but its a bit of a guess until we get some more info on the problem

cheers

Jonathan

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have now had chance to view Timothy's link and see he has suggested a similar process. Sorry for duplication!
0 Likes 0 ·
Never anything wrong with having variations on a theme.
0 Likes 0 ·
The database is of modest size; 120 tables, with less than 100,000 rows in the largest table. Thanks to both of you. Your comments and suggestions will help me solve my problem.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.