This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
From the Useful SQL Scripts Series.
This stored procedure, originally written by Oufimtsev Gleb from Moscow, allows you to search all columns in all tables in a database for the occurrence of a particular string or value. Wild card characters such as those supported by the LIKE keyword can be used.
For example: _ to wild card a single character, and % to wild card a number of characters.
Once you have run the script to create the stored procedure you can execute it to look for data, here are some examples:
exec spSearchOnAllDB ‘Sugar%’
exec spSearchOnAllDB ‘%soft%’
exec spSearchOnAllDB ‘_5234_57%’, 1
exec spSearchOnAllDB M_cro_oft
This script is available from other locations around the Internet, but the one attached has had some changes to make it more suitable for working in a Dynamics GP environment, such as granting of access to DYNGRP. It has also been updated to handle table and column long names with spaces or reserved words in them.
Note: To include a single quote (‘) character in the search string, you will need to replace the single quote (‘) with 2 single quotes in a row (”). Doubling up a single quote will prevent the early termination of the search parameter. Once passed to the code, the updated version of the stored procedure will handle doubling up again for the dynamically created scripts allowing the dynamic scripts to run correctly.
The script is available as an attachment at the bottom of this post. Last Modified: 26-Jul-2013.
Note: Uncomment the line granting Execute permissions to DYNGRP if you want other non ‘sa’ users to be able to use the stored procedure in a Dynamics related database.
Also have a look at the follow up post, Updated: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database, which as a Locate Value application that you can use.
David
24-Jun-2009: Added hint about searching for single quotes.
19-Jul-2013: Updated stored procedure code to handle table and column long names with spaces or reserved words in them. Thanks Eduardo for pointing out the problem.
26-Jul-2013: Updated stored procedure code to handle doubling up of the single quote for use with the dynamically created scripts.
27-Sep-2013: Added link to follow up article.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
Winthrop Development Consultants Blog 
From Vaidy Mohan’s Blog
http://vmdyngp.blogspot.com/2008/12/sql-series-from-david-musgrave-team.html
LikeLike
Posting from Dynamics GP Blogster
http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.html
LikeLike
Thanks! Perfect timing! I literally needed to do this today, and was wondering about how to do it.
LikeLike
Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft Dynamics
LikeLike
Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft
LikeLike
Thank you David, very good script.
Hussain Al-Yousef
hyou91@gmail.com
LikeLike
This is great! I hope I can get it to work.
I ran the sp using the example
exec spSearchOnAllDB ‘Sugar%’
Results were returned from the Sugar% but then I also received this message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘Name’.
What have I done wrong?
Leslie
LikeLike
Leslie,
I didn’t have any issues on my TWO database or DYNAMICS db. I’m wondering if you don’t have a table with a column called "Name" in it as I don’t see otherwise in the SQL how this couldn’t work.
if you add right above the exec (sql) the line:
print @sql
exec(@sql)
Then on the Messages tab you’ll see all the SQL statements being generated. Search for "Name" in them and I suspect you’ll see it is there and sql is unhappy about that because it probably is a reserved word.
We might need to add [] around the column and table fields in the SQL script.
LikeLike
Hi! First of all, thanks for the script, it was very usefull.
The problem reported is related with "long names" in tables or column tables. You must add "[" and "]" to queries inside of stored proceduce, to all @tbl and @col variables.
This is the unofficial path: 🙂
.
.
.
if @OutFullRecords=0
begin
set @sql='insert into #tbl_res (TableName,ColumnName,Id,ColumnValue) '
+'select '+char(39)+@tbl+char(39)+', '
+char(39)+@col+char(39)+', '
if @id_present=1 set @sql=@sql+'IDENTITYCOL, '
else set @sql=@sql+'NULL, '
set @sql=@sql+'convert(varchar(7500),['+@col+']) '
+'from ['+@tbl+'] (nolock) '
+'where convert(varchar(8000),['+@col+']) like '+char(39)+@phrase+char(39)
end
if @OutFullRecords=1
begin
set @sql='if exists (select * from '+@tbl+' (nolock) '
+'where convert(varchar(8000),['+@col+']) like '+char(39)+@phrase+char(39)+') '
+'select '+char(39)+@tbl+char(39)+' TableName, '+char(39)+@col+char(39)+' ColumnName, * '
+'from ['+@tbl+'] (nolock) where convert(varchar(8000),['+@col+']) like '+char(39)+@phrase+char(39)
end
.
.
.
LikeLike
Thanks Eduardo!
On hindsight that makes perfect sense but it wasn't obvious to me that really was our issue.
LikeLike
You're welcome, and thanks to you!
I forgot to add the fix in table name with the option "OutFullRecords"
.
.
.
if @OutFullRecords=1
begin
set @sql='if exists (select * from ['+@tbl+'] (nolock) '
.
.
.
LikeLike
Thanks Eduardo
I have updated the code to resolve these issues and format it nicer.
David
LikeLike
Thanks David,
This is fabulous!!
-Victoria
LikeLike
Why include the tip about single quotes in a search string needing to be extended to four quotes instead of just doing this in the script?
LikeLike
Hi Marc
Good idea, but as you need to pass the single quotes inside a string to the stored procedure, you will have to double up the single quotes for the parameter yourself.
I have updated the script to double up again for the dynamically created code and updated the article accordingly.
David
LikeLike
Post from Victoria Yudin at her blog
victoriayudin.com/…/sql-stored-procedure-to-search-an-entire-database
LikeLike
Thanks David!
I find that doing this sort of thing as a stored procedure could encourage people to load the stored procedure onto customer production servers which I don't like, I prefer to just leave it as a query that can be run without creating additional SQL objects
LikeLike
Hi Adriaan
Have a look at blogs.msdn.com/…/updated-spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx
This follow up article has the Locate Value application which can be used without installing a Stored Procedure.
David
LikeLike