SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

6 Querying SQL/MX Metadata
Use the commands described in this section to obtain the information you need to manage a
database. This section addresses these topics:
Metadata overview
• “Commands for Displaying Information” (page 106)
• “SQL/MX Metadata Tables” (page 107)
• “Understanding ANSI External and Internal Names” (page 111)
Querying system tables
• “Displaying System Schema Information” (page 112)
• “Displaying Catalog Information” (page 118)
• “Displaying Schema Information” (page 121)
• “Displaying Users With Catalog and Schema Creation Rights” (page 124)
Querying objects and other tables
• “Displaying Table Information” (page 125)
• “Displaying Information About Views” (page 129)
• “Displaying Information About SQL/MP Aliases” (page 130)
• “Displaying Information About SPJs” (page 131)
• “Displaying Index Information” (page 131)
• “Displaying Partition Information” (page 135)
• “Displaying Constraint Information” (page 136)
• “Displaying Column Information” (page 138)
• “Displaying Information About Privileges” (page 144)
• “Displaying Information About Sequence Generators” (page 147)
Obtaining other information
• “Displaying Object Integrity and Consistency” (page 147)
• “Displaying Version Numbers” (page 147)
NOTE: For some metadata information, NSM/web provides an alternative to writing complex
queries. The NSM/web graphical user interface automatically displays all the catalogs, schemas,
and tables on a local node. For more information, see the NSM/web Installation Guide or the
NSM/web Online Help.
NonStop SQL/MX stores information about object structures in file labels, resource forks, and
metadata, as follows:
Contain file structure information and security settings. Use the MXCI SHOWLABEL command
to obtain file-label information. Most of the information in file labels is the same as the
metadata.
File labels
Are label extensions that contain SQL/MX information that is not available in the label or
is too large to fit into a standard file label. The resource fork name is derived from the
Resource forks
SQL/MX object's Guardian file name. Use the MXCI SHOWLABEL command to obtain
the Guardian file name. Most of the information in resource forks is the same as the
metadata.
Contain a description of all objects in a database. This information includes the object
type (for example, table or view) and object attributes (for example, partitioning scheme
Metadata
and redefinition time), and ownership information. To obtain metadata information, use
one or both of these methods:
• Use the MXCI SHOWDDL command.
• Use SELECT statements, as described in this section, to query the metadata.
105