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

By | enero 30, 2014

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

Content Protection by DMCA.com

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

  1. Portal+Tips Post author

    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.

    Reply
  2. Erik calderon Post author

    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

    Reply
  3. Alberto Ojeda Post author

    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%

    Reply
  4. Jorge R Cobian N. Post author

    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

    Reply

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *