[T-SQL] Alter jeder Tabelle herausfinden

[12nico07]

Hallo,
ich habe folgendes Problem:
Ich möchte von jeder Tabelle auf dem Server (nicht nur von einer DB) die verschiedenen Daten haben (zuletzt geändert, Erstellt am...)
Zuerst muss ich jede Tabelle herausfinden dazu habe ich mir folgendes gedacht:

Zuerst finde ich alle Datenbanken auf dem Server raus,​

anschließend durchsuche ich jede DB nach ihren Tabellen​

Hier ist das erste Problem, wie kann ich das Variabel mache?
Und mit welchem Befehl bekomme ich das Datum?

Hier mal der Code (mit ein bisschen Erklärung zu meinem Problem)

--Erstellt eine neue temporäre Tabelle
--und füllt diese mit den Datenbanken
    create table #temp
(
name sysname
,db_size nvarchar(13)
,owner sysname
,dbid smallint
,created nvarchar(11)
,status nvarchar(600)
,compatibility_level tinyint
)
go
insert into #temp
exec sp_helpdb
 
 
--Cursor sucht nur die Daten aus der Spalte "name" aus Tabelle #temp
--Also die Namen der Datenbanken
DECLARE Datenbank_Cursor CURSOR FOR
SELECT name
FROM #temp;
 
OPEN Datenbank_Cursor;
DECLARE @Name varchar(50);
FETCH NEXT FROM Datenbank_Cursor
INTO @Name
WHILE @@FETCH_STATUS = 0
    BEGIN
        --Select * From Datenbankname.INFORMATION_SCHEMA.TABLES
        ----Würde funktionieren aber so muss jeder Name eingetippt werden
        ----Wird eine neue Datenbank erstellt/gelöscht -> per Hand ändern.
        ----Daher soll es Variabel sein.
        ----Meine bisherigen Ideen:
        ----Entweder:
        --Use @Name
        --Select * From INFORMATION_SCHEMA.TABLES
        ----ODER--
        --Select * From @Name.INFORMATION_SCHEMA(.TABLES)
        ----ODER--
        --Use @Name
        --exec sp_tables;
        FETCH NEXT FROM Datenbank_Cursor
        INTO @Name
    END;
CLOSE Datenbank_Cursor;
DEALLOCATE Datenbank_Cursor;
GO
 
drop table #temp

Wie ihr seht habe ich mir Gedanken gemacht, aber irgendwie klappts nicht so richtig

 

[Tommi]

Hi,

die Informationen, welche Datenbanken in der SQL-Server.Instanz angelegt sind, findest du in der Systemdatenbank MSDB.
Versuchs mal mit der folgenden Abfrage hierzu:

SELECT * FROM msdb.sys.databases

Vielleich hilft dir hierzu auch noch der folgende Thread:
https://www.datenbankforum.com/threads/tabellen-und-spalteneigenschaften-auslesen.873/

Viele Grüße,
Tommi

 

[12nico07]

Hi Tommi, danke für die Antwort leider ist das aber nicht ganz das was ich brauche.
Die Datenbanken suche ich mit folgendem Code raus

exec sp_helpdb

Die Tabellen in einer Datenbank mit:

Select * From Datenbankname.INFORMATION_SCHEMA.TABLES

Das Problem ist aber das ich den Datenbanname nicht variabel bekomme. Ich müsste jeden Datenbanknamen per Hand einfügen.
Habe es im Code oben ja probiert in einer Schleife variabel zu machen aber das klappt leider nicht so ganz.

 

[Tommi]

Hi,

sorry - da habe ich zu kurz gedacht.

Das wird mit deinem Konstrukt so schon gehen, wenn du deine SELECT-Abfrage in einen String packst.
Diesen String kannst du dann über eine EXEC-Kommando ausführen.

Das könnte dann so aussehen:

DECLARE @SQL nvarchar(max)
 
SET @SQL = 'SELECT ... FROM =' + @Name + '_INFORMATION_SCHEMA.TABLES'
 
INSERT INTO #temp
EXEC (@SQL)

Viele Grüße,
Tommi

 

[12nico07]

Ja so etwas in der Richtung meinte ich, nur leider bekomme ich auch hier eine Fehlermeldung:

Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von '='.

Ich verstehe nur nicht genau warum es immer eine Fehlermeldung gibt, auch bei den Versuchen die ich im Code ausgkommentiert habe, gab es immer Fehlermeldungen.

 

[12nico07]

Habe es jetzt teilweise gelöst:

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

Listet mir alle Tabellen in alles Datenbanken (auf die ich Zugriff habe) auf.
Jetzt muss ich es nurnoch ins richtige Format bringen und das Datum herausfinden.

 

[ukulele]

Da ist auch ein = hinter FROM zuviel drin. Das SQL Statement als String zu speichern ist auch nicht ganz einfach da ' natürlich den String abschließt und durch '' ersetzt werden muss.

 

[12nico07]

Klar das = ist zuviel, muss ich wohl übersehen haben, warum das ' durch " ersetzt werden soll versteh ich nicht, es funktioniert so (Bei "
'_INFORMATION_SCHEMA.TABLES'" muss der erste "_" durch "." ersetzt werden

Habe hier nochmal abschließend die zwei Möglichkeiten gepostet um an alle Tabellen in sämtlichen Datenbanken (mit Zugriffsberechtigung) zu kommen. Entweder

--Erstellt eine neue temporäre Tabelle
--und füllt diese mit den Datenbanken
    create table #temp
(
name sysname
,db_size nvarchar(13)
,owner sysname
,dbid smallint
,created nvarchar(11)
,status nvarchar(600)
,compatibility_level tinyint
)
go
insert into #temp
exec sp_helpdb
 
--Cursor sucht nur die Daten aus der Spalte "name" aus Tabelle #temp
--Also die Namen der Datenbanken
DECLARE Datenbank_Cursor CURSOR FOR
SELECT name
FROM #temp;
 
OPEN Datenbank_Cursor;
DECLARE @Name varchar(50);
FETCH NEXT FROM Datenbank_Cursor
INTO @Name
WHILE @@FETCH_STATUS = 0
    BEGIN
       
        DECLARE @SQL nvarchar(max)
 
        SET @SQL = 'SELECT * FROM ' + @Name + '.INFORMATION_SCHEMA.TABLES'
 
        EXEC (@SQL)
       
        FETCH NEXT FROM Datenbank_Cursor
        INTO @Name
    END;
CLOSE Datenbank_Cursor;
DEALLOCATE Datenbank_Cursor;
GO
 
drop table #temp

Oder (deutlich weniger Code und in diesem Fall mein Favorit, da ein Datum angezeigt wird)

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

Bzw. angepasst an meine Anforderungen:

sp_msforeachdb 'select name, create_date, modify_date from [?].sys.tables'

 

[ukulele]

Nunja ich meinte wenn du im Code einen ' verwendest und diesen Code in einen String packen möchtest musst du ' durch '' ersetzen. In deinem Fal nicht notwendig.