Skip to main content

%Library.SQLCatalog

class %Library.SQLCatalog extends %Library.RegisteredObject

SQL Catalog Queries

Method Inventory

Methods

classmethod GetCachedQueryTableCount(tablename As %String) as %Integer
Given a tablename, return a count of Cached Queries currently defined against the table.
classmethod SQLClassname(qh As %Library.SQLProcContext, table As %String(MAXLEN=257)) as %Library.String [ SQLProc = SQLCatalog_SQLClassname ]
Projected as the stored procedure: SQLCatalog_SQLClassname
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLClassname Procedure
	%SQLCatalog_SQLClassname('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		If 'tablename' is not qualified, the default schema will be added to the name.  
		Returns The name of the class which projected the table
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Queries

query SQLCODEList()
Selects SQLCODE As %SmallInt, DESCRIPTION As %String
Returns a list of all SQLCODE values and descriptions
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  SQLCODEList Procedure
	%SQLCatalog_SQLCODEList()
		Returns list of all SQLCODE values and descriptions
		ROWSPEC = SQLCODE:%SmallInt	- SQLCODE value
			  DESCRIPTION:%String	- SQLCODE Description
		Rows are returned in SQLCODE order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLCachedQueryInfo(stripOptions As %Boolean = 0)
Selects Routine As %String, Query As %String, CreateTime As %TimeStamp, Source As %Boolean, QueryType As %Integer, QueryTypeExt As %String, Cost As %Numeric
The SQLCachedQueryInfo query returns a list of cached queries.
Note that only the first 80 characters of a query are returned.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLCachedQueryInfo Procedure
	%SQLCatalog_SQLCachedQueryInfo(stripOptions=0)
		Takes one %Boolean type parameter which determines if the QUERY contains any /*#OPTIONS ... string added to xDBC statements.  Default is 0.
		Returns Cached Queries the current user is privileged to see
		ROWSPEC = ROUTINE:%String		- Name of the Cached Query's routine
			  QUERY:%String 		- Cached Query's SQL Text
			  CREATE_TIME:%TimeStamp 	- Date/Time of Cached Query creation
			  SOURCE:%Boolean 		- Cached Query source there?
			  QUERYTYPE:%Integer		- 6 or 7
			  QUERYTYPE_EXT:%String		- ODBC QUERY OR DYNAMIC QUERY
			  COST:%Numeric			- Generated Cost of the query
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLCachedQueryTable(%table As %String(MAXLEN=257), stripOptions As %Boolean = 0)
Selects Routine As %String(MAXLEN=128), Query As %String(MAXLEN=256), CreateTime As %TimeStamp, Source As %Boolean, QueryType As %Integer, QueryTypeExt As %String, Cost As %Numeric
The SQLCachedQueryTable query returns a list of cached queries for a given table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLCachedQueryTable Procedure
		%SQLCatalog_SQLCachedQueryTable('tablename',stripOptions=0)
		Takes two parameters:
			1) %String/VARCHAR type parameter which is the name of a table.
			2) %Boolean type parameter which determines if the QUERY contains any /*#OPTIONS ... string added to xDBC statements.  Default is 0.
		Returns Cached Queries on this table the current user is privileged to see
		ROWSPEC = ROUTINE:%String		- Name of the Cached Query's routine
			QUERY:%String				- Cached Query's SQL Text (First 80 characters)
			CREATE_TIME:%TimeStamp		- Date/Time of Cached Query creation
			SOURCE:%Boolean				- Cached Query source there?
			QUERYTYPE:%Integer			- 8 (Dynamic), 9 (xDBC), 10 (Dynamic), 11 (Embedded)
			QUERYTYPE_EXT:%String		- ODBC QUERY OR DYNAMIC QUERY
			COST:%Numeric				- Generated Cost of the query
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLChildTables(table As %String(MAXLEN=257))
Selects TABLE_NAME As %String(MAXLEN=128), COLLECTION_TYPE As %String(MAXLEN=12), ELEMENT_TYPE As %String(MAXLEN=10), CLASS_NAME As %String(MAXLEN=128), ORIGINATING_PROPERTY As %String(MAXLEN=128), INVERSE_RELATIONSHIP As %String(MAXLEN=128)
Get a List of all child tables of a given table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLChildTables Procedure
	%Library.SQLCatalog_SQLChildTables('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns any child tables this table has
		ROWSPEC = TABLE_NAME:%String      	- Name of the Child Table
		 	  COLLECTION_TYPE:%String 	- 'ARRAY' or 'LIST' if the table was projected as the result of a collection, else RELATIONSHIP
			  ELEMENT_TYPE:%String    	- If COLLECTION_TYPE is ARRAY or LIST, this will contain the type of elements in the collection 
							  'PERSISTENT', 'SERIAL', or 'DATATYPE'.  If COLLECTION_TYPE is RELATIONSHIP, this field is NULL
			  CLASS_NAME:%String      	- Name of the class which projected the table
			  ORIGINATING_PROPERTY:%String	- Name of the property which produced the projection of this Child Table.
			  INVERSE_RELATIONSHIP:%String	- If this child table is the result of a RELATIONSHIP, the relationship in the Parent table that 
							  is the inverse of the ORIGINATING_PROPERTY relationship.
							  NOTE:  If the child table is projected as a result of the deprecated IDENTIFIEDBY class parameter, 
							  this INVERSE_RELATIONSHIP will be NULL, even though the COLLECTION_TYPE is reported as RELATIONSHIP.
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLConstraints(table As %String(MAXLEN=257))
Selects CONSTRAINT_NAME As %Library.String(MAXLEN=64), CONSTRAINT_TYPE As %String(MAXLEN=15), CONSTRAINT_DATA As %String(MAXLEN=255), CONSTRAINT_OBJECT_NAME As %Library.String(MAXLEN=64)
Get a List of all constraints from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  SQLConstraints Procedure
	%SQLCatalog_SQLConstraints('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns all fields in the table:
		ROWSPEC = CONSTRAINT_NAME:%String        - Name of the constraint
			  TYPE:%String                   - Type of constraint
			  CONSTRAINT_DATE:%String        - Constraint info depends on type
			  CONSTRAINT_OBJECT_NAME:%String - Object name of the constraint
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLFields(table As %String(MAXLEN=257))
Selects FIELD_NAME As %String, DATATYPE As %String, COLUMN_NUMBER As %Integer, REQUIRED As %String, UNIQUE As %String, COLLATION_FUNCTION As %String, HIDDEN As %String, MAXLEN As %String, BLOB As %String, REFERENCE As %String, CONTAINER_FIELD As %Library.String, ODBC_DATATYPE_CODE As %Library.SmallInt, JDBC_DATATYPE_CODE As %Library.SmallInt, COLLECTION_TYPE As %Library.String, REF_ID_DATATYPE As %Library.String, ELEMENT_TYPE As %Library.String, REFERENCE_CLASS As %Library.String, INSERTABLE As %Library.String(MAXLEN=3), UPDATEABLE As %Library.String(MAXLEN=3), DEFAULT As %String(MAXLEN=255), ROWIDWITHODBCVALUES As %String(MAXLEN=3), SERIAL_FIELD As %String(MAXLEN=3), ROWID_FIELD As %SmallInt, ORIGINATING_PROPERTY As %Library.String(MAXLEN=40), MAXVAL As %Library.String, MINVAL As %Library.String
Get a List of all Fields from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLFields Procedure
	%SQLCatalog_SQLFields('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns all fields in the table:
		ROWSPEC = FIELD_NAME:%String			- Name of the field
			  DATATYPE:%String			- Field's datatype
			  COLUMN_NUMBER:%Integer		- Field's internal column number
			  REQUIRED:%String			- Is field required, 'Yes' or 'No'
			  UNIQUE:%String			- Is field unique, 'Yes', or 'No'
			  COLLATION_FUNCTION:%String		- ALPHAUP, UPPER, STRING, EXACT, PLUS or MINUS
			  HIDDEN:%String			- Is field hidden, 'Yes' or 'No'
			  MAXLEN:%String			- MAXLEN parameter of field (or NULL)
			  BLOB:%String				- Is field a BLOB, 'Yes' or 'No'
			  REFERENCE:%String			- Name of the referenced table or NULL if not a reference
			  CONTAINER_FIELD:%Library.String	- If this is a sub-field of a serial container, the name of the serial field
			  ODBC_DATATYPE_CODE			- ODBC Datatype code for this field
			  JDBC_DATATYPE_CODE			- ODBC Datatype code for this field
			  COLLECTION_TYPE			- Type of collection, 'LIST', 'ARRAY' or ""	
			  REF_ID_DATATYPE			- If this is a reference field, the DATATYPE for the ID of the referenced table
			  ELEMENT_TYPE				- If COLLECTION_TYPE is non-NULL, this will contain the type of elements in the collection
								  'PERSISTENT', 'SERIAL', or 'DATATYPE'.  If COLLECTION_TYPE is NULL, this field is NULL
			  REFERENCE_CLASS:%String		- Name of the referenced table's projecting class or NULL if not a reference
			  INSERTABLE:%String			- Can a value for the field be supplied in an SQL INSERT statement, 'Yes' or 'No'.
			  UPDATEABLE:%String			- Can a value for the field be supplied in an SQL UPDATE statement, 'Yes' or 'No'.
			  DEFAULT:%String			- Field's default value expression (if any)
			  ROWIDWITHODBCVALUES:%String		- If this is the ROWID field and the ROWID field is a datatype which 
								  has OdbcToLogical conversion, return Yes, otherwise No.  If this is not the
								  ROWID field, return NULL.
			  SERIAL_FIELD:%String			- Is this a serial field?  'Yes' or 'No'.
			  ROWID_FIELD:%SmallInt			- Is this a RowID field? 0 = No, 1 = Yes, 2 = A field the RowID is based on.
			  ORIGINATING_PROPERTY:%String		- Name of the class property which projected this field
			  MAXVAL:%Library.String		- MAXVAL parameter of field (or NULL)
			  MINVAL:%Library.String		- MINVAL parameter of field (or NULL)
		Rows are returned in table column order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLForeignKeys(table As %String(MAXLEN=257))
Selects FOREIGN_KEY_NAME As %String(MAXLEN=128), FOREIGN_KEY_FIELDS As %String, TARGET_TABLE As %String(MAXLEN=128), TARGET_CLASS As %String(MAXLEN=31), TARGET_KEY As %String(MAXLEN=128), TARGET_KEY_FIELDS As %String, UPDATE_ACTION As %String(MAXLEN=10), DELETE_ACTION As %String(MAXLEN=10), FOREIGN_KEY_TYPE As %String(MAXLEN=12)
Get a List of all Foreign Key constraints from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLForeignKeys Procedure
	%SQLCatalog_SQLForeignKeys('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns all ForeignKeys defined in the table:
		ROWSPEC = FOREIGN_KEY_NAME:%String   - Name of the FKey constraint
			  FOREIGN_KEY_FIELDS:%String - List of field in the FKey constraint
			  TARGET_TABLE:%String       - Name of the table referenced in the FKey constraint
			  TARGET_CLASS:%String       - Name of the class which projects the table referenced in the FKey constraint
			  TARGET_KEY:%String         - SQLName of the key in the referenced table
			  TARGET_KEY_FIELDS:%String  - List of fields in the reference table this FKey references
			  UPDATE_ACTION:%String      - Cascade, SetNull, NoAction, SetDefault
			  DELETE_ACTION:%String      - Cascade, SetNull, NoAction, SetDefault
			  FOREIGN_KEY_TYPE:%String   - RELATIONSHIP if the FKey was projected from a one-many relationship or FOREIGNKEY if the FKey
						       was projected from a foreign key definition.
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLIndices(table As %String(MAXLEN=257))
Selects INDEX_NAME As %String, FIELDS As %String, UNIQUE As %SmallInt, PAGES As %Integer
Get a List of all Indices from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLIndices Procedure
	%SQLCatalog_SQLIndices('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns all Indices in the table:
		ROWSPEC = INDEX_NAME:%String	- Name of the index
			  FIELDS:%String	- Indexed Fields
			  UNIQUE:%SmallInt	- 1 = UNIQUE Index, 0 NON_UNIQUE
			  PAGES:%Integer	- # of map pages
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLParentTable(table As %String(MAXLEN=257))
Selects PARENT_TABLE As %String(MAXLEN=128), PARENT_CLASS As %String(MAXLEN=128), PARENT_PROPERTY As %String(MAXLEN=128)
SQLParentTable Procedure
	%Library.SQLCatalog_SQLParentTable('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns any parent table this table has
		ROWSPEC = PARENT_TABLE:%String    - Name of the Parent Table
			  PARENT_CLASS:%String    - Name of the class that projected the parent table
			  PARENT_PROPERTY:%String - Name of the property in the parent class (if from a collection) that projected this table
		Rows are returned in no particular order
query SQLProcedureInfo(name As %String(MAXLEN=257))
Selects CLASS_NAME As %String, PROCEDURE_TYPE As %String, METHOD_OR_QUERY_NAME As %String, DESCRIPTION As %String, NUMBER_INPUT_PARAMS As %Integer, NUMBER_OUTPUT_PARAMS As %Integer, RETURN_VALUE As %Integer, NUMBER_OF_COLUMNS As %Integer
Gets detailed information about a single Stored Procedure
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  SQLProcedureInfo Procedure
	%SQLCatalog_SQLProcedureInfo('proc_name')
		Takes 1 %String/VARCHAR type parameter which is an exact Procedure name
		Returns information for the Procedure
		ROWSPEC = CLASS_NAME:%String		- Name of the class procedure method or query reside in
			  PROCEDURE_TYPE:%String		- Type of Stored procedure
			  METHOD_OR_QUERY_NAME:%String	- Name of the method or query the procedure is generated from
			  DESCRIPTION:%String		- Procedure's description
			  NUMBER_INPUT_PARAMS:%Integer	- Number of input parameters
			  NUMBER_OUTPUT_PARAMS:%Integer	- Number of output parameters
			  RETURN_VALUE:%Integer		- Has a return value?
			  NUMBER_OF_COLUMNS:%Integer	- Number of columns
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLProcedures(name As %String(MAXLEN=257))
Selects PROCEDURE_NAME As %String(MAXLEN=128), PROCEDURE_TYPE As %String(MAXLEN=10), CLASS_NAME As %String(MAXLEN=63), METHOD_OR_QUERY_NAME As %String(MAXLEN=32)
Gets a description of the stored procedures available in the catalog.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLProcedures Procedure
	%SQLCatalog_SQLProcedures('proc_name')
		Takes 1 %String/VARCHAR type parameter which is an optional wildcard
		match for the name of a Stored Procedure
		Returns all SQL Procedures which match the name given
		ROWSPEC = PROCEDURE_NAME:%String       - Name of the Stored procedure, LIKE Pattern allowed
			  PROCEDURE_TYPE:%String       - Type of Stored procedure
			  PROCEDURE_CLASS:%String      - Name of the class which projected the Procedure
			  METHOD_OR_QUERY_NAME:%String - Name of the method or query the procedure is generated from
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLRelationships(table As %String(MAXLEN=257), cardinality As %String = "")
Selects RELATIONSHIP_NAME As %String(MAXLEN=128), RELATIONSHIP_CLASS As %String(MAXLEN=31), RELATIONSHIP_TABLE As %String(MAXLEN=128), INVERSE As %String(MAXLEN=128), CARDINALITY As %String(MAXLEN=20), RELATIONSHIP_JDBC_TYPE As %Integer, RELATIONSHIP_JDBC_TYPENAME As %String(MAXLEN=20), INVERSE_JDBC_TYPE As %Integer, INVERSE_JDBC_TYPENAME As %String
Get a List of all relationship constraints from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLRelationships Procedure
	%SQLCatalog_SQLRelationships('tablename','cardinality')
		Parameters:
			  tablename   - %String - required - fully qualified table name 
			  cardinality - %String - optional - default is '*'
					Comma delimited list of cardinality values
					MANY, ONE, PARENT, CHILDREN
					'%', or '*' means any/all cardinality values
		Returns Relationships defined in the class (including inherited relationships) which projected the table.
		ROWSPEC = RELATIONSHIP_NAME:%String           - SQL Name of the Relationship Property
			  RELATIONSHIP_CLASS:%String          - Name of the class the relationship property references
			  RELATIONSHIP_TABLE:%String          - Name of the table the relationship property references
			  INVERSE:%String                     - SQL Name of the inverse property
			  CARDINALITY:%String                 - Relationship's cardinality
			  RELATIONSHIP_JDBC_TYPE:%Integer     - JDBC Datatype number of the relationship property
			  RELATIONSHIP_JDBC_TYPENAME:%String  - JDBC Datatype name of the relationship property
			  INVERSE_JDBC_TYPE:%Integer          - JDBC Datatype number of the inverse property
			  INVERSE_JDBC_TYPENAME:%String       - JDBC Datatype name of the inverse property
	Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLReservedWords()
Selects SQL_RESERVED_WORD As %String(MAXLEN=25)
Gets a list of SQL Reserved Words
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLReservedWords Procedure
	%SQLCatalog_SQLReservedWords()
		Returns a list of SQL Reserved words
		ROWSPEC = SQL_RESERVED_WORD:%String - Name of the SQL Reserved Word
		Rows are returned in Reserved Word order using UPPER collation
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLTableStatements(SchemaName As %String(MAXLEN=128), RelationName As %String(MAXLEN=128))
Selects SCHEMA As %String(MAXLEN=128), RELATION_NAME As %String(MAXLEN=128), PLAN_STATE As %String(MAXLEN=32), LOCATION As %String(MAXLEN=128), STATEMENT As %String(MAXLEN=16384), NATURAL_QUERY As %Boolean, COUNT As %Integer, AVERAGECOUNT As %Double, TOTAL As %Integer, AVERAGE As %Double, STDDEV As %Double, RUNTIMELOCATION As %String(MAXLEN=128), ROWCOUNT As %Integer, AVERAGEROWCOUNT As %Integer, USERNAME As %String(MAXLEN=30), CLIENTNAME As %String(MAXLEN=30), CLIENTIP As %String(MAXLEN=16), CLIENTAPP As %String(MAXLEN=24), CALLSTACK As %String(MAXLEN=256), COMMANDS As %Integer, AVERAGECOMMANDS As %Integer
Returns list of SQL statements against the given Table or View name
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLTableStatements Procedure
	%Library.SQLTableStatements('schema_name','relation_name')
		Takes 2 %String/VARCHAR type parameters which is the name of a schema and a table or view name.
		Returns a list of SQL statements against the table or view
		ROWSPEC = SCHEMA:%String(MAXLEN=128)	  - Schema name
			  RELATION_NAME:%String(MAXLEN=128)   - Table/View name
			  PLAN_STATE:%String(MAXLEN=32)	      - The state of the frozen plan for this statement: Frozen/Explicit, Unfrozen/Parallel or Unfrozen.
			  LOCATION:%String(MAXLEN=128)	      - Routine name the SQL code is located in
			  STATEMENT:%String(MAXLEN(16348)     - SQL Statement
              NATURAL_QUERY:%String               - True if this is a simple query where we will not record stats
              COUNT:%Integer                      - Number of times this query has been called
              AVERAGECOUNT:%Double                - Average number of times this query has been called (based on days since we first saw this)
           	  TOTAL:%Integer                      - Total time spent executing this query
              AVERAGE:%Double                     - Average time spent running query
              STDDEV:%Double                      - Standard deviation of query time
              RUNTIMELOCATION:%String(MAXLEN=128) - For UCQ runtimeLocation is the UCQ classname which is different from the containers name
              ROWCOUNT:%Integer                   - Total %ROWCOUNT from running this query
              AVERAGEROWCOUNT:%Integer            - Average row count per day (since we first saw this query)
              USERNAME:%String                    - $username that built query
              CLIENTNAME:%String                  - Client name that built query
              CLIENTIP:%String                    - Client IP that built query
              CLIENTAPP:%String                   - Client application name that built query
              CALLSTACK:%String                   - Call stack when query was built
              COMMANDS:%Integer                   - ObjectScript commands executed from running this query
              AVERAGECOMMANDS:%Integer            - Average ObjectScript commands executed from running this query per day
		Note:  Only DECLARE, SELECT, INSERT, UPDATE, and DELETE statements are returned.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLTables()
Selects RELATION_NAME As %String, TYPE As %String, OWNER As %String, LAST_COMPILED As %TimeStamp
Get a list of all Tables and Views.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLTables Procedure
	%SQLCatalog_SQLTables()
		Privileges are checked against pre-define $Username.
		Returns a list of tables and views defined in the current namespace.
		ROWSEPC = RELATION_NAME:%String	   - Name of the table or view
			  TYPE:%String 		   - 'TABLE' or 'VIEW'
			  OWNER:%String            - Owner of the table/view
			  LAST_COMPILED:%TimeStamp - Time of last compilation
		Rows are returned in order by RELATION_NAME
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLTriggers(table As %String(MAXLEN=257))
Selects TRIGGER_NAME As %String, TIME_EVENT As %String, ORDER As %Integer, CODE As %String
Get a List of all Triggers from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLTriggers Procedure
	%SQLCatalog_SQLTriggers('tablename')
		Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
		Returns all triggers in the table:
		ROWSPEC = TRIGGER_NAME:%String		- Name of the trigger
			  TIME_EVENT:%String		- Trigger's TIME and EVENT
			  ORDER:%SmallInt		- Trigger's order with the TIME_EVENT
			  CODE:%String			- Trigger code
		Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLViewDependsOn(pView As %String(MAXLEN=257))
Selects SCHEMA As %String, TABLE_NAME As %String
Return list of tables the view depends on
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLViewDependsOn Procedure
	%Library.SQLViewDependsOn('viewname')
		Takes 1 %String/VARCHAR type parameter which is the name of a View.
		Returns a list of tables this view depends on
		ROWSPEC = SCHEMA:%String	- Schema name
			  TABLE_NAME:%String	- Table name
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLViewFields(view As %String(MAXLEN=257))
Selects FIELD_NAME As %String, DATATYPE As %String, MAXLEN As %String, BLOB As %String, MAXVAL As %Library.String, MINVAL As %Library.String
Get a List of all Fields from a view.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLViewFields Procedure
	%SQLCatalog_SQLViewFields('viewname')
		Takes 1 %String/VARCHAR type parameter which is the name of a View.
		Returns all fields in the table:
		ROWSPEC = FIELD_NAME:%String		- Name of the field
			  DATATYPE:%String		- Field's datatype
			  MAXLEN:%String		- MAXLEN parameter of field (or NULL)
			  BLOB:%String			- Is field a BLOB, 'Yes' or 'No'
			  MAXVAL:%Library.String	- MAXVAL parameter of field (or NULL)
			  MINVAL:%Library.String	- MINVAL parameter of field (or NULL)
		Rows are returned view column order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
query SQLViewInfo(view As %String(MAXLEN=257))
Selects VIEW_QUERY As %String
Get full View definition.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLViewInfo Procedure
	%SQLCatalog_SQLViewInfo('viewname')
		Takes 1 %String/VARCHAR type parameter which is the name of a View.
		Returns all fields in the table:
		ROWSPEC = VIEW_QUERY:%String	- Query Text of the View
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Inherited Members

Inherited Methods

FeedbackOpens in a new tab