Find the needle in the SQL database

[AKA: SQL Grep]

[Updated Nov 11 2008]

[Updated March 10 2009]

A few years ago I was debugging an annoying SharePoint 2003 issue with some wrong usernames after renaming them. It turned out that I had to dig deep into the SQL database tables to figure out how everything worked (nobody cares about the actual result anymore).

To do that I had to write a piece of SQL scripting that could search the database like: “search for a string in any column in any record in any table in the current database”. The script was actually quite useful (and hard to write) so I’ve kept it for a few years.

The other day I had another strange problem with the new SharePoint server and had to search the database for a specific guid. I dusted off my old script and it turned out to be very useful again. I updated it slightly and figure that it might be useful for you too.

So here it is.

There are really two parameters that you need to provide. “@searchText” is the search string and “@datatype” is the datatype to search (i.e. what types of columns that should be searched).

“@datatypes” values (select system_type_id, name from sys.types order by name):

Datatype value

SQL DataType

null

All types of strings*

-1

All types of numbers*

127

Bigint

173

Binary

104

Bit

175

Char

61

Datetime

106

Decimal

62

Float

34

Image

56

Int

60

Money

239

Nchar

99

Ntext

108

Numeric

231

Nvarchar

59

Real

58

smalldatetime

52

Smallint

122

Smallmoney

98

sql_variant

231

Sysname

35

Text

189

Timestamp

48

Tinyint

36

uniqueidentifier

165

Varbinary

167

Varchar

241

Xml

* I added special support for the first two options

