Inheritance diagram for Schema:

Collaboration diagram for Schema:

Static Public Member Functions
_.Library.Status	CreateLinkedProcedure (_.Library.String dsn, _.Library.String externalSchema, _.Library.String externalProcedure, _.Library.String localPackage, _.Library.String localClass, _.Library.String localMethod, _.Library.String localSqlName, _.Library.String description)
	Create a linked process. More...

_.Library.Status	CreateLinkedTable (_.Library.String dsn, _.Library.String externalSchema, _.Library.String externalTable, _.Library.String primaryKeys, _.Library.String localClass, _.Library.String localTable, _.Library.String columnMap)
	Create a linked table. More...

_.Library.String	Default ()
	Return the default schema name for the current process in the current namespace. More...

_.Library.Status	DropTable (_.Library.String tablename, _.Library.Boolean dropData)
	This entry point can be used to delete a table definition. More...

_.Library.Status	DropView (_.Library.String viewname)
	This entry point can be used to delete a view definition. More...

_.Library.Status	ExportDDL (_.Library.String schema, _.Library.String table, _.Library.String file, _.Library.DynamicObject qualifiers)
	Export a SQL DDL/DML script file containing Table/View definitions, User definitions, Role definitions, and/or Table/View Privileges based on the qualifiers argument (see below). More...

_.Library.Status	GetAllColumns (_.Library.String tablename, _.Library.String byName, _.Library.String byNumber)
	Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name. More...

_.Library.Status	GetVisibleColumns (_.Library.String tablename, _.Library.String byName, _.Library.String byNumber)
	Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name. More...

	ImportDDL (_.Library.String infile, _.Library.String logfile, _.Library.String DDLMode)
	Import a DDL/DML script file. More...

_.Library.Status	ImportDDLDir (_.Library.String directory, _.Library.String DDLMode, _.Library.DynamicObject qualifiers)
	Import all DDL/DML script file in a given directory. More...

_.Library.Status	ImportDir (_.Library.String dialect, _.Library.String directory, _.Library.DynamicObject qualifiers)
	Import all DDL/DML script files in a given directory. More...

_.Library.String	LoadFDBMS ()
	Import a FDBMS DDL script file.

_.Library.String	LoadInformix ()
	Import an Informix DDL/DML script file. More...

_.Library.String	LoadInterBase ()
	Import an Interbase DDL/DML script file. More...

_.Library.String	LoadMSSQLServer ()
	Import an MS SQL Server DDL/DML script file. More...

_.Library.String	LoadOracle ()
	Import an Oracle DDL/DML script file. More...

_.Library.String	LoadSybase ()
	Import a Sybase DDL/DML script file. More...

_.Library.String	LoadTSQL ()
	Import a TSQL script file. More...

_.Library.Boolean	ProcedureExists (_.Library.String procname, _.Library.String metadata)
	This entry point can be used to determine if a stored procedure exists. More...

_.Library.Status	Run ()
	Import an IRIS SQL script file. More...

_.Library.Status	SetDefault (_.Library.String schema, _.Library.String oldval, _.Library.Boolean Namespace)
	Sets the default schema used by SQL. More...

_.Library.Boolean	TableExists (_.Library.String tablename, _.Library.String metadata)
	This entry point can be used to determine if a base table exists. More...

_.Library.Status	ValidateTable (_.Library.String tablename, _.Library.BigInt index)
	Validate data for a table More...

_.Library.Boolean	ViewExists (_.Library.String viewname, _.Library.String metadata)
	This entry point can be used to determine if a view exists. More...

Static Public Member Functions inherited from Help
_.Library.String	Help (_.Library.String method)
	This is a helper class that is used by the various SYSTEM classes to provide a Help method. More...

Member Function Documentation

◆ CreateLinkedProcedure()

_.Library.Status CreateLinkedProcedure	(	_.Library.String	dsn,
		_.Library.String	externalSchema,
		_.Library.String	externalProcedure,
		_.Library.String	localPackage,
		_.Library.String	localClass,
		_.Library.String	localMethod,
		_.Library.String	localSqlName,
		_.Library.String	description
	)

static

Create a linked process.

dsn - Data Source Name

externalSchema - Schema name of the linked stored procedure of external source

externalProcedure - External selected Stored Procedure name

localPackage - New Stored Procedure's Package name. Default value is "LinkedSchema".

