Busca en todas las tablas de una BD

--*** PROCEDIMIENTO ALMACENADO PARA HACER UNA BUSQUEDA ***--

--*** EN CAMPOS ALFANUMERICOS DE TODAS LAS TABLAS DE UNA ***--

--*** BASE DE DATOS EN ESPECIFICO. ***--

--*** FECHA DE CREACION: 12 DE JUNIO DEL 2006 ***--

--*** CREADO POR: ELENA LOPEZ ***--

CREATE PROCEDURE BuscarTodasTablas (@SearchStr nvarchar(100))ASBEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULLBEGINSET @ColumnName = ''SET @TableName = (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableNameAND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)BEGINSET @ColumnName =(SELECT MIN(QUOTENAME(COLUMN_NAME))FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)AND TABLE_NAME = PARSENAME(@TableName, 1)AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')AND QUOTENAME(COLUMN_NAME) > @ColumnName)
IF @ColumnName IS NOT NULLBEGININSERT INTO #ResultsEXEC('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)ENDEND END
SELECT ColumnName, ColumnValue FROM #ResultsEND
GO