Procedimiento Almacenado para buscar texto en todas las tablas y en todas las columnas

Por medio de este sp se puede buscar una cadena en toda una base, se busca en todas las tablas y en todas las columnas:


exec ToolsSearchAllTablesAllColumns 'Ezequiel'

ColumnName                                             ColumnValue
-----------------------------------------                      ---------------------------------
[dbo].[TABLA_A].[Apellido y Nombre]        Gonzalez, Ezequiel
[dbo].[TABLA_B].[apeynom]                      LIJTINSTENS, EZEQUIEL
[dbo].[TABLA_C].[cysfirdes]                      LEG 98488 AVA EZEQUIEL



CREATE PROC [dbo].[ToolsSearchAllTablesAllColumns]

@SearchStr nvarchar(100)

AS
BEGIN
-- Purpose: To search all columns in all tables for a given search string
-- Written by: Francisco Tapia
-- Site: http://sqlthis.blogspot.com
-- Inspired by: Narayana Vyas Kondreddi http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
-- Tested on: SQL Server 7.0, 2000 and 2005
-- Date modified: June 23, 2009

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT

SELECT @TableName = '', @ColumnName = ''
SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')

WHILE (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName = (
SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '|' + QUOTENAME(C.Column_name))
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName

)

SET @Parse = PATINDEX ('%|%', @ColumnName)

SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING(' + @ColumnName + ',1, 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
)
END
END


SELECT ColumnName, ColumnValue FROM #Results
ORDER BY ColumnName


END

GO