Kronester

Nachdem ich ja bereits einige Scripte

• Struktureller Tabellenvergleich mit SQL-Server (T-SQL)
• SQL-Server – Tabellenvergleich nach Inhalt
• SQL-Server 2005: Suche nach Text in Spalten und Objekten

für die Suche nach Objekten in einer Datenbank an dieser Stelle veröffentlicht habe, möchte ich heute abschließend eine Möglichkeit der Textsuche über alle Tabellen einer Datenbank vorstellen.

Das Script erwartet einen Suchstring und versucht dann das Wort in einer der Tabellenspalten der Datenbank zu finden. Als Wort wird hier ein Text definiert, der als Begrenzer alle Zeichen außer den folgenden aufweisen darf:

• a-z
• A-Z
• 0-9
• den Unterstrich (_)

Hier also das Script:

 T-SQL | 
 copy code |
?
 
001
002
SET NOCOUNT ON
003
 
004
DECLARE @Suchstring VARCHAR(450)
005
 
006
-- ----------------------------------------------------------------------------
007
-- Suchstring - hier eintragen !!
008
-- ----------------------------------------------------------------------------
009
 
010
SET @Suchstring = 'Engineer'
011
 
012
-- ----------------------------------------------------------------------------
013
-- Arbeitsvariablen deklarieren
014
-- ----------------------------------------------------------------------------
015
 
016
DECLARE @SuchWildcard	VARCHAR(500)
017
DECLARE @FeldTrenner	VARCHAR(100)
018
DECLARE @Feldliste		NVARCHAR(MAX)
019
DECLARE @Delim		NVARCHAR(100)
020
DECLARE @Cmd		NVARCHAR(MAX)
021
DECLARE @SchemaName	sysname
022
DECLARE @TabellenName	sysname
023
DECLARE @SpaltenName	sysname
024
 
025
-- Rückgabe des Ergebnisses in dieser Tabelle
026
 
027
DECLARE @ResultCount TABLE
028
        (
029
            SchemaName	sysname,
030
            TabellenName	sysname,
031
            Treffer	INT,
032
            Abfrage	NVARCHAR(MAX)
033
        )
034
 
035
-- ----------------------------------------------------------------------------
036
-- @SuchWildcard wird als WortTrenner verwendet (siehe Select: weiter unten)
037
-- @FeldTrenner  wird als Trennzeichen zwischen den Textfeldern verwendet um
038
--               eine Verschmelzung der Feldinhalte zu vermeiden.
039
--               Der Feldtrenner sollte eindeutig sein da er im weiteren Verlauf
040
--               durch ein Komma ersetzt wird um die Feldliste für die Kontroll-
041
--               abfrage zu bilden.
042
-- ----------------------------------------------------------------------------
043
 
044
SET @SuchWildcard = '[^a-zA-Z0-9/_]'
045
SET @FeldTrenner  = '<*>'
046
 
047
-- ----------------------------------------------------------------------------
048
-- TableCur läuft durch alle Tabellen der Datenbank
049
-- ----------------------------------------------------------------------------
050
 
051
DECLARE TableCur CURSOR FOR
052
SELECT s.name, o.name
053
FROM sys.objects o
054
    join sys.schemas s ON s.schema_id = o.schema_id
055
WHERE o.type = 'U'
056
 
057
OPEN TableCur
058
 
059
FETCH NEXT FROM TableCur INTO @SchemaName, @TabellenName
060
 
061
WHILE @@FETCH_STATUS = 0
062
BEGIN
063
 
064
    -- ---------------------------------------------------------------------------
065
    -- ColCur durchläuft alle Char-Spalten der Tabelle die lang genug sind
066
    --        um den Suchstring enthalten zu können und verkettet diese mit
067
    --        dem FeldTrenner.
068
    -- ---------------------------------------------------------------------------
069
 
070
    DECLARE ColCur CURSOR FOR
071
    SELECT  c.name AS SpaltenName
072
    FROM sys.objects o
073
        join sys.schemas s ON s.schema_id = o.schema_id
074
        join sys.columns c ON c.OBJECT_ID = o.OBJECT_ID
075
        join sys.types   t ON c.system_type_id = t.system_type_id
076
    WHERE t.name like '%char%'
077
      and c.max_length >= LEN(@Suchstring)
078
      and o.name = @TabellenName
079
      and s.name = @SchemaName
080
 
081
    OPEN ColCur
082
 
083
    SET @Feldliste = ''
084
    SET @Delim     = ''
085
 
086
    FETCH NEXT FROM ColCur INTO @SpaltenName
087
 
088
    WHILE @@FETCH_STATUS = 0
089
    BEGIN
090
        SET @Feldliste = @Feldliste + @Delim + @SpaltenName
091
 