localClass - New class name for the new Linked stored procedure. Default value is "LinkedProcedures"

localMethod - Method name for the stored procedure. Default value is externalProcedure.

localSqlName - New SQL name. Default value is externalProcedure.

description - Description. Default value is empty

◆ CreateLinkedTable()

_.Library.Status CreateLinkedTable	(	_.Library.String	dsn,
		_.Library.String	externalSchema,
		_.Library.String	externalTable,
		_.Library.String	primaryKeys,
		_.Library.String	localClass,
		_.Library.String	localTable,
		_.Library.String	columnMap
	)

static

Create a linked table.

dsn - Data Source Name

externalSchema - Schema name of the linked table of external source

externalTable - The linked table name of external source

primaryKeys - The fields for the primary key of new created table.

Multiple fields are separated by comma. The primary key's field(s) should be specified as the "new class property name" if you do not use the original field name of the external source

You at least have to specify one field for this primaryKey.

localClass - The new linked class name. The default value is "User.LinkedClass"

localTable - The new table name for the linked class. Default value is localClass

columnMap - The linked fields of the external table. Default is to map everything as read-only columns if you don't specify anything in the map. Otherwise, specify the fields in the below format. columnMap("external field name") = $lb("new class property name","new sql field name","read-only"(1/0)) or use external field name as default values by specifying columnMap("external field name") = "".
"new class property name" and "new sql field name" could be defined empty and "external field name" would be used as their default values "read only" default is on.

Below is example for linking a table from MySQL

Use these commands to create a table in MySQL

in MySQL shell:

create database test1;

use test1;

create table Person(PID int,name varchar(255));

insert into Person(PID,name) values(1,"Cache")

in Cache Terminal:

$SYSTEM.SQL.Schema.CreateLinkedTable(dsn,"","Person","PID,name","User.LinkedClass","LinkedTable","")

would link all the fields of the table test1.Person from MySQL to the Cache class "User.LinkedTable"

and use both PID and name as primary keys

◆ Default()

_.Library.String Default ( )

static

Return the default schema name for the current process in the current namespace.

Example:

Set CurrentSchema = $SYSTEM.SQL.Schema.Default()
This method can also be called as a Stored Procedure named SYSTEM_SQL.DefaultSchema()

◆ DropTable()

_.Library.Status DropTable	(	_.Library.String	tablename,
		_.Library.Boolean	dropData
	)

static

This entry point can be used to delete a table definition.

Parameters:

tablename

Name of the SQL table to delete. The name may be qualified with a schema name: Medical.Patient. If the schema name is omitted, the default schema is used.

dropData

TRUE(1)/FALSE(0) flag which determine if the table's data is to be deleted or not. If dropData="" or undefined, use system flag to determine if data should be deleted.

Examples:

Do $SYSTEM.SQL.Schema.DropTable("MedLab.Patient",1)
Do $SYSTEM.SQL.Schema.DropTable("IscPerson",1) ; Deletes SQLUser.IscPerson

◆ DropView()

_.Library.Status DropView ( _.Library.String viewname )

static

This entry point can be used to delete a view definition.

Parameters:

viewname: Name of the SQL view to delete. The name may be qualified with a schema name: Medical.PatientView If the schema name is omitted, the default schema is used.

Examples:

Do $SYSTEM.SQL.Schema.DropView("MedLab.PatientView")
Do $SYSTEM.SQL.Schema.DropView("IscPersonView") ; Deletes SQLUser.IscPerson

◆ ExportDDL()

_.Library.Status ExportDDL	(	_.Library.String	schema,
		_.Library.String	table,
		_.Library.String	file,
		_.Library.DynamicObject	qualifiers
	)

static

Export a SQL DDL/DML script file containing Table/View definitions, User definitions, Role definitions, and/or Table/View Privileges based on the qualifiers argument (see below).

Parameters:

