Informix SQL - список всех полей и таблиц

Question

В Informix iSQL есть командаinfo tables;", который показывает все таблицы.
Синтаксис для просмотра полей и их соответствующих типов данных: "info columns for table;"

Есть ли подобная команда, которая показывает table.field для всех таблиц и всех полей?

Answer 1

Используя предпочтительную нотацию JOIN:

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column
  FROM "informix".systables  AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

или старомодная нотация "присоединяйся к где":

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column
  FROM "informix".systables AS t, "informix".syscolumns AS c
 WHERE t.tabid = c.tabid
   AND t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

Предполагая, что вы используете достаточно свежую версию IDS, вы можете делать заказы по столбцам, не указанным в списке выбора. Если вы получаете жалобы, добавьте столбцы заказа в список выбора.

Критерий соединения очевиден; tabtype = 'T' содержит только таблицы, а не представления, синонимы и другие подобные элементы, перечисленные в systables; В tabid >= 100 перечислены только таблицы, созданные явно в базе данных, а не системный каталог.

Это не включает информацию о типе - если вы хотите, вам нужно сделать немного больше работы. Вы найдете файл $INFORMIXDIR/etc/xpg4_is.sql он содержит грубое приближение к старой версии информационной схемы XPG4 (стандарт X/Open) (отсюда и название файла). Там есть функции и т. Д. Для декодирования информации о типах из syscolumns.coltype а также syscolumns.collength в узнаваемые строки. Однако я сильно подозреваю, что он не обрабатывает типы DISTINCT и другие пользовательские типы. Я буду рад, если я окажусь неправ, но... Если вы добавите соответствующие части этого файла в свою базу данных, вы также сможете получить информацию о типе.

Также обратите внимание, что все команды INFO в ISQL и DB-Access моделируются во внешнем интерфейсе, а не выполняются на сервере IDS. По сути, программы принимают запрос и преобразуют его в более сложный оператор SQL. Смотрите код в файле sqlinfo.ec это часть SQLCMD (доступная из Архива программного обеспечения IIUG) для того, как моя программа SQLCMD обрабатывает операторы INFO. (Примечание: вывод INFO в SQLCMD отформатирован иначе, чем вывод INFO в ISQL и DB-Access.)

Answer 2

Как упоминается в ответе Джонатана Леффера, полная обработка типов столбцов и деталей столбцов усложняется, как это видно из документации SYSCOLUMNS. Но если вы смотрите на базу данных, не использующую более сложные типы, это дополнение к его сценарию покажет базовый тип и допустимы ли значения NULL:

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column,
CASE 
  WHEN MOD(coltype,256)=0 THEN 'CHAR' 
  WHEN MOD(coltype,256)=1 THEN 'SMALLINT' 
  WHEN MOD(coltype,256)=2 THEN 'INTEGER' 
  WHEN MOD(coltype,256)=3 THEN 'FLOAT' 
  WHEN MOD(coltype,256)=4 THEN 'SMALLFLOAT' 
  WHEN MOD(coltype,256)=5 THEN 'DECIMAL' 
  WHEN MOD(coltype,256)=6 THEN 'SERIAL' 
  WHEN MOD(coltype,256)=7 THEN 'DATE' 
  WHEN MOD(coltype,256)=8 THEN 'MONEY' 
  WHEN MOD(coltype,256)=9 THEN 'NULL' 
  WHEN MOD(coltype,256)=10 THEN 'DATETIME' 
  WHEN MOD(coltype,256)=11 THEN 'BYTE' 
  WHEN MOD(coltype,256)=12 THEN 'TEXT' 
  WHEN MOD(coltype,256)=13 THEN 'VARCHAR' 
  WHEN MOD(coltype,256)=14 THEN 'INTERVAL' 
  WHEN MOD(coltype,256)=15 THEN 'NCHAR' 
  WHEN MOD(coltype,256)=16 THEN 'NVARCHAR' 
  WHEN MOD(coltype,256)=17 THEN 'INT8' 
  WHEN MOD(coltype,256)=18 THEN 'SERIAL8' 
  WHEN MOD(coltype,256)=19 THEN 'SET' 
  WHEN MOD(coltype,256)=20 THEN 'MULTISET' 
  WHEN MOD(coltype,256)=21 THEN 'LIST' 
  WHEN MOD(coltype,256)=22 THEN 'ROW (unnamed)' 
  WHEN MOD(coltype,256)=23 THEN 'COLLECTION' 
  WHEN MOD(coltype,256)=40 THEN 'LVARCHAR fixed-length opaque types' 
  WHEN MOD(coltype,256)=41 THEN 'BLOB, BOOLEAN, CLOB variable-length opaque types' 
  WHEN MOD(coltype,256)=43 THEN 'LVARCHAR (client-side only)' 
  WHEN MOD(coltype,256)=45 THEN 'BOOLEAN' 
  WHEN MOD(coltype,256)=52 THEN 'BIGINT' 
  WHEN MOD(coltype,256)=53 THEN 'BIGSERIAL' 
  WHEN MOD(coltype,256)=2061 THEN 'IDSSECURITYLABEL'
  WHEN MOD(coltype,256)=4118 THEN 'ROW (named)' 
  ELSE TO_CHAR(coltype)
