Também discutido aqui: link
Como posso pesquisar um banco de dados inteiro do MS SQL 2005 por um valor? Por exemplo, 'ABC12345'.
Obrigado.
Você consulta INFORMATION_SCHEMA.TABLES para todas as tabelas e consulta cada tabela.
Veja link para detalhes.
JR
Eu encontrei este script para ser útil ...
BEGIN TRAN
declare @search nvarchar(100)
set @search = 'string to search for'
-- search whole database for text
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF nullif(object_id('tempdb..#tmpSearch'), 0) IS NOT NULL DROP TABLE #tmpSearch
CREATE TABLE #tmpSearch (
ListIndex int identity(1,1),
CustomSQL nvarchar(2000)
)
Print 'Getting tables...'
INSERT #tmpSearch (CustomSQL)
select 'IF EXISTS (select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'') BEGIN PRINT ''Table ' + TABLE_NAME + ', Column ' + COLUMN_NAME + ''';select * FROM [' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'' END' FROM information_schema.columns
where DATA_TYPE IN ('ntext', 'nvarchar', 'uniqueidentifier', 'char', 'varchar', 'text')
and TABLE_NAME NOT IN ('table_you_dont_want_to_look_in', 'and_another_one')
Print 'Searching...
'
declare @index int
declare @customsql nvarchar(2000)
WHILE EXISTS (SELECT * FROM #tmpSearch)
BEGIN
SELECT @index = min(ListIndex) FROM #tmpSearch
SELECT @customSQL = CustomSQL FROM #tmpSearch WHERE ListIndex = @index
IF @customSql IS NOT NULL
EXECUTE (@customSql)
SET NOCOUNT ON
DELETE #tmpSearch WHERE ListIndex = @index
SET NOCOUNT OFF
END
print 'the end.'
ROLLBACK
Tags search sql-server-2005