uso - ¿Cómo encuentro un valor en cualquier lugar de una base de datos de SQL Server?
substring sql (14)
Dado un #, ¿cómo descubro en qué tabla y columna se puede encontrar?
No me importa si es rápido, solo necesita trabajar.
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'';
Basándome en la respuesta de bnkdev, modifiqué el Código de Narayana para buscar todas las columnas, incluso las numéricas.
Se ejecutará más lento, pero esta versión en realidad encuentra todas las coincidencias, no solo las encontradas en las columnas de texto.
No puedo agradecer a este tipo lo suficiente. 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
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
Es posible que deba crear un índice invertido para su base de datos. Se asegura que será bastante rápido.
Esta es mi opinión independiente sobre esta pregunta que utilizo para mi propio trabajo. Funciona en SQL2000 y superior, permite comodines, filtrado de columnas y buscará la mayoría de los tipos de datos normales.
Se podría select * from * where any like ''foo'' descripción de pseudo-código 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 proceso porque no quiero mantenerlo en cientos de bases de datos y es realmente para el trabajo ad-hoc de todos modos. Por favor, siéntase libre de comentar sobre correcciones de errores.
Gracias por el guión realmente útil.
Es posible que deba 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
Optimicé la respuesta de Allain Lalonde ( https://.com/a/436676/412368 ). Los valores numéricos todavía son compatibles. Debería 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
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
Probablemente tenga que escribir un script corto para consultar los metadatos (en este caso, una lista de tablas / columnas) de la base de datos, y emitir una serie de declaraciones de selección en busca del valor.
Si necesita realizar dicha búsqueda solo una vez, es probable que pueda utilizar cualquiera de los scripts que se muestran aquí. Pero de lo contrario, recomendaría usar ApexSQL Search para esto. Es un complemento SSMS gratuito y realmente me ahorró mucho tiempo.
Antes de ejecutar cualquiera de los scripts anteriores, debe personalizarlo en función del tipo de datos que desee consultar. Si sabe que está buscando una columna de fecha y hora, no es necesario buscar a través de las columnas nvarchar. Esto acelerará todas las consultas anteriores.
Tengo una solución desde hace un tiempo que seguí mejorando. También realiza búsquedas dentro de columnas XML si se le indica que lo haga, o busca valores enteros si proporciona una cadena de solo enteros.
/* 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/
Una vez escribí una herramienta para que yo hiciera exactamente eso:
Es gratis y de código abierto:
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 Proc. Almacenado del enlace anterior. El único cambio que hice fue sustituir la tabla temporal por una variable de tabla para que no tenga que recordar 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
-- 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