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