Schema: Name of the schema in the current namespace to export the privileges for. Use "*" to specify the export of SQL Privileges for all schemas in the current namespace. The default is "*". Note that when "*" is specified, '' schemas (such as Dictionary) are not exported. To export privileges for tables in a schema that begins with '' you must explicitly specify that schema name in Schema.
Table: Name of the table/view in the specified schema to export the privileges for. Use "*" to specify the export of SQL Privileges for all table/s views in the specified schema(s). The default is "*". Note that if you "*" for Schema the export will behave as if you entered "*" for Table as well. For example there is no mechanism for exporting the Person table/view in each schema in the namespace.
File: Name of the file to export the SQL statement to.
qualifiers: Specify any subset of the following arguments in in either dynamic object (e.g. {"FileOpenParam":"WNS","Roles":0}), json string (e.g. "{""FileOpenParam"":""WNS"",""Roles"":""0""}"), or legacy qualifier format (e.g. "/FileOpenParam=WNS/Roles=0"). All qualifier names are case insensitive. Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}. FileOpenParam Parameters to use when opening the File. The default is "WNS". Definitions 1/0 flag. Specify 1 to export the table/view definitions. The default is 1. Users 1/0 flag. Specify 1 to export the User definition. The default is 0. Roles 1/0 flag. Specify 1 to export the Role definition, and the GRANT statements to assign the Roles to the Users and Roles they have been granted to. The default is 1. Privileges 1/0 flag. Specify 1 to export the Table and View privileges for the tables and views specified in the Schema specification. The default is 1. SQLSystemPrivileges 1/0 flag. Specify 1 to export the SQL System privileges defined in the current namespace for the users and roles. The default is 1.

Examples:

Do $SYSTEM.SQL.Schema.ExportDDL("Sample","*","C:\PT\Sample.sql",{"FileOpenParam":"WNS","Users":0,"Roles":0,"Privileges":1,"SQLSystemPrivileges":1})
Do $SYSTEM.SQL.Schema.ImportDDL("C:\DDT\UsersAndRoles.sql")

The method returns a Status Code.

A file created via $SYSTEM.SQL.Schema.ExportDDL() can be imported using one of:

do $SYSTEM.SQL.Schema.ImportDDL(<file>,<logFile>,"IRIS")
do $SYSTEM.SQL.Schema.Run()

The ExportDDL() method will not export the following users and roles -

Users:: SuperUser, Admin, UnknownUser, System, CSPSystem, _SYSTEM, _PUBLIC
Roles:: All, Developer, Manager, Operator, SQL, LegacyTerminal, LegacyCacheDirect, LegacySQL

◆ GetAllColumns()

_.Library.Status GetAllColumns	(	_.Library.String	tablename,
		_.Library.String	byName,
		_.Library.String	byNumber
	)

static

Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name.

This includes all SQL hidden columns.

Parameters:

tablename: Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
byName: Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
byNumber: Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name"

Returns: A Status value

◆ GetVisibleColumns()

_.Library.Status GetVisibleColumns	(	_.Library.String	tablename,
		_.Library.String	byName,
		_.Library.String	byNumber
	)

static

Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name.

This excludes all SQL hidden columns. SQL fields are defined as hidden if they are projected by a property defined as private, a RowID field defined as SqlRowIDPrivate, a RowID field for a linked table, a serial field that is not a collection and the storage structure is known, the %CLASSNAME field, or a list or array collection property projected as a child table.

Parameters:

tableName: Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
byName: Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
byNumber: Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name" Returns: A Status value

◆ ImportDDL()

ImportDDL	(	_.Library.String	infile,
		_.Library.String	logfile,
		_.Library.String	DDLMode
	)

static

Import a DDL/DML script file.

Parameters:

infile: The full path name of the script file to import. This parameter is required.
A $list value may also be passed in with the filename and the TranslateTable to use for the file.
(For information on translate tables, see Translation Tables.) The first element is the file name and the second element is the TranslateTable for the input stream file. This is only supported for DDLMode MSSQL, Sybase, Informix, or MySQL.
logfile: The full path name of the file to report errors in. This parameter is optional. Default is the same as the infile with a _Errors.log extension.

DDLMode Vendor from which the script file originated. This parameter is required. Supported values are:

IRIS
CACHE
FDBMS
Informix
Interbase
MSSQL
MSSQLServer - same as MSSQL
MySQL
Oracle
Sybase

When DDLMode=IRIS, the following statements are supported:

CREATE ...
DROP ...
TRUNCATE TABLE ...
ALTER ...
INSERT ...
UPDATE ...
DELETE ...
SET OPTION ...
GRANT ...
REVOKE ...
LOAD DATA ...
USE [DATABASE] ...
TUNE TABLE ...
FREEZE ...
UNFREEZE ...
LOCK ...
UNLOCK ...
BUILD INDEX ...

Examples:

