Maximo: System Metadata
From ExxtremeWiki
Example: Querying for Maximo Meta Data[edit]
/* ** 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" /