092
        SET @Delim     = '+ ''' + @FeldTrenner + ''' +'
093
 
094
        FETCH NEXT FROM ColCur INTO @SpaltenName
095
    END
096
 
097
    CLOSE ColCur
098
    DEALLOCATE ColCur
099
 
100
    -- ---------------------------------------------------------------------------
101
    -- Wenn eine Feldliste gefunden wurde ... Aufbau des Suchkommandos
102
    -- Die Rückgabe des Kommandos enthält ein SQL-Statement dass in der späteren
103
    -- Analyse alle Textfelder der Tabelle selektiert die in Frage kommen würden.
104
    -- Das Analyse-Statement wird im Feld "Abfrage" des Ergebnisses hinterlegt und
105
    -- kann durch rauskopieren in ein Abfragefenster ausgeführt werden.
106
    -- ---------------------------------------------------------------------------
107
 
108
    IF @Feldliste <> ''
109
    BEGIN
110
        DECLARE @CmdString  NVARCHAR(MAX)
111
        DECLARE @FeldlisteN NVARCHAR(MAX)
112
 
113
        -- --------------------------------------------------------------------
114
        -- Vorbereiten des Analyse-Statements
115
        -- (In der Feldliste den Trenner durch ein Komma ersetzen)
116
        -- Das Kommando muss noch String-Tauglich gemacht werden.
117
        -- --------------------------------------------------------------------
118
 
119
        SET @FeldlisteN = REPLACE(@Feldliste,'+ ''' + @FeldTrenner + ''' +',',')
120
 
121
        SET @CmdString = N'select <@FeldlisteN>
122
                     from <@SchemaName>.<@TabellenName>
123
                     where <@Feldliste> like N''%<@SuchWildcard><@Suchstring><@SuchWildcard>%'' escape ''/''
124
                        or <@Feldliste> like N''<@Suchstring><@SuchWildcard>%''  escape ''/''
125
                        or <@Feldliste> like N''%<@SuchWildcard><@Suchstring>''  escape ''/''
126
                        or <@Feldliste> = N''<@Suchstring>''
127
                    '
128
 
129
        SET @CmdString = REPLACE(@CmdString,'<@FeldlisteN>',@FeldlisteN	)
130
        SET @CmdString = REPLACE(@CmdString,'<@Feldliste>' ,@Feldliste	)
131
        SET @CmdString = REPLACE(@CmdString,'''',''''''			)
132
 
133
        -- --------------------------------------------------------------------
134
        -- Abfrage-Template aufbauen
135
        -- --------------------------------------------------------------------
136
 
137
        SET @Cmd = N'select ''<@SchemaName>'',''<@TabellenName>'',count(*),''<@CmdString>''
138
                     from <@SchemaName>.<@TabellenName>
139
                     where <@Feldliste> like N''%<@SuchWildcard><@Suchstring><@SuchWildcard>%'' escape ''/''
140
                        or <@Feldliste> like N''<@Suchstring><@SuchWildcard>%'' escape ''/''
141
                        or <@Feldliste> like N''%<@SuchWildcard><@Suchstring>'' escape ''/''
142
                        or <@Feldliste> = N''<@Suchstring>''
143
                    '
144
 
145
        -- --------------------------------------------------------------------
146
        -- Variablen einsetzen. Das Analyse-Script gleich am Anfang, da es
147
        -- die anderen Variablen ja ebenfalls enthält.
148
        -- --------------------------------------------------------------------
149
 
150
        SET @Cmd = REPLACE(@Cmd,'<@CmdString>'	,@CmdString	)
151
        SET @Cmd = REPLACE(@Cmd,'<@SchemaName>'	,@SchemaName	)
152
        SET @Cmd = REPLACE(@Cmd,'<@TabellenName>'	,@TabellenName	)
153
        SET @Cmd = REPLACE(@Cmd,'<@Feldliste>'		,@Feldliste	)
154
        SET @Cmd = REPLACE(@Cmd,'<@Suchstring>'	,@Suchstring	)
155
        SET @Cmd = REPLACE(@Cmd,'<@SuchWildcard>'	,@SuchWildcard	)
156
 
157
        -- --------------------------------------------------------------------
158
        -- Ausführen des Statements und der resultierenden Datensatz in die
159
        -- Ergebnistabelle schreiben
160
        -- --------------------------------------------------------------------
161
 
162
        BEGIN try
163
            INSERT INTO @ResultCount
164
            EXEC SP_EXECUTESQL @Cmd
165
        END try
166
        BEGIN catch
167
            PRINT @Cmd
168
        END catch
169
 
170
    END
171
 
172
    FETCH NEXT FROM TableCur INTO @SchemaName, @TabellenName
173
END
174
 
175
CLOSE TableCur
176
DEALLOCATE TableCur
177
 
178
-- ----------------------------------------------------------------------------
179
-- Ausgabe des Ergebnisses
180
-- ----------------------------------------------------------------------------
181
 
182
SELECT * FROM @ResultCount WHERE Treffer > 0
183

Das Ergebnis sieht dann so aus:

Nimmt man die Ausgabe und führt diese aus so enhält man folgendes Ergebnis:

have fun