END AS Type,
BITAND(coltype,256)=256 AS NotNull
  FROM "informix".systables  AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

Answer 3

Я сделал базовое дополнение, чтобы проверить столбец первичного ключа. Также добавлен порядок столбцов, длина типа данных. Это дает базовую информацию для генерации ETL.

SELECT TRIM(t.tabname)  AS TableName
,c.colno                AS ColumnOrder
,TRIM(c.colname)        AS ColumnName
,CASE 
  WHEN MOD(coltype,256)=0 THEN 'CHAR' 
  WHEN MOD(coltype,256)=1 THEN 'SMALLINT' 
  WHEN MOD(coltype,256)=2 THEN 'INTEGER' 
  WHEN MOD(coltype,256)=3 THEN 'FLOAT' 
  WHEN MOD(coltype,256)=4 THEN 'SMALLFLOAT' 
  WHEN MOD(coltype,256)=5 THEN 'DECIMAL' 
  WHEN MOD(coltype,256)=6 THEN 'SERIAL' 
  WHEN MOD(coltype,256)=7 THEN 'DATE' 
  WHEN MOD(coltype,256)=8 THEN 'MONEY' 
  WHEN MOD(coltype,256)=9 THEN 'NULL' 
  WHEN MOD(coltype,256)=10 THEN 'DATETIME' 
  WHEN MOD(coltype,256)=11 THEN 'BYTE' 
  WHEN MOD(coltype,256)=12 THEN 'TEXT' 
  WHEN MOD(coltype,256)=13 THEN 'VARCHAR' 
  WHEN MOD(coltype,256)=14 THEN 'INTERVAL' 
  WHEN MOD(coltype,256)=15 THEN 'NCHAR' 
  WHEN MOD(coltype,256)=16 THEN 'NVARCHAR' 
  WHEN MOD(coltype,256)=17 THEN 'INT8' 
  WHEN MOD(coltype,256)=18 THEN 'SERIAL8' 
  WHEN MOD(coltype,256)=19 THEN 'SET' 
  WHEN MOD(coltype,256)=20 THEN 'MULTISET' 
  WHEN MOD(coltype,256)=21 THEN 'LIST' 
  WHEN MOD(coltype,256)=22 THEN 'ROW (unnamed)' 
  WHEN MOD(coltype,256)=23 THEN 'COLLECTION' 
  WHEN MOD(coltype,256)=40 THEN 'LVARCHAR fixed-length opaque types' 
  WHEN MOD(coltype,256)=41 THEN 'BLOB, BOOLEAN, CLOB variable-length opaque types' 
  WHEN MOD(coltype,256)=43 THEN 'LVARCHAR (client-side only)' 
  WHEN MOD(coltype,256)=45 THEN 'BOOLEAN' 
  WHEN MOD(coltype,256)=52 THEN 'BIGINT' 
  WHEN MOD(coltype,256)=53 THEN 'BIGSERIAL' 
  WHEN MOD(coltype,256)=2061 THEN 'IDSSECURITYLABEL'
  WHEN MOD(coltype,256)=4118 THEN 'ROW (named)' 
  ELSE TO_CHAR(coltype)
END                     AS Datatype
,c.collength            AS DatatypeLength
,CASE WHEN NVL(
(
    SELECT MAX(i.idxname)
    FROM "informix".sysconstraints cn inner join "informix".sysindexes i 
        on cn.idxname = i.idxname
        and cn.tabid = i.tabid
    where cn.constrtype = 'P'
        AND cn.tabid = t.tabid
        AND
        (c.colno = i.part1
        OR c.colno = i.part2
        OR c.colno = i.part3
        OR c.colno = i.part4
        OR c.colno = i.part5
        OR c.colno = i.part6
        OR c.colno = i.part7
        OR c.colno = i.part8
        OR c.colno = i.part9
        OR c.colno = i.part10
        OR c.colno = i.part11
        OR c.colno = i.part12
        OR c.colno = i.part13
        OR c.colno = i.part14
        OR c.colno = i.part15
        OR c.colno = i.part16
        )
),'') = '' THEN 0 ELSE 1 END    AS PK
FROM "informix".systables  AS t  JOIN "informix".syscolumns AS c 
    ON t.tabid = c.tabid
WHERE t.tabtype = 'T'
    AND t.tabid >= 100
    --AND t.tabname = 'resource'
ORDER BY t.tabname, c.colno;

Answer 4

Используйте таблицу syscolumns. Такая информация описана в Руководстве IBM Informix по SQL.

Я сделал простые утилиты Python, которые показывают информацию о схеме для Informix, Oracle и PostgreSQL. Они полезны, если вам нужно сравнить базы данных.