CapturedTech.com

Captured Technology

Another Great SQL Server Grep Function

Sep 08 2009

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

Blog Directory

Latest technology news.
 Patrick Stevens
 533  246383  3/28/2024

FaceBook

Translate