Como administrador de base de datos (DBA), hay momentos en los que necesitas encontrar si un valor de cadena específico existe en alguna columna de tu base de datos de SQL Server. Si bien existen procedimientos almacenados del sistema disponibles para buscar un valor en cada base de datos o cada tabla, no hay un procedimiento almacenado del sistema incorporado que te permita buscar un valor en cada columna. Esto significa que debes construir manualmente una consulta para buscar en cada columna utilizando el operador OR.
Sin embargo, hay una forma de generar dinámicamente una consulta para buscar un valor en todas las columnas de tipo de datos de texto utilizando T-SQL y tablas o vistas del sistema. Al crear un procedimiento almacenado, puedes buscar fácilmente un valor en todas las columnas relevantes.
Opción 1 – Usando un cursor (Método original)
El método original implica el uso de un cursor para iterar a través de cada columna y construir la consulta de búsqueda. Aquí tienes un ejemplo del procedimiento almacenado:
USE master
GO
CREATE PROCEDURE dbo.sp_FindStringInTable
@stringToFind VARCHAR(100),
@schema sysname,
@table sysname
AS
SET NOCOUNT ON
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)
BEGIN TRY
SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE'
SET @where = ''
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @schema + '''
AND TABLE_NAME = ''' + @table + '''
AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'
EXEC (@cursor)
OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> ''
SET @where = @where + ' OR'
SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + ''''
FETCH NEXT FROM col_cursor INTO @columnName
END
CLOSE col_cursor
DEALLOCATE col_cursor
SET @sqlCommand = @sqlCommand + @where
PRINT @sqlCommand
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
PRINT 'Se produjo un error. Verifica que el objeto exista.'
PRINT error_message()
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
Opción 2 – No usar un cursor (Nuevo método)
Un enfoque más nuevo y más simple elimina la necesidad de un cursor. Este método es útil cuando la configuración de la base de datos tiene los cursores configurados como locales. Aquí tienes un ejemplo del procedimiento almacenado:
USE master
GO
CREATE PROCEDURE dbo.sp_FindStringInTable
@stringToFind VARCHAR(max),
@schema sysname,
@table sysname
AS
SET NOCOUNT ON
BEGIN TRY
DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE '
SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
EXEC (@sqlCommand)
PRINT @sqlCommand
END TRY
BEGIN CATCH
PRINT 'Se produjo un error. Verifica que el objeto exista.'
PRINT error_message()
END CATCH
Después de crear el procedimiento almacenado, puedes marcarlo como un procedimiento almacenado del sistema utilizando el siguiente comando:
USE master
GO
EXEC sys.sp_MS_marksystemobject sp_FindStringInTable
GO
Ejemplos de uso
Una vez que se haya creado el procedimiento almacenado, puedes realizar pruebas para buscar valores específicos en tu base de datos. Aquí tienes algunos ejemplos:
Buscar el valor ‘Irv%’ en la tabla Person.Address:
USE AdventureWorks
GO
EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'
Buscar el valor ‘%land%’ en la tabla Person.Address:
USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%', 'Person', 'Address'
Buscar el valor ‘%land%’ en la tabla Person.Contact:
USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'
Otra versión para mostrar las columnas que coinciden
Si deseas ver qué columnas coinciden junto con los datos de la tabla, puedes utilizar el siguiente procedimiento almacenado:
CREATE PROCEDURE dbo.sp_FindStringInTable_with_flag
@stringToFind VARCHAR(max),
@schema sysname,
@table sysname
AS
SET NOCOUNT ON
BEGIN TRY
DECLARE @sqlCommand varchar(max) = 'SELECT '
SELECT @sqlCommand = @sqlCommand + 'CASE WHEN [' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' THEN 1 ELSE 0 END AS ' + COLUMN_NAME + '_found, '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
SELECT @sqlCommand = @sqlCommand + ' * FROM [' + @schema + '].[' + @table + '] WHERE '
SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
EXEC (@sqlCommand)
PRINT @sqlCommand
END TRY
BEGIN CATCH
PRINT 'Se produjo un error. Verifica que el objeto exista.'
PRINT error_message()
END CATCH
Después de crear el procedimiento almacenado, puedes marcarlo como un procedimiento almacenado del sistema utilizando el siguiente comando:
USE master
GO
EXEC sys.sp_MS_marksystemobject sp_FindStringInTable_with_flag
GO
Luego puedes ejecutar el procedimiento almacenado de la siguiente manera:
USE AdventureWorks
GO
EXEC sp_FindStringInTable_with_flag 'Irv%', 'Person', 'Address'
Esto mostrará los resultados junto con una bandera que indica si cada columna coincide con la cadena de búsqueda.
Eso es todo. Una vez que hayas creado estos procedimientos almacenados, puedes usarlos para buscar un valor de cadena específico en cualquier tabla y cualquier base de datos en tu servidor de SQL Server.
Última actualización del artículo: 2021-07-22