Come cercare il database del server SQL per la stringa?

So che è ansible, ma non so come.

Ho bisogno di cercare il database Microsoft SQL per tutte le menzioni di stringa specifica. Ad esempio: vorrei cercare tutte le tabelle, viste, funzioni, stored procedure, … per la stringa “tblEmployes”. (Non i dati all’interno delle tabelle)

Uno dei motivi per cui ho bisogno di questo, vorrei rimuovere alcune tabelle di dati aggiuntive che vengono create, ma temo che possano essere usate da qualche parte nelle procedure o nelle funzioni.

Qualsiasi aiuto è apprezzato.

    questo cercherà ogni colonna di ogni tabella in un database specifico. creare il proc memorizzato sul database in cui si desidera effettuare la ricerca.

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions–1#2 :

    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 

    per eseguirlo basta fare questo:

     exec FindMyData_string 'google', 0 

    funziona incredibilmente bene !!!

    Se hai bisogno di trovare oggetti di database (ad es. Tabelle, colonne, trigger) per nome, dai uno sguardo allo strumento GRATUITO di Red Gate chiamato Ricerca SQL che fa ciò: cerca l’intero database per qualsiasi tipo di stringa.

    inserisci la descrizione dell'immagine qui

    inserisci la descrizione dell'immagine qui

    È un ottimo strumento indispensabile per qualsiasi sviluppatore di database o DBA – ho già detto che è assolutamente GRATUITO da usare per qualsiasi tipo di utilizzo?

    Puoi anche provare ApexSQL Search : è un add-on gratuito SSMS simile a Ricerca SQL.

    Se vuoi davvero usare solo sql potresti provare questo script

     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] 

    Per ottenere una tabella per nome in SQL Server:

     SELECT * FROM sys.Tables WHERE name LIKE '%Employees%' 

    per trovare una stored procedure per nome:

     SELECT name FROM sys.objects WHERE name = 'spName' 

    per ottenere tutte le stored procedure relative a una tabella:

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

    È ansible esportare il database (se di piccole dimensioni) sul disco rigido / desktop, quindi eseguire semplicemente una ricerca di stringhe tramite l’editor di testo.

    Questa procedura di ricerca del codice e funzione ma non ricerca nella tabella 🙂

     SELECT name FROM sys.all_objects WHERE Object_definition(object_id) LIKE '%text%' ORDER BY name 

    potresti;

    1. Script il database in un singolo file e cerca il file per tblEmployees usando un editor di testo. In SQL Server Management Tools (SSMS) fare clic con il pulsante destro del mouse sul database e scegliere Genera script.
    2. Utilizzare ‘Dependance’ di SSMS facendo clic con il pulsante destro su tblEmployees per vedere quali altri oggetti dipendono da esso
    3. Utilizzare uno strumento di terze parti gratuito come RedGate SQLSearch per cercare tutti gli oggetti del database per nome e contenuto per parola chiave.

    Questo cercherà una stringa su ogni database:

     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 ''%'[email protected]_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] 

    Vecchia domanda, lo so, ma ecco la mia versione … L’ho chiamata “Ago nel pagliaio” per ovvi motivi.

    Cerca il valore specifico in ogni riga e ogni colonna, non per i nomi delle colonne, ecc.

    Esegui la ricerca (sostituisci i valori per le prime due variabili, ovviamente):

     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 [email protected]_DB AND DATA_TYPE NOT IN ('timestamp', 'datetime'); DECLARE @TOTAL int = (SELECT COUNT(*) FROM information_schema.columns WHERE [email protected]_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+'.'[email protected]_SCHEMA+'.'[email protected]_NAME+': '[email protected]_NAME+' ('[email protected]_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 '''[email protected]_CATALOG+''' catalog_name, '''[email protected]_SCHEMA+''' schema_name, '''[email protected]_NAME+''' table_name, '''[email protected]_NAME+''' column_name, '''[email protected]_TYPE+''' data_type, ' + +' COUNT(['[email protected]_NAME+']) records '+ +' FROM '[email protected]_CATALOG+'.'[email protected]_SCHEMA+'.'[email protected]_NAME + +' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['[email protected]_NAME + '] as NVARCHAR(max)) ' ELSE ' ['[email protected]_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 @[email protected]+1 -- IF @i > 1000 -- BREAK END CLOSE col_cur; DEALLOCATE col_cur; SELECT * FROM ##RESULTS WHERE RECORDS>0; 

    Quindi per visualizzare i risultati, anche durante l’esecuzione, da un’altra finestra, eseguire:

     DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N'%@[email protected]%' 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 '''[email protected]_CATALOG+''' catalog_name, '''[email protected]_SCHEMA+''' schema_name, '''[email protected]_NAME+''' table_name, '''[email protected]_NAME+''' column_name, '''[email protected]_TYPE+''' data_type, ' + +' ['[email protected]_NAME+']'+ +', * ' +' FROM '[email protected]_CATALOG+'.'[email protected]_SCHEMA+'.'[email protected]_NAME + +' WHERE ' + CASE WHEN @SHOULD_CAST=1 THEN 'CAST(['[email protected]_NAME + '] as NVARCHAR(max)) ' ELSE ' ['[email protected]_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 @[email protected]+1 -- IF @i > 10 -- BREAK END CLOSE col_cur; DEALLOCATE col_cur; 

    Poche menzioni a riguardo:

    • utilizza i cursori anziché un ciclo while di blocco
    • può stampare progresso (decommentare se necessario)
    • può uscire dopo alcuni tentativi (decommentare l’IF alla fine)
    • mostra tutti i record
    • puoi perfezionarlo se necessario

    RINUNCE:

    • NON farlo funzionare in ambienti di produzione!
    • È lento. Se al DB si accede da altri servizi / utenti, aggiungere “WITH (NOLOCK)” dopo ogni nome di tabella in tutti i select, specialmente quelli di selezione dynamic.
    • Non convalida / protegge da tutti i tipi di opzioni di iniezione SQL.
    • Se il tuo DB è enorme, preparati a dormire un po ‘, assicurati che la query non venga uccisa dopo pochi minuti.
    • Inserisce alcuni valori nella stringa, inclusi ints / bigints / smallints / tinyints. Se non ne hai bisogno, mettili nelle stesse liste di esclusione con i timestamp nella parte superiore dello script.

    Spero che questo ti aiuti.

    È stato concesso l’accesso a un database, ma non la tabella in cui è stata archiviata la mia query.

    Ispirato dalla risposta di @marc_s, ho dato un’occhiata a HeidiSQL che è un programma per Windows che può gestire MySQL, MSSQL e PostgreSQL.

    Trovato che può anche cercare un database per una stringa.

    Fai clic su Cerca, quindi cerca testo sul server

    Strumento di ricerca aperto. Assicurarsi che il DB sia selezionato

    Cercherà ogni tabella e ti darà quante volte ha trovato la stringa per tabella!

    Ecco lo stesso script presentato dall’utente l -”””——— ” ” ” ” ” ”, ma corretto per funzionare su SQL con distinzione tra maiuscole e minuscole istanza e con alcuni altri miglioramenti minori.

     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 

    Supponendo che non si effettui una ricerca pubblica in un intero database e si voglia semplicemente trovare una parola chiave da soli, questa è la soluzione più semplice, più pulita e più veloce.

    1. Scarica il tuo database in un file.

       $ mysqldump -u root -p your_database > your_database.sql 
    2. Fai un grep su quel file

       $ grep 'keyword' your_database.sql 

    E hai finito.