¿Cómo puedo encontrar un valor en cualquier lugar en una base de datos SQL Server?

Dado un #, ¿cómo descubro en qué tabla y columna se puede encontrar?

No me importa si es rápido, solo tiene que funcionar.

Esto podría ayudarte . – de Narayana Vyas. Busca todas las columnas de todas las tablas en una base de datos dada. Lo he usado antes y funciona.

Este es el procedimiento almacenado del enlace anterior; el único cambio que hice fue sustituir la tabla temporal por una variable de tabla para que no tenga que acordarse de soltarla cada vez.

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT DECLARE @Results TABLE(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 

Si necesita ejecutar dicha búsqueda solo una vez, probablemente pueda ir con cualquiera de los scripts que ya se muestran aquí. Pero, de lo contrario, recomendaría usar ApexSQL Search para esto. Es un complemento gratuito de SSMS y realmente me ahorró mucho tiempo.

Antes de ejecutar cualquiera de los scripts anteriores, debe personalizarlo en función del tipo de datos que desea ver. Si sabe que está buscando una columna de fecha y hora, entonces no hay necesidad de buscar columnas nvarchar. Esto acelerará todas las consultas anteriores.

Basado en la respuesta de bnkdev, modifiqué el Código de Narayana para buscar todas las columnas, incluso las numéricas.

Funcionará más lento, pero esta versión realmente encuentra todas las coincidencias no solo las encontradas en las columnas de texto.

No puedo agradecerle lo suficiente a este tipo. Me salvó días de búsqueda a mano!

 CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT 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 QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results END 

Esta es mi opinión independiente sobre esta cuestión que uso para mi propio trabajo. Funciona en SQL2000 y superior, permite el uso de caracteres comodín, filtrado de columnas y buscará la mayoría de los tipos de datos normales.

Una descripción de pseudocódigo podría select * from * where any like 'foo'

 -------------------------------------------------------------------------------- -- Search all columns in all tables in a database for a string. -- Does not search: image, sql_variant or user-defined types. -- Exact search always for money and smallmoney; no wildcards for matching these. -------------------------------------------------------------------------------- declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+ declare @ColumnName sysname -------------------------------------------------------------------------------- -- SET THESE! -------------------------------------------------------------------------------- set @SearchTerm = N'foo' -- Term to be searched for, wildcards okay set @ColumnName = N'' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols -------------------------------------------------------------------------------- -- END SET -------------------------------------------------------------------------------- set nocount on declare @TabCols table ( id int not null primary key identity , table_schema sysname not null , table_name sysname not null , column_name sysname not null , data_type sysname not null ) insert into @TabCols (table_schema, table_name, column_name, data_type) select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE from INFORMATION_SCHEMA.TABLES t join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME where 1 = 1 and t.TABLE_TYPE = 'base table' and c.DATA_TYPE not in ('image', 'sql_variant') and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end order by c.TABLE_NAME, c.ORDINAL_POSITION declare @table_schema sysname , @table_name sysname , @column_name sysname , @data_type sysname , @exists nvarchar(4000) -- Can be max for SQL2005+ , @sql nvarchar(4000) -- Can be max for SQL2005+ , @where nvarchar(4000) -- Can be max for SQL2005+ , @run nvarchar(4000) -- Can be max for SQL2005+ while exists (select null from @TabCols) begin select top 1 @table_schema = table_schema , @table_name = table_name , @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0' , @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0' , @where = '' from @TabCols order by id while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin select top 1 @column_name = column_name , @data_type = data_type from @TabCols where table_schema = @table_schema and table_name = @table_name order by id -- Special case for money if @data_type in ('money', 'smallmoney') begin if isnumeric(@SearchTerm) = 1 begin set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards end end -- Special case for xml else if @data_type = 'xml' begin set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + '''' end -- Special case for date else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + '''' end -- Search all other types else begin set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + '''' end delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name end set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end' print @run exec sp_executesql @run end set nocount off 

No lo pongo en forma de progtwig ya que no quiero mantenerlo en cientos de bases de datos y de todos modos es realmente para trabajo ad-hoc. Por favor, no dude en comentar sobre las correcciones de errores.

Optimicé la respuesta de Allain Lalonde ( https://stackoverflow.com/a/436676/412368 ). Los valores numéricos aún son compatibles. Debe ser aproximadamente 4-5 veces más rápido (1:03 vs 4:30), probado en un escritorio con una base de datos de 7GB. http://developer.azurewebsites.net/2015/01/mssql-searchalltables/

 IF OBJECT_ID ('dbo.SearchAllTables', 'P') IS NOT NULL DROP PROCEDURE dbo.SearchAllTables; GO CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Customized and modified: 2014-01-21 -- Tested on: SQL Server 2008 R2 DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256) DECLARE @ColumnName nvarchar(128) DECLARE @DataType nvarchar(128) DECLARE @SearchStr2 nvarchar(110) DECLARE @SearchDecimal decimal(38,19) DECLARE @Query nvarchar(4000) SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''') SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(decimal(38,19), @SearchStr) ELSE NULL END PRINT '@SearchStr2: ' + @SearchStr2 PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS nvarchar) SET @TableName = '' 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)) DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint', 'tinyint', 'numeric', 'decimal') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) SET @DataType = ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND QUOTENAME(COLUMN_NAME) = @ColumnName ) PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')' IF @ColumnName IS NOT NULL BEGIN IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal') BEGIN IF @SearchDecimal IS NOT NULL BEGIN SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS nvarchar(110)), 3630) ' + 'FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS nvarchar) PRINT ' ' + @Query INSERT INTO @Results EXEC (@Query) END END ELSE BEGIN SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' + 'FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 PRINT ' ' + @Query INSERT INTO @Results EXEC (@Query) END END END END SELECT ColumnName, ColumnValue FROM @Results END 

