Written by:
9/8/2009 10:33 PM 

Another Great SQL Server Grep Function

The first SQL Server Grep function I posted is one of the most popular items on the site to today therefore I thought I would share another great version of the MS SQL string search function you can use to search for terms within your database. This version uses a temporary table to store the results from a cursor and then displays the results to the output screen before dropping the temporary table.

create procedure searchforstring
@searchstring varchar(100)
as
begin
declare @SQLString as varchar(1000)
declare @TableName as varchar(255)
declare @ColumnName as varchar(255)
declare @Count as int

DECLARE C_SOURCE CURSOR FOR  
select sysobjects.name tablename,syscolumns.name columnname,'select top 1 * 
from [' + sysobjects.name + '] 
where ' + syscolumns.name 
+' like ''%' + @searchstring + '%'' ' SQL_String
from syscolumns , sysobjects, systypes
where sysobjects.xtype='U' and sysobjects.id = syscolumns.id  and 
systypes.xtype=syscolumns.xtype
and systypes.name <> 'sysname'
and systypes.name  in ('varchar','char','nvarchar','nchar','text','text')
order by sysobjects.name,syscolumns.colid

create table #result(outputstring varchar(500))

OPEN C_SOURCE  
FETCH NEXT FROM C_SOURCE INTO @TableName,@ColumnName,@SQLstring
WHILE @@FETCH_STATUS = 0  

begin
exec(@SQLString)
--print @SQLString
if @@rowcount > 0
        insert into #result values( '[' + @TableName + '].' + @ColumnName)

FETCH NEXT FROM C_SOURCE INTO @TableName,@ColumnName,@SQLstring
END  
CLOSE C_SOURCE  
DEALLOCATE C_SOURCE  
select * from #result
drop table #result
END

Trackback Print

Tags:

Categories:

Location: Blogs CapturedTech - Technology

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code Enter the code shown above in the box below
Add Comment   Cancel