Tabellen und Spalteneigenschaften auslesen

[ukulele]

Mit diesem Select kann man sich bequem eine Übersicht über vorhandene Tabellen und Spalten verschaffen:

SELECT    sysobjects.name AS table_name,
        syscolumns.name AS column_name,
        systypes.name AS datatype,
        syscolumns.[length] AS [length]
FROM    sysobjects
JOIN    syscolumns ON sysobjects.id = syscolumns.id
JOIN    systypes ON syscolumns.xtype = systypes.xtype
WHERE    sysobjects.xtype = 'U'
ORDER BY sysobjects.[name],syscolumns.colid

Ich würde aber gerne noch einen Schritt weiter gehen und z.B. den maximalen Wert, längsten Wert und so weiter einer jeden Spalte auslesen. Das geht aber nur, wenn ich einen Weg finde, Tabellenname und Spaltenname (in meinem Select nur Werte) innerhalb des Selects für einen Subselect zu verwenden. In etwa so:

SELECT    sysobjects.name AS table_name,
        syscolumns.name AS column_name,
        systypes.name AS datatype,
        syscolumns.[length] AS [length],
        (    SELECT    TOP 1 syscolumns.name
            FROM    sysobjects.name ) AS bla
FROM    sysobjects
JOIN    syscolumns ON sysobjects.id = syscolumns.id
JOIN    systypes ON syscolumns.xtype = systypes.xtype
WHERE    sysobjects.xtype = 'U'
ORDER BY sysobjects.[name],syscolumns.colid

Irgendjemand eine Idee?

 

[Tommi]

Hallo ukulele,

hier wirst du mit einer einfachen View nicht auskommen. Das wirst du über eine Prozedur lösen müssen.
Den maximalen Inhalt einer Spalte und auch den höchsten benutzen Auto-Wert kannst du aus den Statistik-Views direkt gewinnen.
Wenn du aber auch die tatsächliche Längen-Nutzung einer bereitgestellten Spalte wissen möchtest, z.B. um abschätzen zu können, dass die Länge ausreichend definiert ist, musst du auf die Dateninhalte selbst zugreifen.
Interessant wären die Angaben ja auch nur für Spalten, in denen variable Datenlängen eingetragen werden können (z.B. varchar, varbinary etc.).

Die mir bekannten Statistik-Views, -Funktionen und -Prozeduren, beziehen sich nur auf Tabellen-Ebene, nicht auf die Spalten-Ebene.
Es kann natürlich sein, dass es tatsächlich eine Funktion gibt, die diese Werte bereits ausgibt, aber eine solche ist mir leider nicht direkt bekannt (auch keine undokumentierte).

Daher müßtest du das selbst programmieren.

Da du über die Statistik-Views des SQL Servers aber nur Namen der Spalten und Tabellen aus Inhalten bekommst, kannst du diese Inhalte auch nur zu einem String verarbeiten und so eine entsprechende SELECT-Abfrage bilden, die sich dann über ein Execute ausführen läßt, womit wir wieder bei der Prozedur sind.

Ich habe so etwas tatsächlich einmal für ein SQL Server 2000 System gemacht, aber das müßte ich noch einmal raussuchen und wahrscheinlich auch noch ein wenig umprogrammieren.
Ich kann dir dieses Beispiel gerne hier zur Verfügung stellen, du musst mir nur ein wenig Zeit lassen, das zu finden

Viele Grüße,
Tommi

 

[ukulele]

Auf den Gedanken bin ich auch gekommen aber er gefiel mir einfach nicht aufgrund der imensen Ausmaße einer solchen Lösung. Daher habe ich immer schon nach einen einfachen Weg gesucht soetwas wie Ausgabewerte als Tabellen oder Spaltennamen nutzbar zu machen.

Die Aufgabe war eigentlich nur ein Datenimport. Es ging mir eigentlich nur darum einen möglichst einheitlichen Weg für den zukünftigen Import mir zunächst unbekannter Daten zu finden. Wenn ich eine Spalte wie z.B. Stückzahl habe die 2 Nachkomma Stellen hat, auf den ersten Blick aber immer nur ganze Zahlen hält dann ist soetwas interessant. Auch die Anzahl der Nachkommastellen kann ja variieren. Es dient also zunächst der Bestimmung des Datentyps um dann im Anschluss in ein sinnvolles Datenformat ohne Konvertierungsfehler zu wechseln mit dem sich besser rechnen läßt.

