Textsuche in Datenbank

chuchi

Benutzer
Beiträge
6
Hi

Ich hoffe ihr könnt mir helfen
Ich bin auf der Suche nach einer Abfrage die mir einen spezifischen Text in der gesamten Datenbank sucht.
Sprich: Ich möchte in jeder Spalte jeder Tabelle nach dem Suchbegriff suchen

Ich konnte leider im Netz nichts finden (da ich vermuttlich nach dem falschen Begriffen gesucht habe...) und daher selber eine Abfrage erstellt

CREATE PROCEDURE [dbo].[q_FeldinhaltSuchen]
@i_SucheNach varchar(255)
AS
DECLARE @ObjID int
DECLARE @Table varchar(50)
DECLARE @Column varchar(50)
DECLARE @PrimaryIDColumn varchar(50)
DECLARE @Search varchar(255)
DECLARE @cmd varchar(1024)

-- Temporäre Tabelle für die Rückgabe
CREATE Table #temp
(
varchar(50),
[Column] varchar(50) ) -- Suchwert definieren (mit % für Wildcard-Suche) SELECT @Search = '%' + @i_SucheNach + '%' DECLARE FieldCursor CURSOR FOR (SELECT sys.tables.object_id, sys.tables.name, sys.columns.name FROM sys.columns join sys.tables ON sys.tables.object_id = sys.columns.object_id WHERE system_type_id IN (SELECT system_type_id FROM sys.types WHERE name in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar'))) OPEN FieldCursor FETCH NEXT FROM FieldCursor INTO @ObjID, @Table, @Column WHILE @@FETCH_STATUS = 0 BEGIN SELECT @PrimaryIDColumn = '' SELECT @PrimaryIDColumn = isnull(name, '') FROM sys.columns WHERE object_id = @ObjID AND is_identity = 1 -- Überprüfung des Inhalts, sofern Inhalt existiert, wird er in die temporäre Tabelle geschrieben -- Sofern PrimaryID existiert, mit PrimaryID auf Existenz prüfen -> Abfragen sind schneller IF @PrimaryIDColumn <> '' SELECT @cmd = 'IF EXISTS(SELECT [' + @PrimaryIDColumn ELSE SELECT @cmd = 'IF EXISTS(SELECT [' + @Column SELECT @cmd = @cmd + '] FROM [' + @Table + '] WHERE convert(varchar(50), [' + @Column + ']) LIKE ''' + @Search + ''') ' SELECT @cmd = @cmd + 'INSERT INTO #temp SELECT ''' + @Table + ''', ''' + @Column + '''' -- Ausführen des Commands: () da der Command in einem String ist print @cmd EXEC (@cmd) SELECT @ObjID = 0 SELECT @Table = '', @Column = '' -- nächstes Objekt FETCH NEXT FROM FieldCursor INTO @ObjID, @Table, @Column END CLOSE FieldCursor DEALLOCATE FieldCursor -- Ausgabe der Tabelle SELECT * FROM #temp DELETE #temp DROP TABLE #temp Finish: RETURN 0 GO

Ich konnte leider im Netz nichts finden (da ich vermuttlich nach dem falschen Begriffen gesucht habe...) und daher selber eine Abfrage erstellt

Ziemlich simpel
Eine Schlaufe durch alle Spalten
Pro Spalte wird ein String-Command erstellt welcher via IF Exist den Suchwert überprüft und sofern vorhanden die Spalte in eine temp. Tabelle (für die Ausgabe) schreibt.

Das funktioniert auch schön und gut
Problem ist: es ist langsam

Kennt ihr eine schnellere Lösung?
Das ermitteln des Primary Keys dient zB schon der Performance, und auch die Restriktion der Spalten (muss nicht nach dem Suchbegriff suchen, wenn die max-Länge der Spalte bereits kleiner ist).

Danke
 
Werbung:

akretschmer

Datenbank-Guru
Beiträge
9.171
Ziemlich simpel
Eine Schlaufe durch alle Spalten
Pro Spalte wird ein String-Command erstellt welcher via IF Exist den Suchwert überprüft und sofern vorhanden die Spalte in eine temp. Tabelle (für die Ausgabe) schreibt.

Das funktioniert auch schön und gut
Problem ist: es ist langsam

Kennt ihr eine schnellere Lösung?

Mal von der Sinnhaftigkeit und dem falschen Umgang mit Datenbanken abgesehen: hole Dir alle Tabellen. Hole Dir von diesen Tabellen alle in Frage kommenden Spalten. Verkleistere all diese Spalten (also je Tabelle) zu einem String. Durchsuche diesen String. Nehme die nächste Tabelle und mach weiter, bis alle durch sind.

Du durchläufst scheinbar jede Tabelle N-fach mit N gleich der Anzahl der in Frage kommender Spalten. Das sind dann N sequentielle Scans. Einer täte reichen.

Viel Spaß!
 

ukulele

Datenbank-Guru
Beiträge
4.416
Eventuell wäre es auch sinnvoll eine Art Index Tabelle aufzubauen in der du zu jedem Wort das in jeder Tabelle / Spalte vorkommt per Trigger einen Eintrag setzt. Dann musst du nur in dieser Tabelle suchen.
 

Tommi

Datenbank-Guru
Beiträge
284
Hi,

das Erste, das mir bei der Beschreibung des Ziels in den Sinn kam, war die Volltext-Suche im SQL Server.
Das ist ein eigenständiger Dienst, der zwar eigens Eingerichtet werden muss, aber dafür sehr performant ist und für alle Such-Abfragen auf String-Felder angewendet werden kann.

Hier ist mal ein Link dazu:
http://msdn.microsoft.com/de-de/library/ms142571(v=sql.105).aspx

Ich selbst habe das zwar noch nicht eingerichtet, aber so schwer kann das nicht sein (und nimmt bestimmt nur unwesentlich mehr Zeit in Anspruch als die Programmierung und Optimierung einer Eigen-Entwicklung)

Viele Grüße,
Tommi
 

Tommi

Datenbank-Guru
Beiträge
284
P.S.: wirklich gut funktioniert diese Volltextsuche allerdings erst ab SQL Server 2008 (besser noch in SQL Server 2008 R2)
 

chuchi

Benutzer
Beiträge
6
Vielen Dank für die Antworten

Ich habe einigen Input von anderen Quellen noch erhalten und diese Abfrage durchsucht meine DB nun in knapp 40 sek, statt in 2 min

Code:
CREATE PROCEDURE [dbo].[x_FeldinhaltSuchen]
    @i_SucheNach varchar(255),
    @i_Spalte varchar(255) = ''
AS
---------------------------------------------------------------------------------------------------------
BEGIN
  DECLARE @ResultColumn varchar(255) = ''
 
    IF NOT EXISTS (SELECT object_id FROM sys.tables WHERE Name = 'FeldinhaltSuchen')
  BEGIN
      -- DROP TABLE FeldinhaltSuchen
      CREATE TABLE FeldinhaltSuchen (Suchwort varchar(255), ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  END
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr nvarchar(110), @SearchColumn nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr = QUOTENAME('%' + @i_SucheNach + '%','''')
  SET @searchColumn = '%' + @i_Spalte + '%'
 
    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
            )       
         
        SET @ResultColumn = ''
        SET @ResultColumn = @TableName + '.' + @ColumnName
 
            IF @ColumnName IS NOT NULL AND @ColumnName LIKE @SearchColumn
            BEGIN
                INSERT INTO FeldinhaltSuchen
                EXEC
                (
              'IF NOT EXISTS(SELECT ColumnValue FROM FeldinhaltSuchen WHERE ColumnName = ''' + @ResultColumn + ''') ' +
                    'SELECT ''' + @i_SucheNach + ''', ''' + @ResultColumn + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
                )
            END
        END   
    END
 
    SELECT Suchwort, ColumnName, ColumnValue FROM FeldinhaltSuchen
END

@akretschmar
Das ist leider nicht meine Datenbank
Sie wurde über Jahre immer wieder ergänzt und von für jede Zusätzliche Spalte wurde einfach eine weitere Tabelle erstellt, mit einem eindeutigen Spaltenname als Primary Key und einer eindeuten SpaltenName als FK auf die bereits bestehende Tabelle.
Dies führte dazu, dass man dann zB einen Bemerkungs-Text für eine Person in eine Tabelle findet die AD_TTXT_Text heisst mit dem PK AD_TTXT_Text_ID und einer Spalte AD_TTXT_Text_memo die gesuchte Bemerkung

Mit der Prozedur konnte ich nun die Inhalte finden, welche ich gesucht habe.
 
Werbung:

chuchi

Benutzer
Beiträge
6
Danke

Konnte mit einigen Infos die Prozedur anpassen und von knapp 2min auf 40 sek runterkommen:

Code:
CREATE PROCEDURE [dbo].[x_FeldinhaltSuchen]
    @i_SucheNach varchar(255),
    @i_Spalte varchar(255) = ''
AS
---------------------------------------------------------------------------------------------------------
BEGIN
  DECLARE @ResultColumn varchar(255) = ''
 
    IF NOT EXISTS (SELECT object_id FROM sys.tables WHERE Name = 'FeldinhaltSuchen')
  BEGIN
      -- DROP TABLE FeldinhaltSuchen
      CREATE TABLE FeldinhaltSuchen (Suchwort varchar(255), ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  END
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr nvarchar(110), @SearchColumn nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr = QUOTENAME('%' + @i_SucheNach + '%','''')
  SET @searchColumn = '%' + @i_Spalte + '%'
 
    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
            )       
         
        SET @ResultColumn = ''
        SET @ResultColumn = @TableName + '.' + @ColumnName
 
            IF @ColumnName IS NOT NULL AND @ColumnName LIKE @SearchColumn
            BEGIN
                INSERT INTO FeldinhaltSuchen
                EXEC
                (
              'IF NOT EXISTS(SELECT ColumnValue FROM FeldinhaltSuchen WHERE ColumnName = ''' + @ResultColumn + ''') ' +
                    'SELECT ''' + @i_SucheNach + ''', ''' + @ResultColumn + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
                )
            END
        END   
    END
 
    SELECT Suchwort, ColumnName, ColumnValue FROM FeldinhaltSuchen
END
 
Oben