wake-up-neo.com

Wie finde ich eine Zeichenfolge in einer gesamten Datenbank?

Ich habe eine bestimmte Zeichenfolge, z. B. "123abcd", aber ich kenne weder den Namen der Tabelle noch den Namen der Spalte in der Tabelle meiner SQL Server-Datenbank. Ich möchte es mit einem select finden und alle Spalten der verwandten Zeichenfolge anzeigen, also habe ich mich gefragt:

select * from Database.dbo.* where * like  '%123abcd%'

Aus offensichtlichen Gründen funktioniert es nicht, aber es gibt eine einfache Möglichkeit, eine select-Anweisung zu erstellen, um so etwas zu tun?

sqldatabasesql-server-2008select
44
Diogo

Das wird funktionieren:

DECLARE @MyValue NVarChar(4000) = 'something';

SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
     sys.tables T ON S.schema_id = T.schema_id;

WHILE (EXISTS (SELECT * FROM #T)) BEGIN
  DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
  DECLARE @TableName NVarChar(1000) = (
    SELECT TOP 1 SchemaName + '.' + TableName FROM #T
  );
  SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);

  DECLARE @Cols NVarChar(4000) = '';

  SELECT
    @Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
  FROM sys.columns C
  WHERE C.object_id = OBJECT_ID(@TableName);

  SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
  SELECT @SQL = @SQL + @Cols;

  EXECUTE(@SQL);

  DELETE FROM #T
  WHERE SchemaName + '.' + TableName = @TableName;
END;

DROP TABLE #T;

Ein paar Vorbehalte. Erstens, das ist unverschämt langsam und nicht optimiert . Alle Werte werden einfach in nvarchar konvertiert, damit sie fehlerfrei verglichen werden können. Es kann zu Problemen mit Werten wie datetime kommen, die nicht wie erwartet konvertieren und daher nicht abgeglichen werden, wenn sie (falsche Negative) sein sollten.

Die WHERE (0 = 1) soll das Erstellen der OR-Klausel erleichtern. Wenn es keine Übereinstimmungen gibt, werden keine Zeilen zurückgegeben.

28
Yuck

Hier sind einige weitere kostenlose Tools, die dafür verwendet werden können. Beide funktionieren als SSMS-Addins.

ApexSQL Search - 100% free - durchsucht sowohl das Schema als auch die Daten in Tabellen. Hat ein paar weitere nützliche Optionen wie Abhängigkeitsverfolgung…

SSMS Tools Pack - kostenlos für alle Versionen außer SQL 2012 - sieht nicht so aus wie das vorige, verfügt jedoch über viele andere coole Funktionen 

11
John Moore
create procedure usp_find_string(@string as varchar(1000))
as
begin
declare @mincounter as int
declare @maxcounter as int
declare @stmtquery as varchar(1000)
set @stmtquery=''
create table #tmp(tablename varchar(128),columnname varchar(128),rowid int identity)
create table #tablelist(tablename varchar(128),columnname varchar(128))
declare @tmp table(name varchar(128))
declare @tablename as varchar(128)
declare @columnname as varchar(128)

insert into #tmp(tablename,columnname)
select a.name,b.name as columnname from sysobjects a
inner join syscolumns b on a.name=object_name(b.id)
where a.type='u'
and b.xtype in(select xtype from systypes
    where name='text' or name='ntext' or name='varchar' or name='nvarchar' or name='char' or name='nchar')
order by a.name

select @maxcounter=max(rowid),@mincounter=min(rowid) from #tmp 
while(@mincounter <= @maxcounter )
begin
 select @tablename=tablename, @columnname=columnname from #tmp where [email protected]
 set @stmtquery ='select top 1  ' + '[' [email protected]+']' + ' from ' + '['[email protected]+']' + ' where ' + '['[email protected]+']' + ' like ' + '''%' + @string + '%'''
 insert into @tmp(name) exec(@stmtquery)
 if @@rowcount >0
 insert into #tablelist values(@tablename,@columnname)
 set @[email protected] +1
end
select * from #tablelist
end
4
Neeraj Kumar Yadav

Ich denke, du hast Optionen:

  1. Erstellen Sie ein dynamisches SQL mit sys.tables und sys.columns, um die Suche durchzuführen ( Beispiel hier ).

  2. Verwenden Sie ein beliebiges Programm, das diese Funktion hat. Ein Beispiel hierfür ist SQL Workbench (kostenlos).

4
DavidEG

In Oracle können Sie den folgenden SQL-Befehl verwenden, um die benötigten SQL-Befehle zu generieren:

select 
     "select * "
     " from "||table_name||
     " where "||column_name||" like '%123abcd%' ;" as sql_command
from user_tab_columns
where data_type='VARCHAR2';
3
Raihan

SQL Locator (kostenlos) hat für mich großartig funktioniert. Es gibt viele Optionen und ist recht einfach zu bedienen.

2
Gabe

Common Resource Grep (crgrep) sucht in Tabellen/Spalten nach String-Übereinstimmungen nach Namen oder Inhalt und unterstützt eine Reihe von DBs, darunter SQLServer, Oracle und andere. Volle Wildcard und andere nützliche Optionen.

Es ist Open Source (ich bin der Autor). 

http://sourceforge.net/projects/crgrep/

2
Craig

Normalerweise verwende ich information_Schema.columns und information_schema.tables, obwohl sys.tables und sys.columns, wie bereits gesagt, kürzer zu tippen ist.

Verketten Sie diese in einer Schleife

@sql = @sql + 'select' + column_name + 
' from ' + table_name + 
' where ' + column_name ' like ''%''+value+''%' UNION

Führen Sie dann die resultierende SQL aus. 

2
MatthewMartin

Entschuldigung für die späte Antwort, aber ich hatte auch diese Frage und löste sie mit einem anderen Ansatz, der wahrscheinlich für alle Datenbanken allgemeiner ist. 

  1. erstellen Sie einen Datenbankspeicherauszug. 
  2. Von dort sollten Sie in der Lage sein, die Datei in einem Texteditor zu öffnen und nach der erforderlichen Zeichenfolge zu suchen.
2
meowcat

Hier ist eine einfache und komfortable Cursor-basierte Lösung

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur
0
Noor A Shuvo