Einen Wert aus der Spalte abfragen

L

lapadula

Aktiver Benutzer
Beiträge
33
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';
 
Werbung:
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?
 
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.
 
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;
 
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
 
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'
 
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
 
Werbung:
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
 

Ähnliche Themen

K
Spaltenname anzeigen mit information_schema.table_constraints
Antworten
4
Aufrufe
265
BerndB
BerndB
N
Hierarchische Abfrage mit Marker
Antworten
6
Aufrufe
129
dabadepdu
dabadepdu
I
Ausgabe der CSV, mehrere Werte pro Spalte in einer Zeile kombinieren
2
Antworten
15
Aufrufe
329
ikodi
I
R
zwei Abfragen auf eine Tabelle zusammenführen?
Antworten
3
Aufrufe
349
ukulele
ukulele
J
Update von Attributen verschiedener Tabellen
Antworten
6
Aufrufe
221
akretschmer
akretschmer
Zurück
Oben