Abfragen auf alle Datenbank auslösen

Question

Hallo

Es kommt häufig vor, dass wir über 20 und mehr Datenbanken die gleiche Abfrage machen müssen.

Es soll z.B. geprüft werden, wie viele Datensätze es in einer bestimmten Tabelle hat. Falls die Tabelle in einer DB nicht existiert, soll die Abfrage übersprungen werden.

Bei folgendem Script funktioniert die use-Zeile anscheinend nicht. Das Exists wirkt sich immer auf die akt. Tabelle aus und nicht aus dem Cursor:

declare  cr_tables cursor
for
    SELECT name FROM master.dbo.sysdatabases 
    WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') 
    and name like 'wfuser%'
    ORDER BY 1

declare @line varchar(500)
declare @database varchar(50)

open cr_tables

FETCH NEXT FROM cr_tables into @database

WHILE @@FETCH_STATUS = 0

BEGIN
    exec ('use ' + @database)
   
    if exists (    SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name like 'idcSalaryPaydayJnl')
    begin
          select @line = 'select ''' + @database +''', count(*) FROM ' + @database + '.dbo.idcsalarystaff'
   exec (@line)
end
FETCH NEXT FROM cr_tables into @database

END

CLOSE cr_tables

DEALLOCATE cr_tables

 

Jemand einen Tipp dazu oder wie würdet ihr das machen?

 

Gruss Christoph

Answer 1

Hallo Christoph,

ein Exec öffnet einen eigenen Batch, das Use gilt nur dort.

Wenn, dann mußt Du alles in einem Exec ausführen also:

set @sql = "Use ' + @database +

alles, was Du dann zusammenbaust

Und führst dann den kompletten SQL-String aus.

Gruß
Christa

Answer 2

Das mit USE wird nicht so richtig klappen, vor allem nicht, wenn Du es mal als SP anlegen willst.

Stelle auch das erste Statement zum Prüfen, ob die Tabelle vorhanden ist, auf dynamischen SQL um, dann geht es (+ Kleinigkeiten wie Datentypen => UniCode):

declare  cr_tables cursor

for

    SELECT name FROM master.dbo.sysdatabases 

    WHERE name like 'wfuser%'

    ORDER BY 1

 

declare @line nvarchar(500)

declare @database varchar(50)

 

DECLARE @result int;

DECLARE @paras nvarchar(100);

SET @paras = '@Result int OUTPUT'

 

open cr_tables

FETCH NEXT FROM cr_tables into @database

WHILE @@FETCH_STATUS = 0

BEGIN

   

    SELECT @line =          

          N'

            SELECT @Result = (SELECT 1

                FROM ' + @database + '.INFORMATION_SCHEMA.TABLES

                WHERE table_name like ''idcsalarystaff'')'

   

     EXECUTE sp_executesql @line, @paras, @Result = @Result OUTPUT;

    

     IF @Result = 1

     BEGIN

         select @line =          

               'select ''' + @database +''', count(*) FROM ' + @database + '.dbo.idcsalarystaff'

         EXECUTE sp_executesql @line

    END   

 

     FETCH NEXT FROM cr_tables into @database

END

 

CLOSE cr_tables

DEALLOCATE cr_tables

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 3

Hallo Olaf

Besten Dank, funktioniert super und jetzt habe ich auch Beispiel und weitere Abfragen zu machen.

 

Danke

Gruss Christoph

Answer 4

Auch wenn Du bereits Deine Antwort erhalten hast...

Je nachdem wie gross Deine Tabellen sind, kann ein SELECT COUNT(*) durchaus schon mal etwas dauern und eine Menge Serverresourcen binden. Falls also nicht zwingend absolute Korrektheit des COUNTs gegeben sein muss, kann man ein hinreichend genaues Ergebnis aus den Systemtabellen erhalten. So ein Ergebnis sollte auch "gut genug" sein für interne Statistiken, etc...

Just my $0.02 anyway.

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org