|
|
Te anexo el store, se prueba: searchalltables 'midato'
create proc searchalltables
(
@searchstr nvarchar(100)
)
as
begin
-- copyright © 2002 narayana vyas kondreddi. all rights reserved.
-- purpose: to search all columns of all tables for a given search string
-- written by: narayana vyas kondreddi
-- site: http://vyaskn.tripod.com
-- tested on: sql server 7.0 and sql server 2000
-- date modified: 28th july 2002 22:50 gmt
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
|