spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
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.
Comments
Anonymous
December 01, 2008
From Vaidy Mohan's Blog http://vmdyngp.blogspot.com/2008/12/sql-series-from-david-musgrave-team.htmlAnonymous
December 01, 2008
Posting from Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/12/sql-mania-series-at-developing-for.htmlAnonymous
December 01, 2008
Thanks! Perfect timing! I literally needed to do this today, and was wondering about how to do it.Anonymous
December 10, 2008
Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft DynamicsAnonymous
December 10, 2008
Based on the success of the "Resolving Security Issues in Dynamics GP" located here, MicrosoftAnonymous
February 24, 2009
Thank you David, very good script. Hussain Al-Yousef hyou91@gmail.comAnonymous
July 08, 2009
The comment has been removedAnonymous
July 08, 2009
The comment has been removedAnonymous
July 01, 2013
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 . . .Anonymous
July 01, 2013
Thanks Eduardo! On hindsight that makes perfect sense but it wasn't obvious to me that really was our issue.Anonymous
July 01, 2013
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) ' . . .Anonymous
July 18, 2013
Thanks Eduardo I have updated the code to resolve these issues and format it nicer. DavidAnonymous
July 21, 2013
Thanks David, This is fabulous!! -VictoriaAnonymous
July 21, 2013
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?Anonymous
July 21, 2013
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. DavidAnonymous
July 25, 2013
Post from Victoria Yudin at her blog victoriayudin.com/.../sql-stored-procedure-to-search-an-entire-databaseAnonymous
September 26, 2013
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 objectsAnonymous
September 26, 2013
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