Probleme mit 2 verschachtelten Cursern

[budi]

Hallo zusammen
Ich möchte gerne von den Benutzertabellen mehrerer Datenbanken innerhalb der selben Server-Instanz, die Indizes (ohne Heap) anzeigen und als T-SQL Command (Index-Rebuild oder -Reorganize) ausgeben (beim Wechsel der Datenbank gebe ich noch einen Titel aus).
Dazu lese ich im aeusseren Cursor von sys.databases die Datenbank-ID, die ich dann im inneren Cursor für sys.dm_db_index_physical_stats weiterverwende.

Der aeussere Cursor funktioniert.
Der innere Cursor funktioniert nur für die Datenbank richtig, die in der Symbolleiste eingestellt ist. Er bringt zwar auch für die anderen Datenbanken einige Zeilen zurück aber nicht alle. Irgendwie verwendet der innere Cursor nicht die Datenbank-ID die ich aus dem aeusseren Cursor mitgebe.
Ich muss noch sagen, dass die Tabellen- und Indexnamen in allen Benutzerdatenbanken identisch sind. Pro Departement gibt es eine eigene Datenbank mit gleicher Struktur aber unterschiedlichem Inhalt.

Hat mir jemand eine Idee was ich falsch mache.
Danke für Hilfe und Unterstuetzung.
Gruss
Dieter

-------------

BEGIN
 SET NOCOUNT ON;
 declare @stmt varchar(2000)
 declare @report varchar(2000)
 declare @ind_name varchar(100)
 declare @tab_name varchar(100)
 declare @schema_name varchar(100)
 DECLARE @database_name_db varchar(100) 
 declare @database_name_ind varchar(100)
 DECLARE @database_id_db varchar(100)
 declare @avg_fragmentation_in_percent smallint
 declare @fragment_count varchar(100)
 declare @record_count varchar(100) 
 declare @report_db varchar(2000)

 -- Datenbank Cursor (äusserer Cursor)
 -------------------
 DECLARE db_cur cursor for 
  SELECT name as DatenbankName, database_id as DatenbankID
  FROM sys.databases db
  ORDER BY db.name asc;
 --
 open db_cur
  BEGIN
   fetch next from db_cur into @database_name_db, @database_id_db 
   while @@fetch_status <> -1
    BEGIN 
     if @database_name_db <> 'tempdb' and @database_name_db <> 'msdb'and @database_name_db <> 'model'
      BEGIN
       set @report_db = '-- ' + @database_name_db + ' --'
       print (@report_db) 

       -- Index Cursor (innerer Cursor)
       -- Lesen von sys.dm_db_index_physical_stats mit der DatenbankID aus dem Datenbank Cursor
       ---------------
       DECLARE ind_cur cursor for 
        SELECT DB_name(@database_id_db) as 'db_name', object_name(ios.object_id) as 'object_name', 
         i.name as 'index_name', avg_fragmentation_in_percent, s.name as 'schema_name', 
         fragment_count, record_count
        FROM sys.dm_db_index_physical_stats (@database_id_db, null, null, null, 'DETAILED') ios
         join  sys.indexes i on i.[object_id] = ios.[object_id] and i.index_id = ios.index_id
         join  sys.objects o on o.[object_id] = i.[object_id]
         --join  sys.objects o on o.[object_id] = ios.[object_id]
         join  sys.schemas s on s.[schema_id] = o.[schema_id]
        WHERE o.type = 'U' and  i.index_id >= 1 and avg_fragmentation_in_percent >= 5
        ORDER BY object_name(ios.object_id) asc, i.name asc;
       --
       open ind_cur
        BEGIN
         fetch next from ind_cur into @database_name_ind, @tab_name, @ind_name,  
          @avg_fragmentation_in_percent, @schema_name, @fragment_count, @record_count 
         set @report = 'database_name;object_name;index_name;avg_fragment%;' 
             + 'fragment_count;record_count;reorganize/rebuild'
         print (@report)   
         while @@fetch_status <> -1
          BEGIN 
           if @avg_fragmentation_in_percent < 30
             Begin
            set @report = @database_name_ind + ';' + @tab_name + ';' + @ind_name + ';' 
             + cast(@avg_fragmentation_in_percent as varchar(100)) + ';' 
             + @fragment_count + ';' +  @record_count + ';reorganize'
             End 
           else
             Begin
            set @report = @database_name_ind + ';' + @tab_name + ';' + @ind_name + ';' 
             + cast(@avg_fragmentation_in_percent as varchar(100)) + ';' 
             + @fragment_count + ';' +  @record_count + ';rebuild'
             End 
           print (@report) 
           --nächsten Datensatz vom Index-Cursor lesen  
           fetch next from ind_cur into @database_name_ind, @tab_name, @ind_name,  
            @avg_fragmentation_in_percent, @schema_name, @fragment_count, @record_count 
          END
        END
       close ind_cur
       deallocate ind_cur
       -------------------------------------------
      END 
     --nächsten Datensatz vom Datenbank-Cursor lesen  
     fetch next from db_cur into @database_name_db, @database_id_db
    END
  END
 close db_cur
 deallocate db_cur