Remarks:

  • The script is not actually a function or a stored procedure because I usually use it by copying the SQL into a SQL query window and execute. I don’t want to create a new function/stored procedure in the databases that I’m peeking into.
  • You can only search datatypes that are convertible to a “nvarchar” column (and only the first 4096 characters are searched), i.e. pretty much everything barring “image” and “timestamp” (see table on http://msdn2.microsoft.com/en-us/library/ms187928.aspx)
  • Every value that is searched is converted to a “nvarchar” column by using default options, i.e. dates and thousand separators will follow your database settings
  • The reason I convert everything is that if you search for a number you probably want to search all number like columns, not just “short” or “long” etc., likewise you’ll want to search every type of text column, not just the one of “char”, “varchar”, “nvarchar”, “text” etc.
  • Is it slow? Absolutely. I wanted to find the string I was searching for, not a query to be part of any production systems
  • Add “%” to search for wildcards (i.e. before and after your string)
  • No it does not search every database only the current one. It’s easy/possible to add a search through all databases, but I didn’t really need it
  • Output is a list of found values with table name, column name and the actual value found (might be interesting if you used wildcards)

Finally! The actual script (copy/paste + modify params):

declare @searchText varchar(255)
set @searchText = '%mystring%'

declare @datatype int
set @datatype = null

/*Set type of coloumn to search

null All types of strings*
-1 All types of numbers*
127 Bigint
173 binary
104 Bit
175 Char
61 datetime
106 decimal
62 Float
34 image
56 Int
60 money
239 nchar
99 ntext
108 numeric
231 nvarchar
59 Real
58 smalldatetime
52 smallint
122 smallmoney
98 sql_variant
231 sysname
35 text
189 timestamp
48 tinyint
36 uniqueidentifier
165 varbinary
167 varchar
241 xml
*/

declare @innerSQL varchar(4000)

DECLARE tables CURSOR READ_ONLY
FOR (
	select so.name as TableName, sc.name as ColName, ss.name as sch
	from syscolumns sc left join sysobjects so
	on sc.id = so.id
	left join sys.all_objects ao
	on so.id = ao.object_id
	left join sys.schemas ss
	on ao.schema_id = ss.schema_id
	where OBJECTPROPERTY(so.id, N'IsUserTable') = 1
		and (sc.xtype = @datatype
		or (@datatype is null and collation is not null)
		or (@datatype = -1 and sc.xprec>0))) -- to include text only

create table ##found ( tablename varchar(255), colname varchar(255), val nvarchar(4000) )

DECLARE @tablename varchar(255)
declare @colname varchar(255)
declare @sch varchar(255)

OPEN tables

FETCH NEXT FROM tables INTO @tablename, @colname, @sch

WHILE (not @@fetch_status = -1)
BEGIN
	IF (not @@fetch_status = -2)
	BEGIN
		--print 'table: ' + @sch + '.' + @tablename + ', column: '+ @colname
		set @innerSql = 'insert into ##found (tablename, colname, val) select '''
		+ @sch + '.' + @tablename + ''', ''' + @colname + ''', Convert(nvarchar(4000),['
		+ @colname + ']) from ' + @sch + '.[' +@tablename + '] where Convert(nvarchar(4000),['
		+ @colname + ']) like ''' + @searchText + ''' '

		exec( @innerSql )

	END
	FETCH NEXT FROM tables INTO @tablename, @colname, @sch
END

CLOSE tables

DEALLOCATE tables

--Output result
select *
from ##found
drop table ##found

I hope some of you will find it useful, if not at least I have a place to find it the next time I need it 😉

Enjoy.

Download

[Added March 10 2009]

For your convenience I’ve added download links

Sql Grep (2005 version)

Sql Grep (2000 version) – Thanks Sootie!

Advertisements

About Søren Nielsen
Long time SharePoint Consultant.

21 Responses to Find the needle in the SQL database

  1. Eddie says:

    Hi Soren,

    This looks like a very useful SQL script, which will be very handy when trying to find something in an unknown database. Thank you!

    I am however having difficulty parsing the script in SQL Server Management Studio, if I just copy and paste it. I’m not an expert in SQL, so I can’t change the copied script to parse either. Can you please post or send me the script in a .txt/.sql file?

    Many thanks
    Eddie

  2. Edward says:

    Just wondering if you actually tried to run your script.

    Msg 102, Level 15, State 1, Line 69
    Incorrect syntax near ‘ + @colname + “‘.
    Msg 105, Level 15, State 1, Line 71
    Unclosed quotation mark after the character string ‘

    exec( @innerSql )

    END
    FETCH NEXT FROM tables INTO @tablename, @colname
    END

    /*CLOSE tables

    DEALLOCATE tables

    /*Output result*/*/
    select *
    from ##found
    drop table ##found
    ‘.

  3. Hi Edward and Eddie

    Thank you for your comments. I’ve used the script many times and it works just fine.

    However… Word or WordPress did mangled both the quotes and the comments quite a bit. I’ve now updated the script above, so you should now be able to do a copy and paste directly into SQL server manager.

    This time I tested it twice 😉

  4. Jeff Pratte says:

    Soren,

    Thanks so much! Script worked beautifully the first time I tried it.

    But then I tried it on a different database where the tables where not the normally:

    dbo.sometable
    dbo.someothertable

    they are all

    heat.sometable
    heat.someothertable

    Can you give me a hint on how to modify your script to work on these type of databases?

    Thanks, Jeff

  5. Hi Jeff

    Glad it worked for you. I just updated the script to include the schema name.

    Try it again and let me know if it works for you.

    /Søren

  6. Des Lynam says:

    Hi Soren,
    Have you got a version that works for SQL 2000?

  7. Hi Des

    Sorry, no. The first version of this script was actually based on SQL 2000, but that’s a while ago (a couple of years) and it was a lot more specialized at the time.

    I don’t have readily access to a SQL 2000 server at the moment, so it’s a bit hard for me to fix whatever errors that may occur.

    I don’t think that I use any 2005 specific syntax, so it’s probably more a question of fixing whatever system tables that I use, that may have changed since 2000.

    What error do you see?

  8. tinny says:

    very cool script! Good job!

  9. dlcollins says:

    9/24/08: Thanks very cool script, got it to work on 2000 db’s pretty easy.

    I think this will be a huge help as time goes by, not just for today

  10. Blartman says:

    Sir, I am indebted to you. Exactly what I was looking for and it does exactly what it says on the tin!

    Thank you so much!

  11. Treebeard says:

    Brilliant, worked first time. Very helpful

    Thanks!!

  12. Rob Dunn says:

    Søren –

    Hi there, this looks SUPER handy. However, I get the following error (we ARE running SQL 2000):

    server(domain\robd): Msg 208, Level 16, State 1, Line 44
    Invalid object name ‘sys.all_objects’.
    server(domain\robd): Msg 208, Level 16, State 1, Line 44
    Invalid object name ‘sys.schemas’.

    Let me know what I need to do – thanks!

  13. Hi Rob

    I have no SQL server 2000 to test it on, but try this:

    Change
    – sys.schemas to sysusers
    – sys.all_objects to sysobjects

    I just did a quick google on the missing views and that is their equivalent in SQL2000.

    However that is assuming that the schema is the same (or at least the columns I’m using).
    Try it.
    You are likely to get some new errors, but hopefully they are easy to solve

    Good luck 😉

  14. Sootie says:

    Good script, it didn’t find what i was looking for but thats not the scripts fault.

    SQL 2000 working version is below.

    declare @searchText varchar(255)
    set @searchText = ‘%mystring%’

    declare @datatype int
    set @datatype = null

    /*Set type of coloumn to search

    null All types of strings*
    -1 All types of numbers*
    127 Bigint
    173 binary
    104 Bit
    175 Char
    61 datetime
    106 decimal
    62 Float
    34 image
    56 Int
    60 money
    239 nchar
    99 ntext
    108 numeric
    231 nvarchar
    59 Real
    58 smalldatetime
    52 smallint
    122 smallmoney
    98 sql_variant
    231 sysname
    35 text
    189 timestamp
    48 tinyint
    36 uniqueidentifier
    165 varbinary
    167 varchar
    241 xml
    */

    declare @innerSQL varchar(4000)

    DECLARE tables CURSOR READ_ONLY
    FOR (
    select so.name as TableName, sc.name as ColName, ss.name as sch
    from syscolumns sc left join sysobjects so
    on sc.id = so.id
    left join sysobjects ao
    on so.id = ao.id
    left join sysusers ss
    on ao.uid = ss.uid
    where OBJECTPROPERTY(so.id, N’IsUserTable’) = 1
    and (sc.xtype = @datatype
    or (@datatype is null and collation is not null)
    or (@datatype = -1 and sc.xprec>0))) — to include text only

    create table ##found ( tablename varchar(255), colname varchar(255), val nvarchar(4000) )

    DECLARE @tablename varchar(255)
    declare @colname varchar(255)
    declare @sch varchar(255)

    OPEN tables

    FETCH NEXT FROM tables INTO @tablename, @colname, @sch

    WHILE (not @@fetch_status = -1)
    BEGIN
    IF (not @@fetch_status = -2)
    BEGIN
    –print ‘table: ‘ + @sch + ‘.’ + @tablename + ‘, column: ‘+ @colname
    set @innerSql = ‘insert into ##found (tablename, colname, val) select ”’
    + @sch + ‘.’ + @tablename + ”’, ”’ + @colname + ”’, Convert(nvarchar(4000),[‘
    + @colname + ‘]) from ‘ + @sch + ‘.[‘ +@tablename + ‘] where Convert(nvarchar(4000),[‘
    + @colname + ‘]) like ”’ + @searchText + ”’ ‘

    exec( @innerSql )

    END
    FETCH NEXT FROM tables INTO @tablename, @colname, @sch
    END

    CLOSE tables

    DEALLOCATE tables

    –Output result
    select *
    from ##found
    drop table ##found

  15. Thanks sootie!

    Excellent work, many people have been asking for this.

    I’ve added download links as well.

  16. scoob says:

    EXCELLENT!!!

    Just what the doctor ordered.

    There are several posts for (n)char/string only searches but I really like(read:NEEDED) the flexibility to select the data type.

    Thanks so much for the effort and the post!

  17. vikpaw says:

    Thanks so much. :O)
    The script is awesome, really useful for me to investigate a particularly complicated database.
    cheers

  18. Cicciomatto says:

    You’re a GENIUS!!!! Thanks a lot 🙂

  19. Edward says:

    Hi Soren, Thanks very much for the script. I know it’s been awhile since you’ve looked at this, but is there any chance you could post how to search all DBs?

    • Søren Nielsen says:

      Hi Edward

      Well I actually haven’t made that script as I haven’t found the need yet – usually I can guess the correct database name in or two tries (in sharepoint).

      It would need to be a dynamic one looping over the database list from the master database views and executing the script above. It might take me a while to do it as there are quite a number of small posts and tasks in my list atm.

      Not hard but it will likely take a couple of hours.

  20. Nick says:

    Worked perfectly. I have limited access to our dbs so your use of a temp table was just what I was after. Thank you very much.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: