SQL Server-Como pesquisar em todas as colunas de todas as tabelas

14 11 2011

 

Untitled77Pesquisar por algo que não sabemos se existe nem onde se encontra, dentro de uma base de dados SQL Server, pode ser uma tarefa complicada e morosa, especialmente se esta tiver muitas tabelas, colunas e muitos registos.

 

Felizmente existe uma forma que facilita este trabalho, criada por Narayana Vyas Kondreddi. Basta criar uma stored procedure,  indicar o termo de pesquisa, e aguardar pelos resultados.

 

Neste artigo, a titulo de exemplo, vou pesquisar pela descrição de um artigo, em toda a base de dados DEMO do Primavera Express.

 

 

Criar a Stored Procedure:

Copie a instrução que se encontra por baixo da imagem, e cole dentro da consola SQL do Administrador Primavera (poderá obviamente utilizar outras ferramentas para o efeito). Execute e aguarde que uma mensagem seja mostrada a informar o resultado da operação.

13-11-2011 19-05-06

 


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 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

 

 

 

Pesquise pelo termo ‘Hand Scanner -TR89’.

EXEC SearchAllTables ‘Hand Scanner -TR89’
GO

13-11-2011 19-06-23

Aguarde até que sejam mostrados os resultados. O tempo irá variar consoante a estrutura da base de dados, o seu tamanho, e a capacidade de processamento existente.

 

 

Resultado:

13-11-2011 19-08-04

Como é possível observar, são mostradas duas colunas. A da esquerda mostra a localização do registo:

[Utilizador do SQL].[Tabela].[Coluna]

À direita são mostrados os registos onde o termo foi encontrado.


Ações

Information

4 responses

14 11 2011
HOAS

Excelente!!!

17 11 2011
João Azevedo

Muito bom, Parabéns

17 11 2011
João Azevedo

Muito bom mesmo, Parabéns.

31 12 2011
Hugo Barros

Fantástico!!!

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s




%d bloggers like this: