Inicio Programación

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

Logo - Buscar un dato o campo en todas las tablas de la base de datos


Ultima Actualización del Artículo: Jun 4, 2017

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.

EXEC SearchAllTables 'Busqueda'
GO

Creditos a Narayana Vyas Kondreddi

Artículos que te pueden interesar:
 
COMPARTIR
Mi nombre es Fernando, soy analista de sistemas actualmente trabajo en una empresa con mas de 200 empleados que utilizan equipo de computo al cual se les da soporte en el departamento. Uno de mis pasatiempos fuera de la empresa es escribir artículos para PortalMasTips donde documento los problemas, inquietudes y detalles interesantes que se presentan.

5 Comentarios

  1. Hola muy buen aporte gracias tengo una consulta a ver si puedes ayudarme como puedo mediante un procedimiento almacenado saber si alguien modifica o borra un registro de una tabla

    • Buen dia Erik

      En muchas bases de datos se crea un Log el cual puedes leer para encontrar que movimientos han realizado, al igual muchos desarrolladores crean una tabla como bitacora para facilitar encontrar este tipo acciones.
      Desde el log puedes ejecutar el Query en tu base de datos.

      	 
      select * FROM   fn_dblog(null,null)
      

      Obtendras toda la información donde la columna Operation, Transaction ID, Transaction SID son los que interesan Operation es donde muestra la accion realizada en base a lo que busques puedes filtrar la informacion.

      una vez que obtengas el Transaction SID en ID del usuario de la base de datos se puede obtener por medio de

      	 
      USE MASTER
      GO   
      SELECT SUSER_SNAME(Transaction_SID_ENCONTRADO) 
      

      te dara el nombre de usuario.

      Saludos.

  2. buen dia, me llamo jorge estuve intentado hacer lo de la busqueda de un dato en las tablas de una BD y se creo bien el procedure pero cuando corro el script me sale es sgte error:
    Msg 0, Level 11, State 0, Line 0
    Error grave en el comando actual. Los resultados, si los hay, se deben descartar.

    que puede ser, espero respuesta… salu2

  3. Excelente scritp para búsquedas, muchas gracias por compartir.

    En cuanto al error de Jorge R Cobian N te sugiero revises muy bien lo que escribiste, pues el script funciona al 100%

Deja un comentario...