Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Einen Wert aus der Spalte abfragen

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von lapadula, 7 August 2016.

  1. lapadula

    lapadula Aktiver Benutzer

    Hallo, ich weiss nicht wie ich folgendes realisieren soll.

    Ich lasse mir alle Spalten einer Tabelle ausgeben. Ich möchte von denen den Namen, Datentyp und Nullwert.
    Nun möchte ich auch EINEN Wert (den ersten oder Random) aus der Spalte dazuhaben. Kann man sowas realisieren?

    Das ist die Abfrage, die ich bisher habe:

    select
    Column_Name,
    Data_Type,
    Is_Nullable

    from INFORMATION_SCHEMA.columns
    where table_name = 'xxx';
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Nicht trivial. Jeder Spalte hat schon mal verschiedene Datentypen - was soll also die weitere Spalte für einen Datentyp haben? Was Du machen kannst: von dem, was du jetzt hast, dynamisch noch eine Abfrage auf einen Wert dieser Spalte machen und dann z.B. in TEXT casten und dranhängen. Das wird aber, so denke ich, wirklich nur via dyn. SQL und Execute gehen.

    Was ist denn das Ziel der Übung?
     
  3. lapadula

    lapadula Aktiver Benutzer

    Es soll eine Datenbankkonvertierung stattfinden. Meine Aufgabe ist es einen Datenabgleich durchzuführen. D. h.

    ich soll Alle Tabellen, Spaltennamen, Datentyp der Spalte, Nullwert und eben einen Beispielwert aus der Datenbank in Excell übertragen. Diese Datenbank hat über 3700 Zeilen, diese per Hand rauszuschreiben würde ewig dauern. Deshalb wollte ich das ganze per Abfrage ausgeben lassen und in Excell kopieren.

    Von dem was du geschrieben hast, habe ich noch keine Ahnung. Muss ich mir erstmal angucken, danke für den Tip.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    um es mal zu zeigen, in PostgreSQL:

    Code:
    test=*# \d a;
      Tabelle „public.a“
     Spalte |  Typ  | Attribute
    --------+---------+-----------
     id  | integer | not null
     mail  | text  |
    Indexe:
      "a_pkey" PRIMARY KEY, btree (id)
    
    test=*# select * from lapadula('a');
     _column_name | _data_type | _is_nullable | _value
    --------------+------------+--------------+--------
     id  | integer  | NO  | 1
     mail  | text  | YES  |
    (2 Zeilen)
    
    Die Funktion ist wie folgt definiert:

    Code:
    create or replace function lapadula(in _table_name text, out _column_name text, out _data_type text, out _is_nullable text, out _value text) returns setof record as $$
    declare r record;
    begin
      for r in select column_name, data_type, is_nullable from information_schema.columns where table_name = _table_name loop
      _column_name := r.column_name;
      _data_type  := r.data_type;
      _is_nullable := r.is_nullable;
      execute 'select ' || r.column_name::text || ' from ' || _table_name || ' limit 1' into _value;
      return next;
      end loop;
    end;
    $$language plpgsql;
    
     
    lapadula gefällt das.
  5. akretschmer

    akretschmer Datenbank-Guru

    Klingt nach einem lustigen Zeitvertreib ;-)
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Dachte ich mir schon, daher hatte ich schon mal parallel ein Beispiel vorbereitet.
     
  7. lapadula

    lapadula Aktiver Benutzer

    So lustig wie sich das anhört ist es gar nicht :D

    Vielen Dank für dein Beispiel, ich versuchs mal umzusetzen.
     
    akretschmer gefällt das.
  8. lapadula

    lapadula Aktiver Benutzer

    Mein Arbeitskollege hat mit dabei geholfen und es geschafft. Hätte ich alleine nicht geschafft.

    Falls es jemandem weiterhelfen sollte hier der code:

    Code:
    SET NOCOUNT ON
    
    CREATE TABLE ##colsBeispiel  (
        ID INT IDENTITY(1,1),
        SchName VARCHAR(128),
        TblName VARCHAR(128),
        ColName VARCHAR(128),
        DataType VARCHAR(128),
        Beispiel Varchar(max),
        PRIMARY KEY(ID)
        )
    
    DECLARE @currTbl VARCHAR(128)
    DECLARE @currCol VARCHAR(128)
    DECLARE @currSch VARCHAR(128)
    DECLARE @currType VARCHAR(128)
    DECLARE @limit INT
    DECLARE @i INT
    DECLARE @sql NVARCHAR(4000)
    
    INSERT INTO ##colsBeispiel (
        SchName,
        TblName,
        ColName,
        DataType,
        Beispiel
        )
    SELECT
        c.TABLE_SCHEMA,
        c.TABLE_NAME,
        c.COLUMN_NAME,
        c.DATA_TYPE,
        '' AS Beispiel
    FROM INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON c.TABLE_CATALOG = t.TABLE_CATALOG
    AND c.TABLE_NAME = t.TABLE_NAME
    AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
    WHERE t.TABLE_TYPE = 'BASE TABLE'
    
    SET @limit = (SELECT MAX(ID) FROM ##colsBeispiel)
    SET @i = 1
    
    WHILE @i <= @limit
    BEGIN  
        SELECT @currSch = SchName,
            @currTbl = TblName,
            @currCol = ColName,
            @currType = DataType
        FROM ##colsBeispiel
        WHERE ID = @i and DataType <> 'image'
           
        SET @sql = 'UPDATE ##colsBeispiel
            SET Beispiel = (Select Top 1 ' + QUOTENAME(@currCol) + ' FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' where ' + QUOTENAME(@currCol) + ' is not null )
            WHERE ID = ' + CAST(@i AS VARCHAR(20));
    
        EXEC(@sql)
    
        SET @i = @i + 1    
    END
    
    SELECT * FROM ##colsBeispiel
    
    DROP TABLE ##colsBeispiel
     
  9. ukulele

    ukulele Datenbank-Guru

    Ich habe sowas vor langer Zeit auch mal "gebastelt". Es ist nicht besonders elegant formuliert, vieleicht liefert dir das aber noch ein paar Anregungen was man erfassen könnte.
    Code:
    IF EXISTS   (   SELECT   1
             FROM   dbo.sysobjects
             WHERE   id = OBJECT_ID(N'[dbo].[index]')
             AND     type IN ( N'U') )
    BEGIN
       SELECT   'Warning: Table Index already exists'
    --   DROP TABLE [index]
    --   DELETE FROM [index]
    END
    
    CREATE TABLE [index] (   table_name VARCHAR(100),
                 column_name VARCHAR(100),
                 datatype VARCHAR(20),
                 [length] SMALLINT,
                 value_type VARCHAR(30), -- Text, Ganzzahl, Zahl
                 value_min VARCHAR(20), -- bei Text Länge
                 value_avg VARCHAR(20), -- bei Text Länge
                 value_max VARCHAR(20), -- bei Text Länge
                 value_count INT,
                 value_unique INT,
                 value_null INT,
                 value_list VARCHAR(255), -- bei unter 10 Einträgen
                 value_shortest VARCHAR(255),
                 value_longest VARCHAR(255) )
    
    INSERT INTO [index](table_name,column_name,datatype,[length])
    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'
    AND     sysobjects.name != 'index'
    AND     systypes.name != 'sysname'
     
  10. ukulele

    ukulele Datenbank-Guru

    Code:
    DECLARE   @query VARCHAR(8000),
         @table_name VARCHAR(100),
         @column_name VARCHAR(100),
         @datatype VARCHAR(20)
    
    WHILE   EXISTS (   SELECT   1
               FROM   [index]
               WHERE   value_type IS NULL )
    BEGIN
       SELECT   TOP 1
           @table_name = table_name,
           @column_name = column_name,
           @datatype = datatype
       FROM   [index]
       WHERE   value_type IS NULL
    
       IF     @datatype NOT IN (   'uniqueidentifier','int','smallint','bigint','numeric','float','money',
                     'char','nchar','varchar','nvarchar','text','ntext','bit',
                     'datetime','smalldatetime' )
       BEGIN
         UPDATE   [index]
         SET     value_type = 'n/a'
         WHERE   table_name = @table_name
         AND     column_name = @column_name
       END
       ELSE
       BEGIN
         IF     @datatype = 'uniqueidentifier'
         BEGIN
           SET     @query = '
    DECLARE   @value_type VARCHAR(30),
         @value_count INT,
         @value_unique INT,
         @value_null INT
    
    SET     @value_count = (   SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_unique = (   SELECT   count(DISTINCT [' + @column_name + '])
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_null = (     SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NULL )
    SET     @value_type = (     CASE
                   WHEN   @value_count = @value_unique
                   AND     @value_null = 0
                   THEN   ''UID, unique, not null''
                   WHEN   @value_count = @value_unique
                   AND     @value_null > 0
                   THEN   ''UID, unique, null''
                   WHEN   @value_count > @value_unique
                   AND     @value_null = 0
                   THEN   ''UID, not null''
                   WHEN   @value_count > @value_unique
                   AND     @value_null > 0
                   THEN   ''UID, null''
                   END )
    
    UPDATE   [index]
    SET     value_type = @value_type,
         value_count = @value_count,
         value_unique = @value_unique,
         value_null = @value_null
    WHERE   table_name = ''' + @table_name + '''
    AND     column_name = ''' + @column_name + '''
    '
           EXEC(@query)
         END
         IF     @datatype IN ( 'int','smallint','bigint','numeric','float','money' )
         BEGIN
           SET     @query = '
    DECLARE   @value_type VARCHAR(30),
         @value_min VARCHAR(20),
         @value_avg VARCHAR(20),
         @value_max VARCHAR(20),
         @value_count INT,
         @value_unique INT,
         @value_null INT,
         @value_list VARCHAR(255)
    
    SET     @value_min = (     SELECT   min([' + @column_name + '])
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_avg = (   CASE
                 WHEN   ''' + @datatype + ''' IN ( ''int'',''smallint'' )
                 THEN   (   SELECT   avg(cast([' + @column_name + '] AS BIGINT))
                       FROM   [' + @table_name + ']
                       WHERE   [' + @column_name + '] IS NOT NULL )
                 ELSE   (   SELECT   avg([' + @column_name + '])
                       FROM   [' + @table_name + ']
                       WHERE   [' + @column_name + '] IS NOT NULL )
                 END )
    SET     @value_max = (     SELECT   max([' + @column_name + '])
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    
    
    SET     @value_count = (   SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_unique = (   SELECT   count(DISTINCT [' + @column_name + '])
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_null = (     SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NULL )
    IF EXISTS (   SELECT   1
           FROM   [' + @table_name + ']
           WHERE   cast([' + @column_name + '] AS VARCHAR(20)) LIKE ''%.%''
           OR     cast([' + @column_name + '] AS VARCHAR(20)) LIKE ''%,%'' )
    BEGIN
       SET     @value_type = ''number''
    END
    ELSE
    BEGIN
       SET     @value_type = ''integer''
    END
    IF     @value_unique BETWEEN 1 AND 10
    BEGIN
       DECLARE   @counter SMALLINT
       SET     @counter = @value_unique
       WHILE   @counter > 0
       BEGIN
         SET     @value_list = (   SELECT   isnull(@value_list,'''') + cast([' + @column_name + '] AS VARCHAR(20)) + ''; ''
                     FROM   (   SELECT   TOP (@counter) [' + @column_name + '],
                               ROW_NUMBER() OVER (ORDER BY [' + @column_name + '] DESC) AS zeilennr
                           FROM   (   SELECT   DISTINCT [' + @column_name + ']
                                 FROM   [' + @table_name + ']
                                 WHERE   [' + @column_name + '] IS NOT NULL ) tt
                           ORDER BY [' + @column_name + '] DESC) t
                     WHERE   t.zeilennr = @counter )
         SET     @counter = @counter - 1
       END
    END
    
    UPDATE   [index]
    SET     value_type = @value_type,
         value_min = @value_min,
         value_avg = @value_avg,
         value_max = @value_max,
         value_count = @value_count,
         value_unique = @value_unique,
         value_null = @value_null,
         value_list = @value_list
    WHERE   table_name = ''' + @table_name + '''
    AND     column_name = ''' + @column_name + '''
    '
           EXEC(@query)
         END
     
  11. ukulele

    ukulele Datenbank-Guru

    Code:
        IF     @datatype IN ( 'char','nchar','varchar','nvarchar' )
         BEGIN
           SET     @query = '
    DECLARE   @value_type VARCHAR(30),
         @value_min VARCHAR(20),
         @value_avg VARCHAR(20),
         @value_max VARCHAR(20),
         @value_count INT,
         @value_unique INT,
         @value_null INT,
         @value_list VARCHAR(255),
         @value_shortest VARCHAR(255),
         @value_longest VARCHAR(255)
    
    SET     @value_min = (     SELECT   min(len([' + @column_name + ']))
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_avg = (     SELECT   avg(len([' + @column_name + ']))
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_max = (     SELECT   max(len([' + @column_name + ']))
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    
    SET     @value_count = (   SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_unique = (   SELECT   count(DISTINCT [' + @column_name + '])
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_null = (     SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NULL )
    SET     @value_type = ''text''
    
    IF     @value_unique BETWEEN 1 AND 10
    BEGIN
       DECLARE   @counter SMALLINT
       SET     @counter = @value_unique
       WHILE   @counter > 0
       BEGIN
         SET     @value_list = (   SELECT   isnull(@value_list,'''') + cast([' + @column_name + '] AS VARCHAR(20)) + ''; ''
                     FROM   (   SELECT   TOP (@counter) [' + @column_name + '],
                               ROW_NUMBER() OVER (ORDER BY [' + @column_name + '] DESC) AS zeilennr
                           FROM   (   SELECT   DISTINCT [' + @column_name + ']
                                 FROM   [' + @table_name + ']
                                 WHERE   [' + @column_name + '] IS NOT NULL ) tt
                           ORDER BY [' + @column_name + '] DESC) t
                     WHERE   t.zeilennr = @counter )
         SET     @counter = @counter - 1
       END
    END
    
    SET     @value_shortest = (     SELECT   min([' + @column_name + '])
                     FROM   [' + @table_name + ']
                     WHERE   len([' + @column_name + ']) = (   SELECT   min(len([' + @column_name + ']))
                                         FROM   [' + @table_name + ']
                                         WHERE   [' + @column_name + '] IS NOT NULL ) )
    SET     @value_longest = (     SELECT   max([' + @column_name + '])
                     FROM   [' + @table_name + ']
                     WHERE   len([' + @column_name + ']) = (   SELECT   max(len([' + @column_name + ']))
                                         FROM   [' + @table_name + ']
                                         WHERE   [' + @column_name + '] IS NOT NULL ) )
    
    UPDATE   [index]
    SET     value_type = @value_type,
         value_min = @value_min,
         value_avg = @value_avg,
         value_max = @value_max,
         value_count = @value_count,
         value_unique = @value_unique,
         value_null = @value_null,
         value_list = @value_list,
         value_shortest = @value_shortest,
         value_longest = @value_longest
    WHERE   table_name = ''' + @table_name + '''
    AND     column_name = ''' + @column_name + '''
    '
           EXEC(@query)
         END
         IF     @datatype IN ( 'text','ntext' )
         BEGIN
           SET     @query = '
    DECLARE   @value_type VARCHAR(30),
         @value_count INT,
         @value_null INT
    
    SET     @value_count = (   SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_null = (     SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NULL )
    SET     @value_type = ''longtext''
    
    UPDATE   [index]
    SET     value_type = @value_type,
         value_count = @value_count,
         value_null = @value_null
    WHERE   table_name = ''' + @table_name + '''
    AND     column_name = ''' + @column_name + '''
    '
           EXEC(@query)
         END
         IF     @datatype IN ( 'bit' )
         BEGIN
           SET     @query = '
    DECLARE   @value_type VARCHAR(30),
         @value_count INT,
         @value_null INT
    
    SET     @value_count = (   SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_null = (     SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NULL )
    SET     @value_type = ''bit''
    
    UPDATE   [index]
    SET     value_type = @value_type,
         value_count = @value_count,
         value_null = @value_null
    WHERE   table_name = ''' + @table_name + '''
    AND     column_name = ''' + @column_name + '''
    '
           EXEC(@query)
         END
         IF     @datatype IN ( 'datetime','smalldatetime' )
         BEGIN
           SET     @query = '
    DECLARE   @value_type VARCHAR(30),
         @value_min VARCHAR(20),
         @value_max VARCHAR(20),
         @value_count INT,
         @value_unique INT,
         @value_null INT,
         @value_list VARCHAR(255)
    
    SET     @value_min = (     SELECT   convert(VARCHAR(19), min([' + @column_name + ']), 121)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_max = (     SELECT   convert(VARCHAR(19), max([' + @column_name + ']), 121)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    
    
    SET     @value_count = (   SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_unique = (   SELECT   count(DISTINCT [' + @column_name + '])
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NOT NULL )
    SET     @value_null = (     SELECT   count(1)
                   FROM   [' + @table_name + ']
                   WHERE   [' + @column_name + '] IS NULL )
    SET     @value_type = ''datetime''
    
    IF     @value_unique BETWEEN 1 AND 10
    BEGIN
       DECLARE   @counter SMALLINT
       SET     @counter = @value_unique
       WHILE   @counter > 0
       BEGIN
         SET     @value_list = (   SELECT   isnull(@value_list,'''') + convert(VARCHAR(23), [' + @column_name + '], 121) + ''; ''
                     FROM   (   SELECT   TOP (@counter) [' + @column_name + '],
                               ROW_NUMBER() OVER (ORDER BY [' + @column_name + '] DESC) AS zeilennr
                           FROM   (   SELECT   DISTINCT [' + @column_name + ']
                                 FROM   [' + @table_name + ']
                                 WHERE   [' + @column_name + '] IS NOT NULL ) tt
                           ORDER BY [' + @column_name + '] DESC) t
                     WHERE   t.zeilennr = @counter )
         SET     @counter = @counter - 1
       END
    END
    
    UPDATE   [index]
    SET     value_type = @value_type,
         value_min = @value_min,
         value_max = @value_max,
         value_count = @value_count,
         value_unique = @value_unique,
         value_null = @value_null,
         value_list = @value_list
    WHERE   table_name = ''' + @table_name + '''
    AND     column_name = ''' + @column_name + '''
    '
           EXEC(@query)
         END
       END
    END
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden