Zoek in tabellen naar een waarde

Vandaag kreeg ik een leuk verzoek van een collega: Kun je in een database in alle tabellen zoeken naar een bepaalde tekst? Lees mee hoe je dit met wat eenvoudige T-SQL statements kunt uitvoeren.

De vraag kan als volgt opgevat worden: zoek in alle kolommen van alle tabellen naar een tekst

De kolommen van een tabel zijn in sys.columns te vinden. Om alleen de user tabellen te vinden is een selectie uit sys.tables voldoende. Aangezien we op een tekstfragment zoeken, hoeven we alleen die kolommen te selecteren die tekst bevatten. Dit zijn de kolommen met als type TEXT, NTEXT, VARCHAR, CHAR, NVARCHAR en NCHAR. Deze informatie is te vinden in de kolom sys.types. Ik gebruik de system_type_id kolom in de query.
Het resultaat sla ik in een tijdelijke tabel op. In deze tijdelijke tabel sla ik ook nog de inhoud van de kolom op.

De code is als volgt: 
set nocount on
go
declare @tablename sysname
declare @columnname sysname
declare @sqlcmd nvarchar(max)
declare @zoekcriteria nvarchar(256)
create table ##zoeklijst(tablename sysname, columnname sysname, inhoud nvarchar(max))
set @zoekcriteria = '%index%'
declare c_lus cursor for
select b.name, a.name
from sys.columns a
join sys.tables b
on a.object_id = b.object_id
where a.system_type_id in (35,99,167,175, 231,239)
order by 1,2
open c_lus
fetch next from c_lus into @tablename, @columnname
while @@fetch_status = 0
begin
set @sqlcmd = 'insert into ##zoeklijst select ' + quotename(@tablename, char(39)) + ','+  quotename(@columnname, char(39))+ ',' + quotename(@columnname) + ' from ' + quotename(@tablename) + ' where ' + quotename(@columnname) + ' like ' + quotename(@zoekcriteria, char(39))
print @sqlcmd
exec(@sqlcmd)
fetch next from c_lus into @tablename, @columnname
end
close c_lus
deallocate c_lus
select * from ##zoeklijst
drop table ##zoeklijst