Extraction Queries for Teradata¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Schema¶
Make sure that your query has a column labeled as SCHEMA in the SELECT statement.
SELECT
DatabaseName AS SCHEMA
FROM
DBC.DBASE
WHERE lower(DatabaseName) NOT IN ('''')
AND lower(DatabaseName) NOT IN
('sysspatial','td_sysfnlib','syslib','tdwstage','twm_source','default',
'tdpuser','public','locklogshredder','sys_calendar','tpch','extuser',
'sysudtlib','td_sysxml','sysadmin','twm_md','all','dbcmngr','viewpoint',
'console','td_sysgpl','information_schema','dbc','sysjdbc','systemfe',
'external_ap','sqlj','tdstats','tdwm','tdqcd','crashdumps','sysuif',
'sysbar','td_server_db');
Table¶
Make sure that your query has columns labeled as SCHEMA, TABLE_NAME, TABLE_TYPE, and REMARKS in the SELECT statement.
SELECT
tb.DataBaseName AS SCHEMA,
tb.TableName AS TABLE_NAME,
tb.CommentString AS REMARKS,
tb.CreatorName AS TABLE_OWNER,
tb.RequestTxtOverFlow,
CASE
WHEN tb.RequestTxtOverFlow IS NULL
THEN tb.RequestText
ELSE tt.RequestText
END AS VIEW_CREATE_STATEMENT,
CASE
WHEN tb.TableKind IN ('T', 'O')
THEN 'TABLE'
ELSE 'VIEW'
END AS TABLE_TYPE
FROM dbc.TablesV tb
LEFT OUTER JOIN
(SELECT *
FROM
(SELECT
DataBaseName,
TableName,
2 * LineNo - 1 AS LineNo,
substr(RequestText, 1, 16000) AS RequestText
FROM dbc.TableTextV text_split_1
WHERE
LOWER(text_split_1.DataBaseName) NOT IN ('''')
AND LOWER(text_split_1.DataBaseName) NOT IN ('sysspatial',
'td_sysfnlib','syslib','tdwstage','twm_source','default',
'tdpuser','public','locklogshredder','sys_calendar','tpch',
'extuser','sysudtlib','td_sysxml','sysadmin','twm_md','all',
'dbcmngr','viewpoint','console','td_sysgpl','information_schema',
'dbc','sysjdbc','systemfe','external_ap','sqlj','tdstats','tdwm',
'tdqcd','crashdumps','sysuif','sysbar','td_server_db')
AND text_split_1.TableKind IN ('O', 'T', 'V')
UNION ALL
SELECT
DataBaseName,
TableName,
2 * LineNo AS LineNo,
substr(RequestText, 16001) AS RequestText
FROM dbc.TableTextV text_split_2
WHERE
LOWER(text_split_2.DataBaseName) NOT IN ('''')
AND LOWER(text_split_2.DataBaseName) NOT IN ('sysspatial',
'td_sysfnlib','syslib','tdwstage','twm_source','default',
'tdpuser','public','locklogshredder','sys_calendar','tpch',
'extuser','sysudtlib','td_sysxml','sysadmin','twm_md','all',
'dbcmngr','viewpoint','console','td_sysgpl','information_schema',
'dbc','sysjdbc','systemfe','external_ap','sqlj','tdstats','tdwm',
'tdqcd','crashdumps','sysuif','sysbar','td_server_db')
AND text_split_2.TableKind IN ('O', 'T', 'V')
AND CHARACTER_LENGTH(RequestText) > 16000) tabletextv_split) tt
ON tb.DataBaseName = tt.DataBaseName
AND tb.TableName = tt.TableName
LEFT OUTER JOIN
(SELECT
DataBaseName,
TableName,
sum(CurrentPerm) AS CurrentPerm,
sum(PeakPerm) AS PeakPerm
FROM dbc.TableSizeV
GROUP BY
DataBaseName,
TableName) ts
ON tb.DataBaseName = ts.DataBaseName
AND tb.TableName = ts.TableName
WHERE LOWER(tb.DataBaseName) NOT IN ('''')
AND LOWER(tb.DataBaseName) NOT IN ('sysspatial','td_sysfnlib',
'syslib','tdwstage','twm_source','default','tdpuser','public',
'locklogshredder','sys_calendar','tpch','extuser','sysudtlib',
'td_sysxml','sysadmin','twm_md','all','dbcmngr','viewpoint',
'console','td_sysgpl','information_schema','dbc','sysjdbc',
'systemfe','external_ap','sqlj','tdstats','tdwm','tdqcd',
'crashdumps','sysuif','sysbar','td_server_db')
AND tb.TableKind IN ('O', 'T')
ORDER BY
TABLE_TYPE,
tb.DataBaseName,
tb.TableName,
tt.LineNo;
View¶
Make sure that your query has columns labeled as SCHEMA, VIEW_NAME, VIEW_CREATE_STATEMENT, ‘VIEW’ AS VIEW_TYPE, and REMARKS in the SELECT statement.
SELECT
tb.DataBaseName AS SCHEMA,
tb.TableName AS VIEW_NAME,
tb.CommentString AS REMARKS,
tb.CreatorName AS TABLE_OWNER,
CASE
WHEN tb.RequestTxtOverFlow IS NULL THEN tb.RequestText
ELSE ttv.RequestText
END AS VIEW_CREATE_STATEMENT,
ttv.LineNo,
tb.RequestTxtOverFlow,
'VIEW' AS VIEW_TYPE
FROM
dbc.TablesV tb
LEFT JOIN
dbc.TableTextV ttv
ON
tb.DataBaseName = ttv.DataBaseName
AND tb.TableName = ttv.TableName
WHERE
tb.TableKind = 'V'
AND LOWER(tb.DataBaseName) NOT IN ('''')
AND LOWER(tb.DataBaseName) NOT IN ('sysspatial','td_sysfnlib',
'syslib','tdwstage','twm_source','default','tdpuser','public',
'locklogshredder','sys_calendar','tpch','extuser','sysudtlib',
'td_sysxml','sysadmin','twm_md','all','dbcmngr','viewpoint',
'console','td_sysgpl','information_schema','dbc','sysjdbc',
'systemfe','external_ap','sqlj','tdstats','tdwm','tdqcd',
'crashdumps','sysuif','sysbar','td_server_db')
ORDER BY
ttv.DataBaseName,
ttv.TableName,
ttv.LineNo DESC;
Column¶
Make sure that your query has columns labeled as SCHEMA, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, REMARKS, and COLUMN_DEFAULT in the SELECT statement.
SELECT CAST (NULL AS varchar(30)) AS table_cat,
TRIM(t.databasename) AS SCHEMA,
TRIM(t.tablename) AS TABLE_NAME,
TRIM(c.columnname) AS COLUMN_NAME,
c.decimaltotaldigits AS decimal_size,
c.decimalfractionaldigits AS fraction_size,
c.chartype AS char_type,
CASE
WHEN c.decimaltotaldigits > 0
THEN LOWER(CAST ((CASE TRIM(c.columntype)
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BO' THEN 'BLOB-TERADATA'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CO' THEN 'CLOB'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I' THEN 'INTEGER'
WHEN 'I8' THEN 'BIGINT'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'N' THEN 'NUMBER'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN 'PT' THEN 'PERIOD(TIME)'
WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE-TERADATA'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'A1' THEN 'ARRAY'
WHEN 'AN' THEN 'MULTI-DIMENSIONAL ARRAY'
WHEN 'UT' THEN 'UDT'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'XM' THEN 'XML'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
ELSE 'NA'
END) AS varchar(500)))||'(' || CAST(c.decimaltotaldigits AS varchar(500))||CAST(CASE
WHEN c.decimalfractionaldigits
IS NOT NULL
AND c.decimalfractionaldigits > 0
THEN ','||CAST(c.decimalfractionaldigits AS varchar(500))
ELSE ''
END AS varchar(500))||')'
WHEN c.chartype = 1
OR c.chartype = 2
THEN LOWER(CAST ((CASE TRIM(c.columntype)
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BO' THEN 'BLOB-TERADATA'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CO' THEN 'CLOB'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I' THEN 'INTEGER'
WHEN 'I8' THEN 'BIGINT'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'N' THEN 'NUMBER'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN 'PT' THEN 'PERIOD(TIME)'
WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE-TERADATA'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'A1' THEN 'ARRAY'
WHEN 'AN' THEN 'MULTI-DIMENSIONAL ARRAY'
WHEN 'UT' THEN 'UDT'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'XM' THEN 'XML'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
ELSE 'NA'
END) AS varchar(500)))||'('||CAST(OREPLACE(CAST(c.columnlength AS varchar(500)), ',', '') AS varchar(500))||')'
ELSE LOWER(CAST ((CASE trim(c.columntype)
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BO' THEN 'BLOB-TERADATA'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CO' THEN 'CLOB'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I' THEN 'INTEGER'
WHEN 'I8' THEN 'BIGINT'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'N' THEN 'NUMBER'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN 'PT' THEN 'PERIOD(TIME)'
WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE-TERADATA'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'A1' THEN 'ARRAY'
WHEN 'AN' THEN 'MULTI-DIMENSIONAL ARRAY'
WHEN 'UT' THEN 'UDT'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'XM' THEN 'XML'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
ELSE 'NA'
END) AS varchar(500)))
END AS type_name,
CAST ((CASE TRIM(c.columntype)
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BO' THEN 'BLOB-TERADATA'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CO' THEN 'CLOB'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I' THEN 'INTEGER'
WHEN 'I8' THEN 'BIGINT'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'N' THEN 'NUMBER'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN 'PT' THEN 'PERIOD(TIME)'
WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE-TERADATA'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'A1' THEN 'ARRAY'
WHEN 'AN' THEN 'MULTI-DIMENSIONAL ARRAY'
WHEN 'UT' THEN 'UDT'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'XM' THEN 'XML'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
ELSE 'NA'
END) AS varchar(500)) AS data_type,
c.columnlength AS LENGTH,
TRIM(c.commentstring) AS REMARKS,
TRIM(c.defaultvalue) AS column_default,
CAST ((row_number () OVER (PARTITION BY SCHEMA, TABLE_NAME
ORDER BY c.columnid)) AS integer) AS ordinal_position,
TRIM((CASE c.nullable
WHEN 'Y' THEN 'YES'
WHEN 'N' THEN 'NO'
ELSE ''
END)) AS is_nullable
FROM dbc.tablesv t
JOIN dbc.columnsv c ON t.databasename = c.databasename
AND t.tablename = c.tablename
WHERE LOWER(TRIM(t.databasename)) IN ('''')
AND LOWER(TRIM(t.databasename)) NOT IN ('sysspatial', 'td_sysfnlib', 'syslib',
'tdwstage', 'twm_source', 'default', 'tdpuser',
'public', 'locklogshredder', 'sys_calendar',
'tpch', 'extuser', 'sysudtlib', 'td_sysxml',
'sysadmin', 'twm_md', 'all', 'dbcmngr',
'viewpoint', 'console', 'td_sysgpl',
'information_schema', 'dbc', 'sysjdbc',
'systemfe', 'external_ap', 'sqlj', 'tdstats',
'tdwm', 'tdqcd', 'crashdumps', 'sysuif',
'sysbar', 'td_server_db')
AND TRIM(t.tablekind) IN ('O', 'T', 'V')
ORDER BY
TRIM(t.DatabaseName),
TRIM(t.TableName)
Primary Key¶
Make sure that your query has columns labeled as SCHEMA, TABLE_NAME, and COLUMN_NAME in the SELECT list.
SELECT
'' AS TABLE_CAT,
DATABASENAME AS SCHEMA,
TABLENAME AS TABLE_NAME,
COLUMNNAME AS COLUMN_NAME,
CAST (COLUMNPOSITION AS SMALLINT) AS KEY_SEQ,
INDEXNAME AS PK_NAME,
INDEXNUMBER AS INDEX_NUMBER
FROM dbc.indicesV
WHERE LOWER(TRIM(DATABASENAME)) NOT IN ('''')
AND LOWER(TRIM(DATABASENAME)) NOT IN
('sysspatial','td_sysfnlib','syslib','tdwstage','twm_source',
'default','tdpuser','public','locklogshredder','sys_calendar',
'tpch','extuser','sysudtlib','td_sysxml','sysadmin','twm_md','all',
'dbcmngr','viewpoint','console','td_sysgpl','information_schema',
'dbc','sysjdbc','systemfe','external_ap','sqlj','tdstats','tdwm','tdqcd',
'crashdumps','sysuif','sysbar','td_server_db')
AND TRIM(indextype) IN ('K', 'P', 'Q')
AND TRIM(uniqueflag) = 'Y'
ORDER BY
DATABASENAME,
TABLENAME,
INDEXNUMBER,
COLUMNPOSITION
Foreign Key¶
Make sure that your query has columns labeled as PK_CATALOG, PK_SCHEMA, PK_TABLE, PK_COLUMN, FK_CATALOG, FK_SCHEMA, FK_TABLE, and FK_COLUMN in the SELECT list.
SELECT
CAST (NULL AS varchar(30)) AS PK_CATALOG,
TRIM(ParentDB) AS PK_SCHEMA,
TRIM(ParentTable) AS PK_TABLE,
TRIM(ParentKeyColumn) AS PK_COLUMN,
cast (null AS varchar(30)) AS FK_CATALOG,
TRIM(ChildDB) AS FK_SCHEMA,
TRIM(ChildTable) AS FK_TABLE,
TRIM(ChildKeyColumn) AS FK_COLUMN,
CAST (NULL AS smallint) AS KEY_SEQ,
CAST (3 AS smallint) AS UPDATE_RULE,
CAST (3 AS smallint) AS DELETE_RULE,
TRIM(IndexName) AS FK_NAME,
CAST (NULL AS varchar(30)) AS PK_NAME,
CAST (NULL AS smallint) AS DEFERRABILITY
FROM DBC.All_RI_ParentsV
WHERE LOWER(TRIM(ChildDB)) NOT IN ('''')
AND LOWER(TRIM(ChildDB)) NOT IN
('sysspatial','td_sysfnlib','syslib','tdwstage','twm_source',
'default','tdpuser','public','locklogshredder','sys_calendar',
'tpch','extuser','sysudtlib','td_sysxml','sysadmin','twm_md',
'all','dbcmngr','viewpoint','console','td_sysgpl','information_schema',
'dbc','sysjdbc','systemfe','external_ap','sqlj','tdstats','tdwm','tdqcd',
'crashdumps','sysuif','sysbar','td_server_db')
ORDER BY
FK_CATALOG,
FK_SCHEMA,
FK_TABLE,
FK_NAME,
FK_COLUMN
Index¶
Make sure that your query has columns labeled as SCHEMA, TABLE_NAME, and FUNCTION_NAME in the select list.
SELECT
DISTINCT TRIM(c.databasename) AS SCHEMA,
TRIM(c.tablename) AS TABLE_NAME,
TRIM(c.columnname) AS COLUMN_NAME,
TRIM(c.indexname) AS INDEX_NAME,
TRIM(c.indextype) AS INDEX_TYPE,
c.indexnumber AS INDEX_NUMBER,
c.columnposition AS COLUMN_POSITION,
c.uniqueFlag AS UNIQUE_FLAG
FROM dbc.indicesV c
WHERE LOWER(TRIM(c.databasename)) NOT IN ('''')
AND LOWER(TRIM(c.databasename)) NOT IN
('sysspatial','td_sysfnlib','syslib','tdwstage','twm_source','default',
'tdpuser','public','locklogshredder','sys_calendar','tpch','extuser',
'sysudtlib','td_sysxml','sysadmin','twm_md','all','dbcmngr','viewpoint',
'console','td_sysgpl','information_schema','dbc','sysjdbc','systemfe',
'external_ap','sqlj','tdstats','tdwm','tdqcd','crashdumps','sysuif','sysbar','td_server_db')
AND TRIM(c.indextype) <> 'Q'
ORDER BY
c.databasename,
c.tablename,
c.indexname,
c.indexnumber,
c.columnposition;
Function¶
Make sure that your query has columns labeled as SCHEMA, FUNCTION_CAT, and FUNCTION_NAME in the select list.
SELECT
TRIM(fn.TableName) AS FUNCTION_NAME,
TRIM(fn.TableKind) AS ARG_TYPE,
NULL AS FUNCTION_DEFINITION,
TRIM(fn.DataBaseName) AS SCHEMA,
TRIM(m.requesttext) AS REMARKS,
f.fieldid,
TRIM(f.fieldname) AS ARG_NAME
FROM dbc.tvm m, dbc.tvfields f, dbc.tablesV fn
WHERE LOWER(TRIM(fn.databasename)) NOT IN ('''')
AND LOWER(TRIM(fn.databasename)) NOT IN
('sysspatial','td_sysfnlib','syslib','tdwstage','twm_source',
'default','tdpuser','public','locklogshredder','sys_calendar',
'tpch','extuser','sysudtlib','td_sysxml','sysadmin','twm_md','all',
'dbcmngr','viewpoint','console','td_sysgpl','information_schema',
'dbc','sysjdbc','systemfe','external_ap','sqlj','tdstats','tdwm',
'tdqcd','crashdumps','sysuif','sysbar','td_server_db')
AND fn.TableName = m.tvmname
AND m.tvmid = f.tableid
AND (fn.TableKind ='F' OR fn.TableKind ='P')
ORDER BY
fn.DataBaseName,
fn.TableName,
f.fieldid;
Function Definition¶
Make sure that your query has columns labeled as SCHEMA, FUNCTION_CAT, FUNCTION_NAME, ARG_NAME, TYPE_NAME, ARG_TYPE, and ARG_DEF in the select list.
SELECT
TRIM(fn.TableName) AS specificname,
TRIM(fn.TableName) AS FUNCTION_NAME,
TRIM(fn.TableKind) AS ARG_TYPE,
TRIM(fn.DataBaseName) AS SCHEMA,
TRIM(m.requesttext) AS REMARKS,
f.fieldid,
TRIM(f.fieldname) AS ARG_NAME,
NULL AS FUNCTION_DEFINITION,
f.spparametertype,
CAST
(
(CASE f.FieldType
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'A1' THEN TRIM(TRAILING FROM f.UDTName)
WHEN 'AN' THEN TRIM(TRAILING FROM f.UDTName)
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BO' THEN 'BLOB'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CO' THEN 'CLOB'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT' WHEN 'I2' THEN 'SMALLINT'
WHEN 'I' THEN 'INTEGER'
WHEN 'I8' THEN 'BIGINT'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'N' THEN 'NUMBER'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN 'PT' THEN 'PERIOD(TIME)'
WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'XM' THEN 'XML'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
WHEN 'UT' THEN TRIM(TRAILING FROM f.UDTName)
ELSE TRIM (f.FieldType)
END) AS VARCHAR(500)) AS TYPE_NAME
FROM dbc.tvm m, dbc.tvfields f, dbc.tablesV fn
WHERE LOWER(TRIM(fn.databasename)) NOT IN ('''')
AND LOWER(TRIM(fn.databasename)) NOT IN
('sysspatial','td_sysfnlib','syslib','tdwstage','twm_source','default',
'tdpuser','public','locklogshredder','sys_calendar','tpch','extuser',
'sysudtlib','td_sysxml','sysadmin','twm_md','all','dbcmngr','viewpoint',
'console','td_sysgpl','information_schema','dbc','sysjdbc','systemfe',
'external_ap','sqlj','tdstats','tdwm','tdqcd','crashdumps','sysuif','sysbar','td_server_db')
AND fn.TableName = m.tvmname
AND m.tvmid = f.tableid
AND (fn.TableKind ='F' OR fn.TableKind ='P')
ORDER BY
fn.DataBaseName,
fn.TableName,
f.fieldid;