Do $SYSTEM.SQL.Schema.ImportDDL("C:\PT\Patient.sql",,"Sybase")
Do $SYSTEM.SQL.Schema.ImportDDL("C:\DDT\all_tables.sql",all.log,"Oracle")

◆ ImportDDLDir()

_.Library.Status ImportDDLDir	(	_.Library.String	directory,
		_.Library.String	DDLMode,
		_.Library.DynamicObject	qualifiers
	)

static

Import all DDL/DML script file in a given directory.

All files with the extension .sql in the directory will be imported.

Parameters:

directory

The full path name of the directory to import. This parameter is required.

DDLMode

Vendor from which the script file originated. This parameter is required. Supported values are:

Informix
MSSQL
MSSQLServer - same as MSSQL
Sybase

qualifiers

Specify any subset of the following optional arguments in dynamic object format (e.g. {"logFile":"log.txt","eosDelimiter":";"}). Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}, and all qualifier names are case insensitive. logFile The full path name of the file to report errors in. This parameter is Optional. Default is DDLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded.
The name of the log file will be the same as the file imported, but with the extension .log instead of .sql. eosDelimiter End of statement delimiter. Will default to an appropriate value based on the value of DDLMode. This parameter is optional.

Examples:

Do $SYSTEM.SQL.Schema.ImportDDLDir("C:\Work\db1\","Informix",{"logFile":"C:\Work\db1\import.log","eosDelimiter":";"})
Do $SYSTEM.SQL.Schema.ImportDDLDir("C:\Work\db1\","Informix",{"logFile":1,"eosDelimiter":";"})

◆ ImportDir()

_.Library.Status ImportDir	(	_.Library.String	dialect,
		_.Library.String	directory,
		_.Library.DynamicObject	qualifiers
	)

static

Import all DDL/DML script files in a given directory.

All files with the extension .sql in the directory will be imported.

The caller can optionally specify a comma delimited list of file extensions to import. Subdirectories are also recursively processed if the caller specifies the recurse qualifier as 1. The log for this command can be found in SQLImportDir.log within the input directory.

Parameters:

dialect: Vendor from which the script file originated. This parameter is required. Supported values are: MSSQL MSSQLServer - same as MSSQL Sybase
directory: The full path name of the directory to import. This parameter is required.
qualifiers: Specify any subset of the following optional arguments in dynamic object format (e.g. {"logFile":"importdir.log","echoMode":0}). Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}, and all qualifier names are case insensitive. logFile The full path name of the file to report errors in. Default is SQLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql. extensions A comma delimited list of file extensions to import. Defaults to "sql". eosDelimiter End of statement delimiter. Will default to an appropriate value based on the value of DDLMode. recurse 0/1. If 1, then subdirectories of directory will be recursively imported. This parameter defaults to 0. messageMode 0/1. If true then all messages reported by executing imported statements will be displayed on the current device. Default is true. echoMode 0/1. If true, all statement source is displayed on the current device. Default is true. errorPause The number of seconds to pause when an error is reported. The default is five seconds.

Examples:

Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"logFile":"C:\Work\db1\import.log","extensions":"sql","eosDelimiter":";"})
Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"logFile":"C:\Work\db1\import.log","extensions":"sql,tab,sp","eosDelimiter":";","messageMode":1})
Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"eosDelimiter":";"})

◆ LoadInformix()

_.Library.String LoadInformix ( )

static

Import an Informix DDL/DML script file.

The Informix DDL/DML Import Utility supports the following statements:

CREATE TABLE ...
ALTER TABLE ...
CREATE INDEX ...
CREATE VIEW ...
SET OPTION ...
GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...

Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).

◆ LoadInterBase()

_.Library.String LoadInterBase ( )

static

Import an Interbase DDL/DML script file.

The Interbase DDL/DML Import Utility supports the following statements:

CREATE TABLE ...
ALTER TABLE ...
CREATE INDEX ...
CREATE UNIQUE INDEX ...
CREATE VIEW ...
CREATE ROLE ...

Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).

◆ LoadMSSQLServer()

_.Library.String LoadMSSQLServer ( )

static

Import an MS SQL Server DDL/DML script file.

The MS SQL Server DDL/DML Import Utility supports the following statements:

CREATE [GLOBAL TEMPORARY] TABLE ...
CREATE VIEW ...
ALTER TABLE ...
CREATE INDEX ...
CREATE CLUSTERED INDEX ...
CREATE UNIQUE INDEX ...
INSERT ...
UPDATE ...
DELETE ...
SET OPTION ...

Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).