Auch sind Dinge wie höchste und niedrigste Werte von Nutzem wenn es um die Frage geht ob diese importierten Daten plausibel sind.

Ich habe jetzt unter Verwendung des Ausgangsstatements eine Tabelle zu Tabelleninformationen erstellt und diese dann per Script um entsprechende Daten gefüllt. Ich muss aber dazu sagen das das ganze zu zeitaufwendig wird wenn man alle eventualitäten (wie z.B. länderspezifische Datumsformate) berücksichtigt. Dennoch kam dabei eine ganz nette Übersicht zu stande und vieleicht werde ich das bei der nächsten Datenanalyse weiter ausbauen.

Hier mal der Code:

-- DROP TABLE [index]
CREATE TABLE [index] (    table_name VARCHAR(20),
                        column_name VARCHAR(20),
                        datatype VARCHAR(20),
                        possible_datatype VARCHAR(20),
                        [length] SMALLINT,
                        value_min VARCHAR(20),
                        value_avg VARCHAR(20),
                        value_max VARCHAR(20),
                        length_max SMALLINT,
                        length_value VARCHAR(20) )
 
INSERT INTO [index]
SELECT    sysobjects.name AS table_name,
        syscolumns.name AS column_name,
        systypes.name AS datatype,
        NULL AS possible_datatype,
        syscolumns.[length] AS [length],
        NULL AS value_min,
        NULL AS value_avg,
        NULL AS value_max,
        NULL AS length_max,
        NULL AS length_value
FROM    sysobjects
JOIN    syscolumns ON sysobjects.id = syscolumns.id
JOIN    systypes ON syscolumns.xtype = systypes.xtype
WHERE    sysobjects.xtype = 'U'
AND        sysobjects.name != 'index'
 
DECLARE    @query VARCHAR(4000),
        @table_name VARCHAR(20),
        @column_name VARCHAR(20)
 
WHILE    EXISTS (    SELECT    1
                    FROM    [index]
                    WHERE    possible_datatype IS NULL )
