[SQL Server Tip] Cercare un testo in tutto il database
Durante lo sviluppo di un'applicazione è nata l'esigenza di poter effettuare delle ricerche di testo all'interno di tutte le colonne (testo) di tutte le tabelle. Naturalmente facendo in modo che questo meccanismo continui a funzionare anche nel momento in cui dovessero essere aggiunte tabelle e/o colonne.
In pratica poter chiamare una procedura, passando una stringa di ricerca, ed avere come risultato l'elenco delle tabelle (e colonne) in cui questo testo è stato trovato.
Quindi avere delle istruzioni in grado di:
- recuperare l'elenco di tutte le colonne testo di tutte le tabelle
- costruire, per ciascuna riga del nostro elenco, un'istruzione di select con opportuna clausola WHERE ... LIKE ...
- memorizzare la [tabella].[colonna] che ha prodotto il risultato
Abbiamo esteso da subito questa procedura in modo tale da poter avere, se richiesto, anche il numero di occorrenze trovate.
Ovvero abbiamo messo a disposizione due parametri:
- @str nvarchar(200) : per poter passare il testo da ricercare
- @countOccurence bit = 0 : (opzionale) per specificare se contare anche il numero di occorrenze
Per arrivare al risultato e poter fare un test costruisco un database di esempio:
use master
go
if exists (select name from sys.databases where name = N'test')
drop database test
GO
create database test
go
use test
go
if object_id('clienti') is not null drop table clienti
if object_id('fornitori') is not null drop table fornitori
if object_id('magazzini') is not null drop table magazzini
go
create table clienti (id int identity(1,1), codCliente varchar(100))
create table fornitori (id int identity(1,1), codFornitore varchar(100))
create table magazzini (id int identity(1,1), codMagazzino varchar(100))
go
insert clienti values ('rossi')
insert clienti values ('rossini')
insert clienti values ('bianchi')
insert fornitori values ('neri')
insert fornitori values ('blu')
insert fornitori values ('gialli')
insert magazzini values ('rossi')
insert magazzini values ('rossi')
insert magazzini values ('viola')
go
select * from clienti
select * from fornitori
select * from magazzini
go
Queste le tabelle popolate con i dati di prova:
Questa la procedura:
if exists (select 1 from INFORMATION_SCHEMA.ROUTINES
where SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'up_searchDb')
drop procedure dbo.up_searchDb
go
create procedure dbo.up_searchDb
(
@str nvarchar(200),
@countOccurence bit = 0
)
as
begin
set nocount on
set @str = QUOTENAME('%' + @str + '%','''')
declare @tsql nvarchar(2000)
declare @tableName nvarchar(200)
declare @value nvarchar(200)
select
table_schema + '.' + table_name as tableName,
column_name as columnName
into #columnList
from INFORMATION_SCHEMA.COLUMNS
where data_type IN ('char', 'varchar', 'nchar', 'nvarchar')
order by table_name, table_schema
create table #search
(
tableName nvarchar(200),columnName nvarchar(200), value nvarchar(200), occurence int
)
while exists (select 1 from #columnList)
begin
select top 1 @tableName = tableName, @value = columnName from #columnList
if (@countOccurence = 1)
begin
set @tsql = 'SELECT ''' + @tableName + ''',''' + @value + ''', ' + @value + ', count(1)
FROM ' + @tableName + ' (NOLOCK) WHERE ' + @value + ' LIKE ' + @str + ' GROUP BY ' + @value
end
else
begin
set @tsql = 'SELECT TOP 1 ''' + @tableName + ''',''' + @value + ''', '''', NULL
FROM ' + @tableName + ' (NOLOCK) WHERE ' + @value + ' LIKE ' + @str
end
print @tsql
insert #search
exec sp_executesql @tsql
delete from #columnList where tableName = @tableName and columnName = @value
end
select * from #search order by tableName, value
end
go
E due esempi di chiamata:
exec dbo.up_searchDb @str = 'rossi', @countOccurence = 0
exec dbo.up_searchDb @str = 'rossi', @countOccurence = 1
