Searching all Columns in all Tables in a Database

Category : Blogs Published : January 30, 2013 User Rating : 4 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




I was recently asked if I could do a quick search on a SQL Server database to find any references to a particular network folder. A user application generated a set of files which were stored at this location. The user wanted to move the files and repoint the application at the new location by updating any database columns that referred to the old location.

He was a bit surprised when I said that a search facility wasn’t available in SQL Server. I can see his logic – after all databases are all about searching. However, as we know, it’s the application that is usually responsible for implementing SQL code for whatever searching is required.

That set me thinking as to how I could write some SQL that would query all string columns in all tables in a database to find a specific string, and return how many matches there were in each column.

This is the solution I came up with :

DECLARE @SQL VARCHAR(MAX)
DECLARE @SearchString VARCHAR(100)
SET @SQL=''

-- ------------------------------------------
-- Enter the string to be searched for here :
--
SET @SearchString='bracket'
-- ------------------------------------------

SELECT   @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
         + C.name +
'''+'' on table '' + ''' + SC.name + '.' + T.name +
         ''' [Matches for '''+@SearchString+''':] FROM ' +

         QUOTENAME(SC.name) + '.' + QUOTENAME(T.name) + ' WHERE ' + QUOTENAME(C.name) +
         ' LIKE ''%' +
@SearchString +
         '%'' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10)

FROM     sys.columns C
JOIN     sys.tables T
ON       C.object_id=T.object_id
JOIN     sys.schemas SC
ON       SC.schema_id=T.schema_id
JOIN     sys.types ST
ON       C.user_type_id=ST.user_type_id
JOIN     sys.types SYST
ON       ST.system_type_id=SYST.user_type_id
AND      ST.system_type_id=SYST.system_type_id
WHERE    SYST.name IN ('varchar','nvarchar','text','ntext','char','nchar')
ORDER BY T.name, C.name

-- Strip off the last UNION ALL
IF LEN(@SQL)>12
  SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)

EXEC(@SQL)

--PRINT @SQL
If I run this SQL against a database in SQL Server Management Studio it will search all string based columns (varchar, nvarchar, text, ntext, char, nchar) in all tables in the current database, for the specified string (in this case 'bracket').

When run against the AdventureWorksLT2008R2 database (one of the Microsoft sample databases available for download from the CodePlex website) it returns the following results :

Results for searching all columns in all tables in a database
As you can see it returns the number of matches on each column where there is at least one match. I've been using this script for a while and it works in all of the databases that I've used (mostly SQL 2005, 2008 and 2008R2). It is schema aware, so will check schemas other than 'dbo'. It'll also work with user defined types. However I'm sure there are situations where it won’t work - XML datatypes for one.

Obviously for production databases you need to use this script with care, especially if there are a lot of columns to search. In that situation it might be better to use a lightweight profiler trace, and see how the application is getting hold of the data.

Please let me know if you find the script of use.
Link back to this article : http://www.sqlmatters.com/Articles/Searching all columns in all tables in a database.aspx

Keywords

SQL,scripts,columns,tables


Comments
Post by M Allen on Mon 22 Jul 2013 17:58. Report Inappropriate Post
Website : N.A
Try this one out too:

http://www.sqlservercentral.com/scripts/String/89388/
Post by neeraj on Fri 21 Feb 2014 16:41. Report Inappropriate Post
Website : http://www.passionforsql.com
Good evening friends..I have heard this question many times "can we search a particular text in whole database?" There is no direct way of searching a particular text in whole database but we can write a stored procedure which can search a particular text in whole database in all text columns like(varchar, nvarchar,text,ntext,char,nchar).

Here is the code :- Please feel free to ask any question, if you find any difficulty to understand this code.

create procedure usp_find_string(@string as varchar(1000))
as
begin
declare @mincounter as int
declare @maxcounter as int
declare @stmtquery as varchar(1000)
set @stmtquery=''
create table #tmp(tablename varchar(128),columnname varchar(128),rowid int identity)
create table #tablelist(tablename varchar(128),columnname varchar(128))
declare @tmp table(name varchar(128))
declare @tablename as varchar(128)
declare @columnname as varchar(128)

insert into #tmp(tablename,columnname)
select a.name,b.name as columnname from sysobjects a
inner join syscolumns b on a.name=object_name(b.id)
where a.type='u'
and b.xtype in(select xtype from systypes
where name='text' or name='ntext' or name='varchar' or name='nvarchar' or name='char' or name='nchar')
order by a.name

select @maxcounter=max(rowid),@mincounter=min(rowid) from #tmp
while(@mincounter <= @maxcounter )
begin
select @tablename=tablename, @columnname=columnname from #tmp where rowid=@mincounter
set @stmtquery ='select top 1 ' + '[' +@columnname+']' + ' from ' + '['+@tablename+']' + ' where ' + '['+@columnname+']' + ' like ' + '''%' + @string + '%'''
insert into @tmp(name) exec(@stmtquery)
if @@rowcount >0
insert into #tablelist values(@tablename,@columnname)
set @mincounter=@mincounter +1
end
select * from #tablelist
end

I hope you will find this article helpful. :)
Post by naveen kumar on Thu 04 Sep 2014 06:11. Report Inappropriate Post

hi sir how do u use this stored procedure in html page to display in a repeater or a grid view please provide the necessary details
Post by Colleen on Tue 05 May 2015 19:33. Report Inappropriate Post

Just what I needed! Thanks!
Post by Sunil on Wed 10 Jun 2015 18:57. Report Inappropriate Post

Thank you this is very useful
Post by Anvesh on Fri 19 Feb 2016 09:08. Report Inappropriate Post

Nice Article !

Really this will help to people of SQL Server Community.
I have also prepared small note on this, How to find all columns information of SQL Server Database.

http://www.dbrnd.com/2015/10/sql-server-script-to-find-all-columns-information-of-a-database/
Post by Kodanda Rama on Tue 22 Mar 2016 18:58. Report Inappropriate Post

Thank You
Post by Geethika on Wed 14 Sep 2016 05:08. Report Inappropriate Post

I need to find the count of the keyword in the entire database. I'm doing this using python connection. Will this work?
Post by Jenni on Fri 09 Jun 2017 16:32. Report Inappropriate Post

Thank you so much for this!

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered