Maximo: System Metadata

Example: Querying for Maximo Meta Data

/*
** Configuration Tables
*/
 
SELECT * FROM maxattributecfg WHERE attributename = 'CHANGED' AND persistent = 1;
SELECT * FROM maxattributecfg WHERE changed != 'N';
SELECT * FROM maxobjectcfg WHERE changed != 'N';
SELECT * FROM maxtablecfg; -- changed by the owning object
SELECT * FROM maxsysindexes WHERE changed != 'N'; -- Note this is both a staging and the main table
SELECT * FROM maxsyskeys WHERE changed != 'N';
SELECT * FROM maxviewcfg;
SELECT * FROM maxviewcolumncfg WHERE changed != 'N';
 
/*
** Meta-Data
*/
 
-- table structure
SELECT * FROM maxattribute; -- objectname and attributename are the keys
SELECT * FROM maxobject; -- objectname is the key, also defines the system and where the object resides amongst other things (System, Org, OrgSite, Site)
SELECT * FROM maxtable ORDER BY tablename; -- tablename is the key, has the uniquecolumnname for the table and the storagepartition
SELECT * FROM maxsysindexes; -- Name is the key, Note this is both a staging and the main table
SELECT * FROM maxsyskeys; -- IXNAME = Name in maxsysindexes and is the key - this is where the key's are listed in colseq and ordering
SELECT * FROM maxview; -- lists the view selections, where, viewname etc...
SELECT * FROM maxviewcolumn; -- column list for the views
 
-- variables and parameters
 
SELECT * FROM maxvars;
SELECT * FROM maxvartype;
SELECT * FROM sigoption; -- Signature security options by app/option name and visible or not
SELECT * FROM maxprop;  -- System Properties (with defaults)
SELECT * FROM maxpropvalue;  -- System Properties (as configured)
SELECT * FROM maxpresentation; -- screens
 
SELECT * FROM sctemplate; -- Start Center templates
 
SELECT * FROM pricalc; -- priority calculation settings
SELECT * FROM maxmenu; -- application menu positions
SELECT * FROM maxapps; -- list of applications
SELECT * FROM maxlookupmap; -- interrelationships not defined in the relationships table
 
-- Dummy tables (has one of every type of attribute, one row only)
SELECT * FROM dummy_table;
SELECT * FROM mea_dummy_table;
 
 
 
 
 
SELECT *
	FROM maxtable
	WHERE tablename = 'ASSET'
	ORDER BY tablename
;
export excelfile "C:\logs\metadata\maxtable_asset.xls"
/
 
SELECT *
	FROM maxobjectcfg
	WHERE objectname IN (SELECT tablename FROM maxtable)
	AND objectname = ('ASSET')
	ORDER BY objectname
;
export excelfile "C:\logs\metadata\maxobjectcfg_asset.xls"
/
 
SELECT *
	FROM maxattributecfg
	WHERE objectname IN (SELECT tablename FROM maxtable)
	AND objectname = ('ASSET')
	ORDER BY objectname, attributename
;
export excelfile "C:\logs\metadata\maxattributecfg_asset.xls"
/
 
SELECT *
	FROM all_tables
	WHERE owner = 'MAXIMO'
	AND TABLE_NAME = 'ASSET'
	ORDER BY TABLE_NAME
;
export excelfile "C:\logs\metadata\all_tables_asset.xls"
/
 
SELECT *
	FROM all_tab_columns
	WHERE owner = 'MAXIMO'
	AND TABLE_NAME = 'ASSET'
	ORDER BY TABLE_NAME, column_name
;
export excelfile "C:\logs\metadata\all_tab_columns_asset.xls"
/
 
SELECT *
	FROM all_indexes
	WHERE owner = 'MAXIMO'
	AND TABLE_NAME = 'ASSET'
	ORDER BY TABLE_NAME, index_name
;
export excelfile "C:\logs\metadata\all_indexes_asset.xls"
/
 
SELECT *
	FROM all_ind_columns
	WHERE index_name IN (SELECT index_name FROM all_indexes	WHERE owner = 'MAXIMO')
	AND TABLE_NAME = 'ASSET'
	ORDER BY TABLE_NAME, index_name, column_position
;
export excelfile "C:\logs\metadata\all_ind_columns_asset.xls"
/
 
SELECT *
	FROM maxsysindexes
	WHERE tbname = 'ASSET'
	ORDER BY name
;
export excelfile "C:\logs\metadata\maxsysindexes_asset.xls"
/
 
SELECT *
	FROM maxsyskeys
	WHERE ixname IN (SELECT name FROM maxsysindexes WHERE tbname = 'ASSET')
	ORDER BY ixname, colseq
;
export excelfile "C:\logs\metadata\maxsysindexes_asset.xls"
/
 
/*
** Alt-I data first custom fields, then all
**
*/
 
SELECT objectname,attributename,columnname,domainid,LENGTH,maxtype,required,persistent,primarykeycolseq,remarks,sameasattribute,sameasobject,title
	FROM maxattributecfg
	WHERE objectname IN (SELECT tablename FROM maxtable)
	AND userdefined = 1 --objectname = ('ASSET')
	ORDER BY objectname, attributename
;
export excelfile "C:\logs\metadata\maxattributecfg_alt_i_userdefined_true.xls"
/
 
SELECT objectname,attributename,columnname,domainid,LENGTH,maxtype,required,persistent,primarykeycolseq,remarks,sameasattribute,sameasobject,title
	FROM maxattributecfg
	WHERE objectname IN (SELECT tablename FROM maxtable)
--	and userdefined = 1 --objectname = ('ASSET')
	ORDER BY objectname, attributename
;
export excelfile "C:\logs\metadata\maxattributecfg_alt_i_all_attributes_selected_fields.xls"
/