use VMAPPL_DEV -- database
go
-- use it directory
create proc p_search
@str Nvarchar(1000)
as
if @str is null return
declare @s Nvarchar(4000)
create table #t(tablename sysname,fieldname sysname)
declare tb cursor local for
select s='if exists(select 1 from ['+replace(b.name,']',']]')+'] where ['+a.name+'] like N''%'+@str+'%'')
print ''Position in table and filed: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xtype in(175,239,99,35,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go
exec p_search 'aa' -- search content
go
go
-- use it directory
create proc p_search
@str Nvarchar(1000)
as
if @str is null return
declare @s Nvarchar(4000)
create table #t(tablename sysname,fieldname sysname)
declare tb cursor local for
select s='if exists(select 1 from ['+replace(b.name,']',']]')+'] where ['+a.name+'] like N''%'+@str+'%'')
print ''Position in table and filed: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xtype in(175,239,99,35,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go
exec p_search 'aa' -- search content
go