Search a string throughout the database

[vimalpercy]

Hello
I have a Database which contains like 1000 Tables.
I am not the designer of that DB.So I need to in which table and which column
that string exists. IS there a DBWIDE String search possible?
Thanks and Regards

[skhanal]

There is no one statement to do that. You have write a stored procedure or script to loop though all tables, all columns and search it.

[MAK]

This statement will generate a bunch of sql statements and you could use to find the table that matches your search criteria

set quoted_identifier off
declare @searchstring varchar(100)
set @searchstring ='%sys%'
Select 'Select "'+object_name(id)+'" as tablename,count(*) as NoofRowsMatch from '+object_name(id) + ' where '+name +' like "'+@searchstring +'"'
from syscolumns where xtype in (175,239,99,231,35,167)

[vimalpercy]

i used your string builder function and run the resulting string set and got this error
Invalid column name

I tried removing the quotes from the tablename but no luck
and since we select a text ie tablename which is not in the table it throws an error.

But it is a very cool idea to get all those tables details
in that way.
Thanks MAK
Once again you have proven you are a real specialist.

[vimalpercy]

set quoted_identifier off
declare @searchstring varchar(100)
set @searchstring ='%sys%'
Select 'Select "'+object_name(id)+'" as tablename,count(*) as NoofRowsMatch from '+object_name(id) + ' where '+name +' like "'+@searchstring +'"'
from syscolumns where xtype in (175,239,99,231,35,167)


In this script
i generated the string and replaced the "" quote with single quote by search and replace method and then it worked.

[MAK]

you could use
set quoted_identifier off
before execute all the select statement

[bobkand]

I tried to execute the script and it given me nearly 2000rows...
But i couldn't understand what exactly it is doing... can some one brief me this.... please

[rmiao]

It generates sql statements to search specified string in every column of every table in the db.

[mikr0s]

/****************************************
SEARCH FOR A STRING THROUGHOUT A DATABASE
****************************************/

CREATE PROCEDURE db_search
@the_text NVARCHAR(100) = NULL
AS

SET NOCOUNT ON

SELECT @the_text = ISNULL(@the_text, '')


DECLARE @runner INT,
@limit INT,
@SQL NVARCHAR(1000),
@is_like TINYINT,
@SQLParams NVARCHAR(30)

DECLARE @mytbl TABLE (ident INT IDENTITY(1, 1),
tbl NVARCHAR(200) COLLATE database_default NULL,
col NVARCHAR(200) COLLATE database_default NULL,
is_like TINYINT NULL)


--Get columns of every table that contain character like, data types
INSERT INTO @mytbl(tbl, col)
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%char%'
OR DATA_TYPE LIKE '%text%'
OR DATA_TYPE LIKE '%uniqueidentifier%'
ORDER BY TABLE_NAME, ORDINAL_POSITION


--Initialize Variables
SELECT @SQLParams = '@res TINYINT OUTPUT', @the_text = '%'+@the_text+'%'

--Virtual Cursor
SELECT @runner = 1, @limit = ISNULL(MAX(ident), 0) -- ISNULL(),actually not needed
FROM @mytbl

--Run through temporary table records
WHILE @runner <= @limit
BEGIN
--Initialize Variables on every pass(build dynamic query)
SELECT @is_like = 0, @SQL = 'SELECT TOP 1 @res = 1 FROM ['+tbl+'] WHERE ['+col+'] LIKE '''+ @the_text +''''
FROM @mytbl
WHERE ident = @runner

--Execute dynamic query and get the output value
EXEC sp_executesql @SQL, @SQLParams,
@res = @is_like OUTPUT

--Set result to current record
UPDATE @mytbl
SET is_like = @is_like
WHERE ident = @runner

--Move to next record
SELECT @runner = @runner + 1
END

SELECT tbl AS TABLE_NAME, col AS COLUMN_NAME
FROM @mytbl
WHERE is_like = 1

--EOF--

--HTH--