◆ LoadOracle()

_.Library.String LoadOracle ( )

static

Import an Oracle DDL/DML script file.

The Oracle DDL/DML Import Utility supports the following statements:

CREATE TABLE ...
CREATE VIEW ...
ALTER TABLE ...
CREATE INDEX ...
CREATE UNIQUE INDEX ...
DROP TABLE ...
DROP VIEW ...
DROP INDEX ...
INSERT ...
UPDATE ...
DELETE ...
SET OPTION ...
CREATE USER ...
CREATE ROLE ...
GRANT { SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...

Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).

◆ LoadSybase()

_.Library.String LoadSybase ( )

static

Import a Sybase DDL/DML script file.

The Sybase DDL/DML Import Utility supports the following statements:

CREATE [GLOBAL TEMPORARY] TABLE ...
CREATE VIEW ...
ALTER TABLE ...
CREATE INDEX ...
CREATE CLUSTERED INDEX ...
CREATE UNIQUE INDEX ...
INSERT ...
UPDATE ...
DELETE ...
SET OPTION ...
GRANT CONNECT ... (Same as SQL CREATE USER ...)
GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...

Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).

◆ LoadTSQL()

_.Library.String LoadTSQL ( )

static

Import a TSQL script file.

TSQL source files can contain any TSQL syntax supported by InterSystems IRIS TSQL.
This API will put the caller to the SQL Shell in the default TSQL dialect as defined in the TSQL Compatibility Settings.
The caller can then use the run [filename] command from the shell to import the script file.

◆ ProcedureExists()

_.Library.Boolean ProcedureExists	(	_.Library.String	procname,
		_.Library.String	metadata
	)

static

This entry point can be used to determine if a stored procedure exists.

Parameters:

procname: Name of the stored procedure to check.
procname can be qualified or unqualified. If unqualified, the default schema is applied.
metadata: Passed by reference, optional argument.
If the stored procedure exists, returns a $LIST with the following information about the procedure: $ListBuild(SchemaName,ProcedureName,Classname that projected the stored procedure,Procedure type)

Examples:

Write $SYSTEM.SQL.Schema.ProcedureExists("SQLUser.stpSalaryReport") // Writes a 1 if procedure SQLUser.stpSalaryReport exists
Write $SYSTEM.SQL.Schema.ProcedureExists("stpSalaryReport",.metadata) // Writes a 1 if procedure [DefaultSchema].stpSalaryReport exists, returns metadata=$lb("SQLUser","stpSalaryReport","User.stpSalaryReport","function")

Notes:

If the user calling the function does not hold any privileges for the procedure, 0 will be returned.
If a class exists that would project this procedure to SQL during compilation, but the class has not been compiled, 0 will be returned.
If a procedure is marked as hidden, 0 will be returned.
metadata will be set to "" if 0 is returned by the function.

This method can also be called as a Stored Procedure named SYSTEM_SQL.Schema_ProcedureExists(procname)

◆ Run()

_.Library.Status Run ( )

static

Import an IRIS SQL script file.

For InterSystems IRIS SQL script files, the default end-of-statement marker is the string GO. The end-of-statement marker must be on a line by itself after the statement.

◆ SetDefault()

_.Library.Status SetDefault	(	_.Library.String	schema,
		_.Library.String	oldval,
		_.Library.Boolean	Namespace
	)

static

Sets the default schema used by SQL.