BEGIN
    SELECT    TOP 1
            @table_name = table_name,
            @column_name = column_name
    FROM    [index]
    WHERE    possible_datatype IS NULL
 
    SET        @query = '
    DECLARE    @possible_datatype VARCHAR(20)
 
    SET        @possible_datatype =  (    CASE
                                    WHEN    (    SELECT    count(*)
                                                FROM    ' + @table_name + '
                                                WHERE    isdate(' + @column_name + ') = 0
                                                OR        datepart(yyyy,cast(' + @column_name + ' AS DATETIME)) < 2000 ) = 0
                                    THEN    ''date''
                                    WHEN    (    SELECT    count(*)
                                                FROM    ' + @table_name + '
                                                WHERE    ' + @column_name + ' IS NOT NULL
                                                AND    (    isnumeric(' + @column_name + ') = 0
                                                OR        ' + @column_name + ' LIKE ''%,%'' )) = 0
                                    THEN    ''ganzzahl''
                                    WHEN    (    SELECT    count(*)
                                                FROM    ' + @table_name + '
                                                WHERE    ' + @column_name + ' IS NOT NULL
                                                AND        isnumeric(replace(' + @column_name + ','','',''.'')) = 0 ) = 0
                                    THEN    ''fliesskomma''
                                    ELSE    ''unknown''
                                    END )
 
    UPDATE    [index]
    SET        possible_datatype = @possible_datatype
    WHERE    table_name = ''' + @table_name + '''
    AND        column_name = ''' + @column_name + '''
 
    IF        @possible_datatype = ''date''
    BEGIN
        UPDATE    [index]
        SET        value_min = (    SELECT    TOP 1 convert(VARCHAR(20), cast(' + @column_name + ' AS DATETIME), 104)
                                FROM    ' + @table_name + '
                                WHERE    cast(' + @column_name + ' AS DATETIME) = (    SELECT    min(cast(' + @column_name + ' AS DATETIME))
                                                                                    FROM    ' + @table_name + ' ) ),
                value_max = (    SELECT    TOP 1 convert(VARCHAR(20), cast(' + @column_name + ' AS DATETIME), 104)
                                FROM    ' + @table_name + '
                                WHERE    cast(' + @column_name + ' AS DATETIME) = (    SELECT    max(cast(' + @column_name + ' AS DATETIME))
                                                                                    FROM    ' + @table_name + ' ) )
        WHERE    table_name = ''' + @table_name + '''
        AND        column_name = ''' + @column_name + '''
    END
    IF        @possible_datatype = ''ganzzahl''
    BEGIN
        UPDATE    [index]
        SET        value_min = (    SELECT    min(cast(' + @column_name + ' AS INT))
                                FROM    ' + @table_name + ' ),
                value_max = (    SELECT    max(cast(' + @column_name + ' AS INT))
                                FROM    ' + @table_name + ' )
        WHERE    table_name = ''' + @table_name + '''
        AND        column_name = ''' + @column_name + '''
        UPDATE    [index]
        SET        value_avg = (    SELECT    avg(cast(' + @column_name + ' AS INT))
                                FROM    ' + @table_name + '
                                WHERE    ' + @column_name + ' IS NOT NULL )
        WHERE    table_name = ''' + @table_name + '''
        AND        column_name = ''' + @column_name + '''
    END
    IF        @possible_datatype = ''fliesskomma''
    BEGIN
        UPDATE    [index]
        SET        value_min = (    SELECT    left(convert(VARCHAR(40),min(cast(replace(' + @column_name + ','','',''.'') AS NUMERIC(38,20)))),20)
                                FROM    ' + @table_name + ' ),
                value_max = (    SELECT    left(convert(VARCHAR(40),max(cast(replace(' + @column_name + ','','',''.'') AS NUMERIC(38,20)))),20)
                                FROM    ' + @table_name + ' )
        WHERE    table_name = ''' + @table_name + '''
        AND        column_name = ''' + @column_name + '''
        UPDATE    [index]
        SET        value_avg = (    SELECT    left(convert(VARCHAR(40),avg(cast(replace(' + @column_name + ','','',''.'') AS NUMERIC(38,20)))),20)
                                FROM    ' + @table_name + '
                                WHERE    ' + @column_name + ' IS NOT NULL )
        WHERE    table_name = ''' + @table_name + '''
        AND        column_name = ''' + @column_name + '''
    END
    IF        @possible_datatype = ''unknown''
    BEGIN
        UPDATE    [index]
        SET        length_max = (    SELECT    max(len(' + @column_name + '))
                                FROM    ' + @table_name + ' ),
                length_value = (    SELECT    TOP 1 ' + @column_name + '
                                    FROM    ' + @table_name + '
                                    WHERE    len(' + @column_name + ') = (    SELECT    max(len(' + @column_name + '))
                                                                            FROM    ' + @table_name + ' ) )
        WHERE    table_name = ''' + @table_name + '''
        AND        column_name = ''' + @column_name + '''
    END
    '
    EXEC (@query)
END
 
SELECT    *
FROM    [index]
ORDER BY column_name,table_name

Sollte eigentlich auf jedem MSSQL laufen und liefert einen groben Überblick über die Daten. Natürlich kann es erstmal nur ein Datumsformat und Zahlen erkennen, das was halt grade gebraucht wurde.

 

[Tommi]

Hi,

ganz schön viel Arbeit .
So weit mußte ich in meiner Abfrage nicht gehen. Hast du schon mal überlegt hier CLR einzusetzen?
Einige deiner Umrechnungen sind ja doch recht komplex, auch die Anzahl der IF-Abfragen ist recht hoch.
Die Hilfe von .NET dafür in Anspruch zu nehmen, erscheint mir an der ein oder anderen Stelle ganz hilfreich - nur mal so als Idee.

Viele Grüße,
Tommi

 

[ukulele]

Vermutlich anders viel leichter, ich kann aber kein .NET