Gracias por el guión realmente útil.

Es posible que necesite agregar la siguiente modificación al código si sus tablas tienen campos no convertibles:

 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 NOT IN ('text', 'image', 'ntext') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) 

Chris

Es mi manera de resolver esta pregunta. Probado en SQLServer2008R2

 CREATE PROC SearchAllTables @SearchStr nvarchar(100) AS BEGIN DECLARE @dml nvarchar(max) = N'' IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results CREATE TABLE dbo.#Results ([tablename] nvarchar(100), [ColumnName] nvarchar(100), [Value] nvarchar(max)) SELECT @dml += ' SELECT ''' + s.name + '.' + t.name + ''' AS [tablename], ''' + c.name + ''' AS [ColumnName], CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) AS [Value] FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' (NOLOCK) WHERE CAST(' + QUOTENAME(c.name) + ' AS nvarchar(max)) LIKE ' + '''%' + @SearchStr + '%''' FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c .user_type_id = ty .user_type_id WHERE t.is_ms_shipped = 0 AND ty.name NOT IN ('timestamp', 'image', 'sql_variant') INSERT dbo.#Results EXEC sp_executesql @dml SELECT * FROM dbo.#Results END 

Tengo una solución de hace un tiempo que seguí mejorando. También busca dentro de las columnas XML si se le pide que lo haga, o busca valores enteros si proporciona una cadena de enteros solamente.

 /* Reto Egeter, fullparam.wordpress.com */ DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */ SET @FullRowResult = 1 SET @FullRowResultRows = 3 SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */ SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */ SET @SearchStrInXML = 0 /* Searching XML data may be slow */ IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20)) SET NOCOUNT ON DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110) SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''') DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20)) WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME) AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 ) IF @TableName IS NOT NULL BEGIN DECLARE @sql VARCHAR(MAX) SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2) AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1) AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ') AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)' INSERT INTO @ColumnNameTable EXEC (@sql) WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable) BEGIN PRINT @ColumnName SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue INSERT INTO #Results EXEC(@sql) IF @@ROWCOUNT > 0 IF @FullRowResult = 1 BEGIN SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' + ' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue EXEC(@sql) END DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName END END END SET NOCOUNT OFF SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results GROUP BY TableName, ColumnName, ColumnValue, ColumnType 

Fuente: http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/

Aquí, solución muy dulce y pequeña:

 1) create a store procedure: create procedure get_table @find_str varchar(50) as begin declare @col_name varchar(500), @tab_name varchar(500); declare @find_tab TABLE(table_name varchar(100), column_name varchar(100)); DECLARE tab_col cursor for select C.name as 'col_name', T.name as tab_name from sys.tables as T left outer join sys.columns as C on C.object_id=T.object_id left outer join sys.types as TP on C.system_type_id=TP.system_type_id where type='U' and TP.name in('text','ntext','varchar','char','nvarchar','nchar'); open tab_col fetch next from tab_col into @col_name, @tab_name while @@FETCH_STATUS = 0 begin insert into @find_tab exec('select ''' + @tab_name + ''',''' + @col_name + ''' from ' + @tab_name + ' where ' + @col_name + '=''' + @find_str + ''' group by ' + @col_name + ' having count(*)>0'); fetch next from tab_col into @col_name, @tab_name; end CLOSE tab_col; DEALLOCATE tab_col; select table_name, column_name from @find_tab; end 

=======================

 2) call procedure by calling store procedure: exec get_table 'serach_string'; 

Probablemente tenga que escribir un guión corto para consultar los metadatos (en este caso, una lista de tablas / columnas) de la base de datos, y emitir una serie de declaraciones seleccionadas que busquen el valor.

Otra forma de usar JOIN y CURSOR:

 USE My_Database; -- Store results in a local temp table so that. I'm using a -- local temp table so that I can access it in SP_EXECUTESQL. create table #tmp ( tbl nvarchar(max), col nvarchar(max), val nvarchar(max) ); declare @tbl nvarchar(max); declare @col nvarchar(max); declare @q nvarchar(max); declare @search nvarchar(max) = 'my search key'; -- Create a cursor on all columns in the database declare c cursor for SELECT tbls.TABLE_NAME, cols.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES AS tbls JOIN INFORMATION_SCHEMA.COLUMNS AS cols ON tbls.TABLE_NAME = cols.TABLE_NAME -- For each table and column pair, see if the search value exists. open c fetch next from c into @tbl, @col while @@FETCH_STATUS = 0 begin -- Look for the search key in current table column and if found add it to the results. SET @q = 'INSERT INTO #tmp SELECT ''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%''' EXEC SP_EXECUTESQL @q fetch next from c into @tbl, @col end close c deallocate c -- Get results select * from #tmp -- Remove local temp table. drop table #tmp 

Una vez escribí una herramienta para hacer exactamente eso:

a7 SqlTools

Es gratis y de código abierto:

enlace github

Es posible que necesite construir un índice invertido para su base de datos. Se asegura que es bastante rápido.

 -- exec pSearchAllTables 'M54*' ALTER PROC pSearchAllTables (@SearchStr NVARCHAR(100)) AS BEGIN -- A procedure to search all tables in a database for a value -- Note: Use * or % for wildcard DECLARE @Results TABLE([Schema.Table.ColumnName] NVARCHAR(370), ColumnValue NVARCHAR(3630)) SET NOCOUNT ON DECLARE @TableName NVARCHAR(256) = '' , @ColumnName NVARCHAR(128) , @SearchStr2 NVARCHAR(110) = QUOTENAME(REPLACE(@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 [Schema.Table.ColumnName] , ColumnValue FROM @Results GROUP BY [Schema.Table.ColumnName] , ColumnValue END