1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Textsuche in Datenbank

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von chuchi, 25 April 2013.

  1. chuchi

    chuchi Benutzer

    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
    (
    [Table] 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
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Dumpe die Datenbank und verwende dann Tools wie grep.

    Hint: Datenbanken nutzt man nicht wie eine lose Zettelsammlung.

    Andreas
     
  3. akretschmer

    akretschmer Datenbank-Guru

    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ß!
     
  4. ukulele

    ukulele Datenbank-Guru

    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.
     
  5. Tommi

    Tommi Datenbank-Guru

    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
     
    Walter und ukulele gefällt das.
  6. Tommi

    Tommi Datenbank-Guru

    P.S.: wirklich gut funktioniert diese Volltextsuche allerdings erst ab SQL Server 2008 (besser noch in SQL Server 2008 R2)
     
  7. chuchi

    chuchi Benutzer

    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.
     
    ukulele gefällt das.
  8. chuchi

    chuchi Benutzer

    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
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden