28

May

spFindWord: Ricerca testo a tappeto su SQL

Ciao a tutti

 

tempo fa ho già bloggato su questo argomento, ma ritorno a parlarne perché è sempre attuale

 

l’SQL per creare la SP è questo:

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spFindWord
    @serchstring varchar(100),
    @contains bit = 0,
    @text bit = 0
AS
BEGIN
SET NOCOUNT ON;


declare @allowedTypes table(name varchar(100))
CREATE TABLE #foundValues (tab varchar(100), col varchar(100));
CREATE TABLE #foundResult ([table] varchar(100), [column] varchar(100), value varchar(max));

INSERT INTO @allowedTypes SELECT 'nvarchar'
INSERT INTO @allowedTypes SELECT 'varchar'
   
IF(@text <> 0)
BEGIN
INSERT INTO @allowedTypes SELECT 'ntext'
INSERT INTO @allowedTypes SELECT 'text'
END

insert into #foundValues
    SELECT h.name + '.' + t.name, c.name
    from sys.tables t
    inner join sys.all_columns c on c.object_id=t.object_id
    inner join sys.types as s on s.user_type_id = c.user_type_id
    inner join sys.schemas h on h.schema_id = t.schema_id
    where s.name in (SELECT name FROM @allowedTypes)

declare @sql varchar(max);

WHILE (SELECT COUNT(*) FROM #foundValues) > 0
BEGIN

declare @tab varchar(100);
declare @col varchar(100);

SELECT TOP 1 @tab=f.tab, @col=f.col FROM #foundValues f

IF @contains <> 0
EXEC (N'
    INSERT INTO #foundResult
        SELECT ''' + @tab + ''', ''' + @col + ''',' + @col + ' FROM ' + @tab + ' WHERE ' + @col + ' LIKE ''%' + @serchstring + '%''
        ')
else
EXEC (N'
    INSERT INTO #foundResult
        SELECT ''' + @tab + ''', ''' + @col + ''',' + @col + ' FROM ' + @tab + ' WHERE ' + @col + ' = ''' + @serchstring + '''
        ')

DELETE FROM #foundValues WHERE col = @col and tab = @tab

END

select * from #foundResult

DROP TABLE #foundValues
DROP TABLE #foundResult
   
END
GO

 

WARNING: l’SP non è thread-safe

 

per usarla: @serchstring è la stringa da ricercare nelle colonne varchar/nvarchar, @contains è opzionale e serve per attivare il LIKE (ricerca contiene), @text è opzionale e serve per attivare la ricerca nelle colonne di tipo text/ntext

 

a presto

by Antonio Esposito on 5/28/2012