This configuration setting provides the ability to define a default schema name other than SQLUser, the default. When an unqualified table name is encountered in an SQL statement (and there is no #import statement specified), the default schema will be used. This setting has nothing to do with the mappings between SQL schema names and the class package name, it only specifies the default schema.

Parameter:

schema: String containing the default SQL schema name. If schema is "" or not defined, the default schema will be set to SQLUser.
oldval: Passed By Reference. Contains the previous value of the setting.
Namespace: Boolean 1/0 flag. If TRUE, set the default schema for the current namespace only. The default for Namespace is FALSE

Returns:

Status Code

NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately.

◆ TableExists()

_.Library.Boolean TableExists	(	_.Library.String	tablename,
		_.Library.String	metadata
	)

static

This entry point can be used to determine if a base table exists.

Parameters:

tablename: Name of the table to check.
tablename can be qualified or unqualified. If unqualified, the default schema is applied.
metadata: Passed by reference, optional argument.
If the table exists, returns a $LIST with the following information about the table: $ListBuild(SchemaName,TableName,Classname that projected the table,System Flag)

Examples:

Write $SYSTEM.SQL.Schema.TableExists("Sample.Person") // Writes a 1 if table Sample.Person exists
Write $SYSTEM.SQL.Schema.TableExists("Sample.Person",.metadata) // Writes a 1 if table Sample.Person exists, returns metadata=$lb("Sample","Person","Sample.Person","0")

Notes:

If the user calling the function does not hold any privileges for the table, 0 will be returned.
If a class exists that would project this table to SQL during compilation, but the class has not been compiled, 0 will be returned.
metadata will be set to "" if 0 is returned by the function.

This method can also be called as a Stored Procedure named SYSTEM_SQL.Schema_TableExists(tablename)

◆ ValidateTable()

_.Library.Status ValidateTable	(	_.Library.String	tablename,
		_.Library.BigInt	index
	)

static

Validate data for a table

This utility can be called via $SYSTEM.SQL.Schema.ValidateTable(tablename) or as the SYSTEM_SQL.Schema_ValidateTable(tablename) stored procedure. This method/procedure returns a resultset that contains a row for each issue found with the table's data. If the resultset is empty, the table has no data validation issues. The table's data is checked for the following:

Each field's validation code from the IsValid method of the field's datatype Required fields do not have a null value Unique constraints do not have duplicate values Foreign Key fields reference valid rows in the referenced table

Parameters:

tablename: Name of the table to validate data. Must be a table name, not a view name.
index: Internal use only

Returns: Status Code

Example: Set status=$SYSTEM.SQL.Schema.ValidateTable("Sample.Person") // Validates the data in the Sample.Person table Set status=$SYSTEM.SQL.Schema.ValidateTable("Company") // Validates the data in the SQLUser.Company table call SYSTEM_SQL.Schema_ValidateTable('Sample.Person') // Validates the data in the Sample.Person table via SQL

Notes:

After calling $SYSTEM.SQL.Schema.ValidateTable(...), the resultset will be held in the sqlcontext object. Do sqlcontext.Display() will dump the results to the current device
If the table is sharded, this should be called on the shard master table
There is no locking performed by the utility. If run on a table in a live system, you could receive false-positive error reports

◆ ViewExists()

_.Library.Boolean ViewExists	(	_.Library.String	viewname,
		_.Library.String	metadata
	)

static

This entry point can be used to determine if a view exists.

Parameters:

viewname: Name of the view to check.
viewname can be qualified or unqualified. If unqualified, the default schema is applied.
metadata: Passed by reference, optional argument.
If the view exists, returns a $LIST with the following information about the view: $ListBuild(SchemaName,ViewName,Classname that projected the view,System Flag)

Examples:

Write $SYSTEM.SQL.Schema.ViewExists("SQLUser.STestView") // Writes a 1 if view SQLUser.STestView exists
Write $SYSTEM.SQL.Schema.ViewExists("STestView",.metadata) // Writes a 1 if view [DefaultSchema].STestView exists, returns metadata=$lb("SQLUser","STestView","User.STestView","0")

Notes:

If the user calling the function does not hold any privileges for the view, 0 will be returned.
If a class exists that would project this view to SQL during compilation, but the class has not been compiled, 0 will be returned.
If a class that projects the view is marked as hidden, 0 will be returned.
metadata will be set to "" if 0 is returned by the function.

This method can also be called as a Stored Procedure named SYSTEM_SQL.Schema_ViewExists(viewname)

Static Public Member Functions

Member Function Documentation

◆ CreateLinkedProcedure()

◆ CreateLinkedTable()

◆ Default()

◆ DropTable()

◆ DropView()

◆ ExportDDL()

◆ GetAllColumns()

◆ GetVisibleColumns()

◆ ImportDDL()

◆ ImportDDLDir()

◆ ImportDir()

◆ LoadInformix()

◆ LoadInterBase()

◆ LoadMSSQLServer()

◆ LoadOracle()

◆ LoadSybase()

◆ LoadTSQL()

◆ ProcedureExists()

◆ Run()

◆ SetDefault()

◆ TableExists()

◆ ValidateTable()

◆ ViewExists()