How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?
JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>
|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>