Como procurar na base de dados do servidor sql pelo texto?
Eu sei que é possível, mas não sei como.
Preciso de procurar na Base de Dados SQL da Microsoft por todas as menções de string específicas. Por exemplo: eu gostaria de pesquisar todas as tabelas, vistas, funções, procedimentos armazenados, ... for string "tblEmployes". (Dados não incluídos nos quadros)
uma das razões pelas quais preciso disto, gostaria de remover algumas tabelas de dados extra que são criadas, mas receio que sejam usadas em algum lugar em procedimentos ou funções.
qualquer ajuda é apreciar.
83
12 answers
Isto irá procurar em cada coluna de cada tabela numa base de dados específica. crie o proc armazenado na base de dados em que deseja procurar.
CREATE PROCEDURE FindMyData_String
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO
Para executá-lo, basta fazer isto:
exec FindMyData_string 'google', 0
108
Author: l--''''''---------'''''''''''', 2012-02-07 23:56:03
Se precisar de encontrar objectos de base de dados (por exemplo, tabelas, colunas, gatilhos) pelo nome - dê uma vista de olhos à ferramenta FREE Red-Gate Chamada SQL Search O Que Faz isto - ele procura em toda a sua base de dados por qualquer tipo de texto(s).
É uma grande ferramenta obrigatória para qualquer desenvolvedor de DBA ou banco de dados - já mencionei que é absolutamente livre para usar para qualquer tipo de uso??
55
Author: marc_s, 2012-02-08 06:08:47
Você também pode tentar ApexSQL Search – é uma adição SSMS livre semelhante à pesquisa SQL.
Se realmente quiser usar apenas o sql, talvez queira experimentar este programa
select
S.name as [Schema],
o.name as [Object],
o.type_desc as [Object_Type],
C.text as [Object_Definition]
from sys.all_objects O inner join sys.schemas S on O.schema_id = S.schema_id
inner join sys.syscomments C on O.object_id = C.id
where S.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
and C.text like '%ICE_%'
order by [Schema]
45
Author: George Ober, 2013-03-13 03:33:37
Para obter uma tabela pelo nome no servidor sql:
SELECT *
FROM sys.Tables
WHERE name LIKE '%Employees%'
Para encontrar um procedimento armazenado pelo nome:
SELECT name
FROM sys.objects
WHERE name = 'spName'
Para obter todos os procedimentos armazenados relacionados com uma tabela:
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
15
Author: TheBoyan, 2012-02-07 23:54:12
Pode exportar a sua base de dados (se for pequena) para o seu disco rígido / ambiente de trabalho e, em seguida, fazer uma pesquisa de texto através do editor de texto.
13
Author: Ivan Ivković, 2014-12-11 15:57:37
Este procedimento e função de pesquisa de códigos, mas não na Tabela:)
SELECT name FROM sys.all_objects WHERE Object_definition(object_id) LIKE '%text%' ORDER BY name
5
Author: aykut aydoğan, 2016-04-21 09:47:12
Podias;
- programe a base de dados para um único ficheiro e procure no ficheiro por ligações por 'tblEmployees', usando um texto Editor. No SQL Server Management Tools (SSMS), carregue com o botão direito sobre a base de dados e escolha gerar programas.
- Use as dependências de vista do SSMS ao carregar com o botão direito sobre as ligações para ver quais os outros objectos que estão dependentes dele
- Utilize uma ferramenta de terceiros livre, como o RedGate SQLSearch para procurar todos os objectos da base de dados pelo nome e conteúdo pela palavra-chave.
3
Author: ajayel, 2012-02-07 23:58:41
Isto irá procurar um texto em cada base de dados:
declare @search_term varchar(max)
set @search_term = 'something'
select @search_term = 'use ? SET QUOTED_IDENTIFIER ON
select
''[''+db_name()+''].[''+c.name+''].[''+b.name+'']'' as [object],
b.type_desc as [type],
d.obj_def.value(''.'',''varchar(max)'') as [definition]
from (
select distinct
a.id
from sys.syscomments a
where a.[text] like ''%'+@search_term+'%''
) a
inner join sys.all_objects b
on b.[object_id] = a.id
inner join sys.schemas c
on c.[schema_id] = b.[schema_id]
cross apply (
select
[text()] = a1.[text]
from sys.syscomments a1
where a1.id = a.id
order by a1.colid
for xml path(''''), type
) d(obj_def)
where c.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
and db_id() not in (1,2,3,4) -- avoid sys databases'
if object_id('tempdb..#textsearch') is not null drop table #textsearch
create table #textsearch
(
[object] varchar(300),
[type] varchar(300),
[definition] varchar(max)
)
insert #textsearch
exec sp_MSforeachdb @search_term
select *
from #textsearch
order by [object]
2
Author: kinzleb, 2016-05-19 22:17:47
Pergunta antiga, eu sei, mas aqui vai a minha versão...
Chamei-lhe" agulha no palheiro " por razões óbvias.
Procura por um valor específico em cada linha e em cada coluna, não por nomes de colunas, etc.
Executar a pesquisa (substituir os valores das duas primeiras variáveis, claro):
DECLARE @SEARCH_DB VARCHAR(100)='REPLACE_WITH_YOUR_DB_NAME'
DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%REPLACE_WITH_SEARCH_STRING%'
SET NOCOUNT ON;
DECLARE col_cur CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime');
DECLARE @TOTAL int = (SELECT COUNT(*)
FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime'));
DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500);
DECLARE @SQL nvarchar(4000)='';
PRINT '-------- BEGIN SEARCH --------';
OPEN col_cur;
FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
BEGIN TRY DROP TABLE ##RESULTS; END TRY BEGIN CATCH END CATCH
CREATE TABLE ##RESULTS( TABLE_CATALOG nvarchar(500), TABLE_SCHEMA nvarchar(500), TABLE_NAME nvarchar(500), COLUMN_NAME nvarchar(500), DATA_TYPE nvarchar(500), RECORDS int)
DECLARE @SHOULD_CAST bit=0
DECLARE @i int =0
DECLARE @progress_sum bigint=0
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT '' + CAST(@i as varchar(100)) +' of ' + CAST(@TOTAL as varchar(100)) + ' ' + @TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME+': '+@COLUMN_NAME+' ('+@DATA_TYPE+')';
SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE
WHEN 'varchar' THEN 0
WHEN 'nvarchar' THEN 0
WHEN 'char' THEN 0
ELSE 1 END)
SET @SQL='SELECT '''+@TABLE_CATALOG+''' catalog_name, '''+@TABLE_SCHEMA+''' schema_name, '''+@TABLE_NAME+''' table_name, '''+@COLUMN_NAME+''' column_name, '''+@DATA_TYPE+''' data_type, ' +
+' COUNT(['+@COLUMN_NAME+']) records '+
+' FROM '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME +
+' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['+@COLUMN_NAME + '] as NVARCHAR(max)) ' ELSE ' ['+@COLUMN_NAME + '] ' END
+' LIKE '''+ @SEARCH_VALUE_LIKE + ''' '
-- PRINT @SQL;
IF @i % 100 = 0
BEGIN
SET @progress_sum = (SELECT SUM(RECORDS) FROM ##RESULTS)
PRINT CAST (@i as varchar(100)) +' of ' + CAST(@TOTAL as varchar(100)) +': '+ CAST (@progress_sum as varchar(100))
END
INSERT INTO ##RESULTS (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, RECORDS)
EXEC(@SQL)
FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
SET @i=@i+1
-- IF @i > 1000
-- BREAK
END
CLOSE col_cur;
DEALLOCATE col_cur;
SELECT * FROM ##RESULTS WHERE RECORDS>0;
Então para ver os resultados, mesmo enquanto executa, a partir de outra janela, execute:
DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%@FLEX@%'
SELECT * FROM ##RESULTS WHERE RECORDS>0;
SET NOCOUNT ON;
DECLARE col_cur CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ##RESULTS WHERE RECORDS>0;
DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500);
DECLARE @SQL nvarchar(4000)='';
OPEN col_cur;
FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
DECLARE @i int =0
DECLARE @SHOULD_CAST bit=0
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE
WHEN 'varchar' THEN 0
WHEN 'nvarchar' THEN 0
WHEN 'char' THEN 0
ELSE 1 END)
SET @SQL='SELECT '''+@TABLE_CATALOG+''' catalog_name, '''+@TABLE_SCHEMA+''' schema_name, '''+@TABLE_NAME+''' table_name, '''+@COLUMN_NAME+''' column_name, '''+@DATA_TYPE+''' data_type, ' +
+' ['+@COLUMN_NAME+']'+
+', * '
+' FROM '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME +
+' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['+@COLUMN_NAME + '] as NVARCHAR(max)) ' ELSE ' ['+@COLUMN_NAME + '] ' END
+' LIKE '''+ @SEARCH_VALUE_LIKE + ''' '
PRINT @SQL;
EXEC(@SQL)
FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
SET @i=@i+1
-- IF @i > 10
-- BREAK
END
CLOSE col_cur;
DEALLOCATE col_cur;
- Ele usa cursores em vez de um bloqueador. loop
- Pode imprimir o progresso (se necessário)
- Ele pode sair após algumas tentativas (descomentar o se no final)
- mostra todos os registos
- podes sintonizá-lo conforme necessário
Declarações de exoneração de Responsabilidade:
- Não o executem em ambientes de produção!
É lento. Se o DB for acessado por outros serviços / usuários, por favor adicione "com (NOLOCK)" após cada nome da tabela em todas as seleções, especialmente a seleção dinâmica o.
- não valida / protege contra todo o tipo de opções de injecção de SQL.
- Se o seu DB é enorme, prepare-se para algum sono, certifique-se de que a consulta não será morta após alguns minutos.
- lança alguns valores para string, incluindo Ents/bigints/smallints / tinyints. Se você não precisar deles, coloque - os nas mesmas listas de exclusão com as datas no topo do script.
2
Author: user682385, 2017-01-11 18:27:44
Foi dado acesso a uma base de dados, mas não à tabela onde a minha consulta estava a ser armazenada.
Inspirado pela resposta de @marc_s, eu dei uma olhada em HeidiSQL que é um programa do Windows que pode lidar com MySQL, MSSQL e PostgreSQL.
Descobriu que também pode procurar numa base de dados por um texto.
Ele vai procurar em cada tabela e dar-lhe quantas vezes no texto encontrado por tabela!
1
Author: Ari, 2017-10-25 01:57:46
Aqui está o mesmo programa que foi submetido pelo utilizador l--"""---------"""""", mas corrigido para trabalhar em uma instância SQL sensível a casos, e com algumas outras melhorias menores.
DROP PROCEDURE IF EXISTS dbo.spFind_Text_In_Database
GO
CREATE PROCEDURE dbo.spFind_Text_In_Database
@strText_To_Find NVARCHAR(4000),
@bitExact_Match BIT = 0
AS
SET NOCOUNT ON
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.TABLE_NAME, C.TABLE_SCHEMA, C.COLUMN_NAME, C.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS C
INNER Join INFORMATION_SCHEMA.TABLES AS T
ON C.TABLE_NAME = T.TABLE_NAME
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE TABLE_TYPE = 'BASE TABLE'
And DATA_TYPE In ('ntext','text','nvarchar','nchar','varchar','char')
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SELECT @SQLTemplate = CASE WHEN @bitExact_Match = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @strText_To_Find + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @strText_To_Find + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i
PRINT @SQL
EXEC sp_executesql @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO
0
Author: Mike, 2018-06-05 23:37:00
Supondo que você não vai fazer uma pesquisa pública em uma base de dados inteira e você só quer encontrar uma palavra-chave por sua própria, esta é a solução mais simples, mais limpa e mais rápida.
-
Atira a tua base de dados para um ficheiro.
$ mysqldump -u root -p your_database > your_database.sql -
Faz um grep nesse ficheiro
$ grep 'keyword' your_database.sql
-4
Author: www-data, 2015-12-22 21:22:44