Spalten- und Tabelleninformationen auslesen

Dilbert

Dilbert

Neuer Benutzer
Beiträge
4
Hallo,

ich muss eine Art Export-Funktion für MS-SQL Datenbanken (ab Version 2008) programmieren.
Dazu brauche ich einige Informationen über die DB sowie deren Tabellen.

Vieles bekomme ich auch problemlos heraus.
Manches bequem, anderes über Umwege. (Vielleicht hat ja jemand noch elegantere Lösungen?)

Ich bekomme bereits:
- die Tabellennamen:
Select * From sys.tables

- Die Spaltennamen:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Test'

- Mit obiger Abfrage auch ob es "Nullable" ist, sowie den Datentyp.

- Die Primärschlüssel (ist aber recht aufwändig, geht das auch eleganter?):
SELECT i.name AS index_name
,ic.index_column_id
,key_ordinal
,c.name AS column_name
,TYPE_NAME(c.user_type_id)AS column_type
,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('test')

- In der obigen Abfrage steckt auch die "IsIdentity", aber nur für den PK

Was mir fehlt:

1.
Ich brauche die "IsIdentity" auch wenn es KEIN Primärschlüssel ist.

2.
Den Datentyp in "verwertbarer" Form.
Ich bekomme mit obiger Abfrage leider nur "VarChar", und im getrennten Feld die Länge.
Wenn ich daraus eine neue Tabelle anlegen soll, muss ich das wieder zusammen basteln.
Und das ist leider für fast jeden Datentyp in einem anderen Feld!?!
Also müsste ich für jeden Datentyp irgendetwas eigenes stricken.
Da gibt es doch bestimmt etwas eleganteres!

3.
Die Namen indizierter Spalten.

4.
Ich brauche die "Change-Tracking" Eigenschaften der gesamten Tabelle.
(Nicht für die Spalten einzeln nötig)

5.
Ich brauche die "Change-Tracking" Eigenschaften der Datenbank.

Da ich ja Optimist bin habe ich noch die Hoffnung, dass es eine SP gibt, die mir genau diese Daten in einer einzigen hübschen Tabelle bequem zurück gibt.
:-)

Hat jemand Ideen?

Bye,

Dilbert
 
Werbung:
Hi,

ja so was ähnliches wie ein Dump.
Ohne die eigentlichen Tabelleninhalte und nur ein paar spezielle Tabellen- und Spalteneigenschaften.

Bye,

Dilbert
 
Dilbert schrieb:
Hi,

ja so was ähnliches wie ein Dump.
Ohne die eigentlichen Tabelleninhalte und nur ein paar spezielle Tabellen- und Spalteneigenschaften.

Bye,

Dilbert
Zum Vergrößern anklicken....


In PG wäre das -s bzw. --schema-only, M$SQL kenne ich nicht so. Ich glaube aber gehört zu haben, daß das da mit einem reinen SQL-Dump wohl nicht so trivial ist ...
 
Hi,

ich brauche das Ganze auch nicht als File, sondern innerhalb meiner Software zu Dokumentationszwecken. Das Dump-File ausgeben und dann wieder einlesen wäre zwar ein kleiner Umweg, aber maybe einfacher als mit "normalem" SQL?

Bye,

Dilbert
 
Hallo Dilbert,

ich habe da mal eine Abfrage geschrieben, die wie folgt aussieht:

Code: 
SELECT T.name as table_name,
T.object_id,
T.modify_date as table_changing,
C.column_id,
C.name as column_name,
(CASE    WHEN TY.name IN ('numeric','decimal') THEN TY.name+'('+CAST(C.precision AS varchar(50))+','+CAST(C.scale as varchar(50))+')'
        WHEN TY.name IN ('varchar','nvarchar','varbinary') THEN TY.name+'('+(CASE WHEN C.max_length=-1 THEN 'max' ELSE CAST(C.max_length AS varchar(50)) END)+')'
ELSE TY.name END) as data_type,
C.is_nullable as column_is_nullable,
C.is_identity as column_is_identity,
C.is_computed as column_is_computed,
ISNULL(CC.definition,'') as column_computed_definition
,(CASE    WHEN ISNULL(IC.index_column_id,0)>0 THEN 'PK'
        WHEN ISNULL(FC.type,'')='F' THEN 'FK'
ELSE '' END) as column_key
,ISNULL(IC.index_column_id,0) as column_primary_key
,ISNULL(I.name,'') as PK_name
,ISNULL(FC.name,'') as FK_name
FROM sys.tables T
INNER JOIN sys.columns C
    ON C.object_id=T.Object_id