END

[O. Helper]

Hallo Dieter,

das Script ist ein wenig groß, um es ganz zu analysieren, aber eines kann ich schon mal sagen (oder eher vermuten).

Du versuchst DB-Übergreifenden zu arbeiten, tust es aber doch nicht.

In Deinem äußeren Cursor holst Du Dir alle DB-IDs.
Mit "sys.dm_db_index_physical_stats (@database_id_db..." bekommst Du auch noch die gewünschte Info zur jeweiligen Datenbank.

Alles andere bezieht sich aber auf die DB, auf der Du das Script ausführst.
Z.B. object_name(ios.object_id), hier kannst Du keine DB-ID angeben, woher SQL wissen, aus welcher DB die Bezeichnung kommen soll? Die object_id sind Server-weit nicht eindeutig, nur je DB.

Gleiches gilt für Selektionen wie
join sys.indexes i on ..... join sys.objects o on
Da auch hier keine DB angegeben ist, bezieht es sich auf die aktuelle DB.
Dadurch bekommst Du durch Zufall vielleicht ein Teilergebnis, aber kein richtiges Ergebnis.

Olaf.

[ebis]

wozu das Cursor-geraffel, folgendes SQL-Statement tuts doch auch:

SELECT	DB_name(d.database_id)		AS 'db_name'
	,	object_name(ios.object_id)		AS 'object_name'
	,	i.name				AS 'index_name'
	,	avg_fragmentation_in_percent
	,	s.name				AS 'schema_name'
	,	fragment_count
	,	record_count
FROM		Sys.databases			AS D
CROSS APPLY	sys.dm_db_index_physical_stats 
		(NULL, null, null, null, 'DETAILED')	AS  ios
JOIN		sys.indexes 	AS i 	ON i.[object_id] = ios.[object_id] 
					AND i.index_id = ios.index_id
JOIN		sys.objects	AS o 	ON o.[object_id] = i.[object_id]
JOIN		sys.schemas	AS s 	ON s.[schema_id] = o.[schema_id]
WHERE		d.database_id > 4 d.name -- entspricht NOT IN ('tempdb','msdb','model', 'master')
	AND	o.type = 'U' 
	AND	i.index_id >= 1 
	AND	 avg_fragmentation_in_percent >= 5
ORDER BY		[db_name], [object_name], index_name

Je nach Anzahl der Datenbanken und Anzahl ihrer Tabellen dauerts zwar ein bisschen....

EBI

[budi]

Script funktioniert nicht

Hallo zusammen
Sorry für meine verspätete Antwort aber die Grippe hat mich letzte Woche erwischt.

Hallo Olaf
Danke für Deine Antwort. und den Hinweis, dass die object_id nur je DB eindeutig sind und nicht Server-weit . Das wusste ich nicht.
Hast Du eine Idee, wie man das anders lösen könnte.

Hallo EBI
Vielen Dank auch für Dein Script. Es funktioniert allerdings bei mir nicht richtig. Die einzelnen Datenbanken (ich habe es mit 2 Benutzerdatenbanken getestet) kommen zwar schön der Reihe nach aber die Tabellen sind meist doppelt aufgeführt.
Zuerst kommt die DB1 mit allen Tabellen und zusätzlich noch mit allen Tabellen aus der DB2. Dann kommt die DB2 mit allen Tabellen und zusätzlich noch allen Tabellen aus der DB1. Siehe pdf-Anhang (Testresultat.pdf) wo ich einige Indizes dokumentiert habe.

Zudem ist das Ergebnis davon abhängig, welche Datenbank in der Symbolleiste eingestellt ist.

Meine Fragen:
1. Wie bringe ich die doppelten Einträge weg
2. Das Script soll unabhängig von der in der Symbolleiste eingestellten DB funktionieren.

Besten Dank für Eure Hilfe
Gruss
Dieter

PS: Ich hoffe mein Anhang und diese Antwort kommen am richtigen Ort an. Bin neu in diesem Forum.

[budi]

Ich konnte das Problem beheben

Hallo zusammen
Ich habe meine Variante mit den beiden Cursorn weiterverfolgt und den inneren Cursor in ein EXEC-Statement eingepackt.

exec('use ' + @database_name_db + ' mein Code ')

Jetzt funktioniert das Script und liefert mir aus allen Benutzerdatenbanken innerhalb einer Serverinstanz die Indizes und deren Fragmentierungsstand.

Freundliche Grüsse
Dieter