SQL – Buscar un dato o campo en todas las tablas de la base de datos en SQL Server

10253

Es común que necesitemos buscar un dato o campo en todas las tablas de la base de datos, mas cuando no somos los que desarrollamos la base de datos podemos tener complicaciones para encontrar algunos campos para poder reportearlos lo que deseemos.

Por lo tanto esta ocasión veremos como buscar un dato o campo en todas las tablas de la base de datos en SQL Server.

También te puede interesar buscar dentro de procedimientos almacenados o SQL – Buscar tabla o columna en una base de datos de SQL Server

Query para crear el procedimiento almacenado para realizar búsquedas

Primeramente con este query se crea el procedimiento anidado que utilizaremos para realizar la búsqueda de los datos o campos en toda las tablas de la base de datos.

Donde utilizaremos la linea de código “EXEC SearchAllTables ‘Busqueda’ GO”para convocar la búsqueda.

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	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 NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		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 NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

Query para buscar un dato o campo en todas las tablas de la base de datos

Finalmente para llamar / convocar la búsqueda debemos ejecutar este query cambiando ‘Busqueda’ por el texto del campo o dato que deseemos buscar, nos realizara la búsqueda de este en todas las tablas y columnas de la base de datos.