INNER JOIN sys.types TY
    ON C.system_type_id = TY.system_type_id
LEFT OUTER JOIN sys.computed_columns CC
    ON CC.object_id = T.object_id
    AND CC.column_id = C.column_id
LEFT OUTER JOIN sys.indexes I
    INNER JOIN sys.index_columns IC
        ON I.object_id=IC.object_id
        AND I.index_id=IC.index_id
        AND I.is_primary_key=1
    ON I.object_id=T.object_id
    AND IC.column_id=C.column_id
LEFT OUTER JOIN sys.objects O
    INNER JOIN sys.foreign_keys FC
        ON FC.object_id = O.object_id
    INNER JOIN sys.foreign_key_columns FKC
        ON FKC.constraint_object_id=O.object_id
    ON O.type='F'
    AND O.parent_object_id=T.object_id
    AND FKC.parent_column_id=C.column_id
ORDER BY T.name, C.column_id

Im Übrigen: Die IsIdentity-Eigenschaft kann in einer Tabelle nur für EINE Spalte angegeben werden, niemals für mehrere!
Mit dieser Eigenschaft soll ein automatischer Wert zur eindeutigen Identifizierung einer einzelnen Zeile innerhalb der Tabelle erzeugt werden.
Eine doppelte Angabe dieser Eigenschaft ist also unnötig.

Das ist anders beim PK. Hier kann mehr als eine Spalte als PK angegeben werden, der dann ebenfalls eine Zeile eindeutig identifiziert.
Das ist aber ein anderer Sachverhalt im Vergleich zur IsIdentity-Eigenschaft.

Ich hoffe, das hilft.

Viele Grüße,
Tommi
 
Hi,

erstmal:
WOW!
Danke!
Das ist mal 'ne Abfrage!

Nur 2 Fragen hab' ich noch:
Bei meinen nvarchar's bekomme ich eine zweite Zeile mit "sysname".
Bekommt man die noch weg?
Und:
Das "table_changing" liefert mir ein Datum, auch wenn in der Tabelle die Änderungsnachverfolgung aus ist.
Kann man diese Änderungsnachverfolgung auch gezielt abfragen?
Also, ich muss nur wissen ob sie für die Tabelle an oder aus ist.
Nicht für die einzelnen Spalten.
(Also wäre eine getrennte Abfrage wahrscheinlich besser)

Das hilft mir aber schon mal ganz schön weiter!
Nochmal danke!

Bye,

Dilbert
 
Werbung:
Hallo Dilbert,

die Abfrage ist aus meinen "gesammelten Werken" und daher natürlich nicht 1:1 auf deine Bedürfnisse angepasst.
Die Angabe des "table_changing" ist lediglich die Angabe der letzten Änderung am Tabellenaufbau.
Dies ist keine Angabe der letzten inhaltlichen Änderung.

Die Auswertung von implementierten Mechanismen des SQL Servers wie "Änderungsnachverfolgung" und "Change Data Capture" sind zwar ganz nett, aber die Auswertung der dafür erstellten und bereitgestellten Tabellen, Funktionen und Prozeduren ist äußerst komplex. Mit einer solchen Auswertung habe ich mich noch nicht beschäftigt. Allgemein ist aber die Anwendung des CDC detaillierter als die Änderungsnachverfolgung.

Etwas mehr Informationen dazu findesz du hier:
https://msdn.microsoft.com/de-de/library/bb933994(v=sql.105).aspx

Viele Grüße,
Tommi
 
Zurück
Oben