2/04/2014

Search for Keyword Query in SQL Server Database

I was looking for a solution to search a SQL Server database for every column to find a keyword.

Which brought me to this link.

http://stackoverflow.com/questions/834912/sql-search-for-a-string-in-every-varchar-column-in-a-database

And from here, I modified the code to work in SQL Server (was written for Sybase):

It's not stealing if I give proper credit, right?
-----------------------------------------------------------------------------------------------------
-- SQL Server - SCRIPT TO FIND STRING IN ANY COLUMN IN TABLE AND PRINT TableName/ColumnName TO RESULTS --
-----------------------------------------------------------------------------------------------------
 
set nocount off
 
IF OBJECT_ID(N'tempdb..#SearchString') IS NOT NULL
 BEGIN
     drop table #SearchString
 END
-- CREATE OBJECTS REQUIRED FOR SCRIPT
create table #SearchString (SearchString varchar(100))
go
 
-- SET SEARCH STRING
declare @search_string  varchar(100)
set @search_string = 'Zero'
 
-- WRITE SEARCH STRING TO TEMP TABLE
-- TO STORE IT AWAY AND BE ABLE TO READ
-- IT IN NEXT BATCH
insert into #SearchString (SearchString)
    values (@search_string)
 
IF OBJECT_ID(N'tempdb..#TabCol') IS NOT NULL
 BEGIN
     drop table #TabCol
 END
-- GET ALL RELEVANT TABLES AND COLUMNS
--insert #TabCol
    select object_name(o.id) as TableName, c.name as ColumnName
    into #TabCol
        from sysobjects o, syscolumns c
    where o.type = 'U' -- ONLY USER TABLES
          and c.usertype in (1,2,18,19,24,25,42) -- ONLY LOOK FOR CHAR, VARCHAR, ETC.
          and c.id = o.id
          and c.name is not null
          and c.length >= datalength(@search_string)
go
 
-- GET TOTAL NUMBER OF RELEVANT COLUMNS
select count(*) as RelevantColumns from #TabCol
go
 
-- CREATE CURSOR TO LOOP THROUGH TABLES AND COLUMNS TO FIND COLUMNS CONTAINING THE SEARCH STRING
declare cur cursor for
select TableName, ColumnName from #TabCol order by TableName, ColumnName
for read only
go
 
-- VARIABLE DEFINITION
declare
    @table_name     SYSNAME,
    @table_id       int,
    @column_name    SYSNAME,
    @sql_string     varchar(2000),
    @search_string  varchar(100)
 
-- GET SEARCH STRING FROM TABLE
select @search_string = SearchString from #SearchString
 
-- CURSOR INIT
open cur
 
fetch cur into @table_name, @column_name
 
-- LOOP THROUGH TABLES AND COLUMNS SEARCHING FOR SEARCH STRING AND PRINT IF FOUND
while (@@FETCH_STATUS != -1)
begin
    set @sql_string = 'if exists (select * from ' + @table_name + ' where [' + @column_name + '] like ''%' + @search_string + '%'') print ''' + @table_name + ', ' + @column_name + ''''
    execute(@sql_string)
    fetch cur into @table_name, @column_name
end
go
 
-- CLEAN-UP
close cur
deallocate cur
 
drop table #SearchString
drop table #TabCol
go
 



 

No comments:

Post a Comment

Babalon