Working with Ms-sql Server Information Schema View

Understanding the Information Schema

A database consists of data and metadata. Data, as the name implies, is the collection of values (including TEXT, NTEXT, and IMAGE items) stored in the columns that make up each row in the tables within the database. Metadata is a description of the database objects and is used to store and manage the data items in the database. The system stores database metadata in a set of tables known collectively as the system catalog. If you were to query the tables in the system catalog, you could display metadata that describes the database tables, columns, views, constraints, domains, privileges, and so on. In short, the DBMS system catalog's tables contain metadata data that describe every object in the database.

Although the DBMS maintains the system catalog for its own internal use, you can use standard SQL queries (SELECT statements) to access the system tables as well. As a matter of fact, you used the system catalog when you called the SQLColName() function to retrieve the names of the columns in a query's results set in Tip 398, "Using the SqlColName() Function to Retrieve the Names of the Columns in the Results Set Generated by a Query," and when you called the SQLNumCols() function to retrieve the number of columns returned by a query in Tip 397, "Using the SqlNumCols() Function to Determine the Number of Columns in the Results Set Generated by a Query." In short, a user-accessible system catalog makes a relational DBMS self-describing and lets you design general-purpose "front ends" such as query tools like the MS-SQL Server's Query Analyzer.

While all of the major DBMS products use system catalogs to store metadata, neither SQL-89 nor the current SQL-92 standard requires that they do so. As a result, the structure of the system catalog tables and the metadata contained in them varies greatly from one DBMS product to another. Rather than taking on the impossible task of trying to get DBMS vendors to change their products and agree on a standard structure (and physical implementation) for the system catalog, the SQL standards committee defined a series of views of the system catalog tables instead. These views of the system catalog are called the information schema in the SQL-92 standard.

The system catalog table views that make up the information schema give each user a standardized way to get a description of the database objects available on the user's database connection. Tips 473-492 describe the 20 information schema views available on MS-SQL Server. The important thing to understand now is that if your DBMS product is compliant with the SQL-92 standard, it, too, will have information schema views similar to those found on the MS-SQL Server. However, the SQL-92 standard allows DBMS vendors to add additional views and to add additional columns to the standard-defined information schema views. Therefore, use the following 20 tips (Tips 473-492) as a guide to the type of information available in the various information schema views, and check your system manual for the information schema definition specific to your DBMS product.

Understanding the Information Schema CHECK_CONSTRAINTS View

The CHECK_CONSTRAINTS view is a virtual table based on information from the SYSOBJECTS and SYSCOMMENTS system tables that contains one row for each CHECK constraint in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

the DBMS will return the information shown in Table 473.1 about the CHECK constraints on objects in the current database to which the login ID you used to connect with the server has at least one of the access privileges.

Table 473.1: The Columns in the Information Schema CHECK_CONSTRAINTS View

Column Name	Data Type	Description
CONSTRAINT_CATALOG	NVARCHAR(128)	Database in which the constraint is defined
CONSTRAINT_SCHEMA	NVARCHAR(128)	Owner of the constraint
CONSTRAINT_NAME	SYSNAME	Name of the constraint
CHECK CLAUSE	NVARCHAR(4000)	Text of the CHECK constraint

To display the information schema CHECK_CONTRAINTS view column information for all tables in the current database (even those to which you do not have any access privileges), execute a SELECT statement such as:

 SELECT db_name() 'Database/Catalog',
 user_name(sysobjects.UID) 'Constraint Owner',
 sysobjects.name 'Constraint Name',
 syscomments.text 'Constraint Text'
 FROM sysobjects, syscomments
 WHERE sysobjects.ID = syscomments.ID
 AND sysobjects.xtype = 'C'

Understanding the Information Schema COLUMN_DOMAIN_USAGE View

The COLUMN_DOMAIN_USAGE view is a virtual table based on information from the SYSOBJECTS, SYSCOLUMNS, and SYSTYPES system tables that contains one row for each column in the current database that has a user-defined data type. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE

the DBMS will return the information shown in Table 474.1 about the columns defined using user-defined data types in tables in the current database to which the login ID that you used to connect with the server has at least one of the access privileges.

Table 474.1: The Columns in the Information Schema COLUMN_DOMAIN_USAGE View

Column Name	Data Type	Description
DOMAIN_CATALOG	NVARCHAR(128)	Database in which the user-defined data type was created
DOMAIN_SCHEMA	NVARCHAR(128)	The username that created the user-defined data type
DOMAIN_NAME	SYSNAME	The name of the user-defined data type
TABLE_CATALOG	NVARCHAR(128)	The database that contains the table with the column that has a user-defined data type
TABLE_SCHEMA	NVARCHAR(128)	The user ID of the owner of the table with the column that has a user-defined data type
TABLE_NAME	SYSNAME	The name of the table with the column that has a user-defined data type
COLUMN_NAME	SYSNAME	The name of the column with the user-defined data type

To display the information schema, COLUMN_DOMAIN_USAGE view column information for all tables in the current database (even those to which you do not have any access privileges), execute a SELECT statement such as:

 SELECT db_name() 'Domain Database/Catalog',
 user_name(systypes.UID) 'Domain Owner',
 systypes.name 'Domain Name',
 db_name() 'Table Database/Catalog'
 user_name(sysobjects.UID) 'Table Owner',
 sysobjects.name 'Table Name',
 syscolumns.name 'Column Name'
 FROM sysobjects, syscolumns, systypes
 WHERE sysobjects.ID = syscolumns.ID
 AND syscolumns.xusertype = systypes.xusertype
 AND systypes.xusertype > 256

Understanding the Information Schema COLUMN_PRIVILEGES View

The COLUMN_PRIVILEGES view is a virtual table based on information from the SYSPROTECTS, SYSOBJECTS, and SYSCOLUMNS system tables that contains one row for each privilege on a column granted to or granted by a database user. When you execute the query

 SELECT * FROM INFORMATTON_SCHEMA.COLUMN_PRIVILEGES

the DBMS will return the information shown in Table 475.1 about the privileges on table columns in the current database granted by or granted to the login ID you used to connect with the server.

Table 475.1: The Columns in the Information Schema COLUMN_PRIVILEGES View

Column Name	Data Type	Description
GRANTOR	NVARCHAR(128)	ID of the account that granted the privilege
GRANTEE	NVARCHAR(128)	ID of the account to which the privilege was granted
TABLE_CATALOG	NVARCHAR(128)	The name of the database that contains the table column on which the privilege was granted
TABLE_SCHEMA	NVARCHAR(128)	ID of the user that owns the table that contains the column on which the privilege was granted
TABLE_NAME	SYSNAME	The name of the table that contains the column on which the privilege was granted
COLUMN_NAME	SYSNAME	The name of the column on which the privilege was granted
PRIVILEGE_TYPE	VARCHAR(10)	The access privilege (permission) granted on the column
IS_GRANTABLE	VARCHAR(3)	YES if the privilege grantee has the right to grant the privilege (permission) to others, or NO if the grantee does not

To review the Transact-SQL code that defines the information schema COLUMN_PRIVILEGES view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema COLUMNS View

The COLUMNS view is a virtual table based on information from the SYSOBJECTS, MASTER.DBO.SPT_DATATYPE_INFO, SYSTYPES, SYSCOLUMNS, SYSCOMMENTS, and MASTER.DBO.SYSCHARSETS system tables that contains one row for each privilege on a column granted to or granted by a database user. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.COLUMNS

the DBMS will return the information shown in Table 476.1 about the table columns in the current database available to the login ID you used to connect with the server.

Table 476.1: The Columns in the Information Schema COLUMNS View

Column Name/Data Type	Description
TABLE_CATALOG NVARCHAR(128)	The name of the database that contains the column
TABLE_SCHEMA NVARCHAR(128)	The ID of the user that owns the table that contains the column
TABLE_NAME NVARCHAR(128)	The name of the table that contains the column
COLUMN_NAME NVARCHAR(128)	The name of the column
ORDINAL_POSITION SMALLINT	The position of the column in its table definition, counting from left to right (\ with the first column on the left having an ordinal position of 1)
COLUMN_DEFAULT NVARCHAR(4000)	The column's default value
IS_NULLABLE VARCHAR(3)	YES if the column allows a NULL value, or NO if not
DATA_TYPE NVARCHAR(128)	The column's data type
CHARACTER_MAXIMUM_LENGTH SMALLINT	Maximum length in characters for BINARY, CHARACTER, TEXT, and IMAGE data; NULL for columns of other data types
CHARACTER_OCTET_LENGTH SMALLINT	Maximum length in bytes for BINARY, CHARACTER, TEXT, and IMAGE data; NULL for columns of other data types
NUMERIC PRECISION TINYINT	Precision for INTEGER data, precise and approximate numeric data, and MONEY data; NULL for columns of other data types
NUMERIC_PRECISION_RADIX SMALLINT	Precision radix for INTEGER data, precise and approximate numeric data, and MONEY data; NULL for columns of other data types
NUMERIC_SCALE TINYINT	The scale for INTEGER data, precise and approximate numeric data, and MONEY data; NULL for columns of other data types
DATETIME_PRECISION SMALLINT	Subtype code for DATETIME and INTERVAL data; NULL for columns of other data types
CHARACTER_SET_CATALOG VARCHAR(6)	The name of the database (normally MASTER) in which the character set for character and TEXT data is located; NULL for columns of other data types
CHARACTER_SET_SCHEMA VARCHAR(3)	The ID (normally DBO) of the owner of the character set for character and TEXT data; NULL for columns of other data types
CHARACTER_SET_NAME NVARCHAR(128)	The name of the character set for character and TEXT columns; NULL for columns of other data types
COLLATION_CATALOG VARCHAR(6)	The name of the database (normally MASTER) in which the sort order for the column's character or TEXT data is defined; NULL for columns of other data types
COLLATION_SCHEMA VARCHAR(3)	The ID (normally DBO) of the owner of the collation for a column with character or TEXT data; NULL for columns of other data types
COLLATION_NAME NVARCHAR(128)	The name of the sort order for columns with character or TEXT data; NULL for columns of other data types
DOMAIN_CATALOG NVARCHAR(128)	The name of the database in which the user-defined data type was created if the column has a user-defined data type; NULL for columns of other data types
DOMAIN_SCHEMA NVARCHAR(128)	The ID of the user that created the user-defined data type if the column has a user-defined data type; NULL for columns of other data types
DOMAIN_NAME NVARCHAR(128)	The name of the user-defined data type if the column has a user-defined data type; NULL for columns of other data types

To review the Transact-SQL code that defines the information schema COLUMNS view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema CONSTRAINT_COLUMN_USAGE View

The CONSTRAINT_COLUMN_USAGE view is a virtual table based on information from the SYSOBJECTS, SYSCOLUMNS, and SYSTYPES system tables that contains one row for each column with a constraint defined on it. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

the DBMS will return the information shown in Table 477.1 about the table, column, and constraints on each of the columns in the current database that is defined with a constraint and to which the login ID you used to connect with the server has at least one of the access privileges.

Table 477.1: The Columns in the Information Schema CONSTRAINT_COLUMN_USAGE View

Column Name/Data Type	Description
TABLE_CATALOG NVARCHAR(128)	The name of the database in which the table with the constrained column is defined
TABLE_SCHEMA NVARCHAR(128)	The ID of the owner of the table with the constrained column
TABLE_NAME NVARCHAR(128)	The name of the table with the constrained column
COLUMN_NAME NVARCHAR(128)	The name of the column on which the constraint is defined
CONSTRAINT_CATALOG NVARCHAR(128)	The name of the database in which the constraint is defined
CONSTRAINT_SCHEMA NVARCHAR(128)	The ID of the constraint's owner
CONSTRAINT_NAME NVARCHAR(128)	The name of the constraint

To display the information schema CONSTRAINT_COLUMN_USAGE view information for all columns in the current database that have constraints (even those to which you do not have any access privileges), execute a SELECT statement such as:

 SELECT iskcu.table_catalog 'Table Database',
 iskcu.table_schema 'Table Owner ID',
 iskcu.table_name 'Table Name',
 iskcu.column_name 'Column Name',
 iskcu.constraint_catalog 'Constraint Database',
 iskcu.constraint_schema 'Constraint Owner ID',
 iskcu.constraint_name 'Constraint Name'
 FROM information_schema.key_column_usage iskcu
 UNION
 SELECT db_name(),
 user_name(tableobj.UID),
 tableobj.name,
 syscolumns.name,
 db_name(),
 user_name(constraintobj.UID),
 constraintobj.name
 FROM sysobjects tableobj, sysobjects constraintobj,
 syscolumns
 WHERE tableobj.ID = constraintobj.parent_obj
 AND constraintobj.xtype = 'C'
 AND constraintobj.info = syscolumns.colid
 AND syscolumns.ID = constraintobj.parent_obj
 UNION
 SELECT db_name(),
 user_name(tableobj.UID),
 tableobj.name,
 syscolumns.name,
 db_name(),
 user_name(constraintobj.UID),
 constraintobj.name
 FROM sysobjects tableobj, sysobjects constraintobj,
 syscolumns, systypes
 WHERE tableobj.ID = syscolumns.ID
 AND syscolumns.xusertype = systypes.xusertype
 AND systypes.xusertype > 256
 AND systypes.domain = constraintobj.ID
 AND constraintobj.xtype = 'R'

Understanding the Information Schema CONSTRAINT_TABLE_USAGE View

The CONSTRAINT_TABLE_USAGE view is a virtual table based on information from the SYSOBJECTS system table that contains one row for each table with a constraint defined on it. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

the DBMS will return the information shown in Table 478.1 about each table in the current database that is defined with a constraint and to which the login ID you used to connect with the server has at least one of the access privileges.

Table 478.1: The Columns in the Information Schema CONSTRAINT_TABLE_USAGE View

Column Name	Data Type	Description
TABLE_CATALOG	NVARCHAR(128)	The name of the database in which the constrained table is defined
TABLE_SCHEMA	NVARCHAR(128)	The ID of the owner of the constrained table
TABLE_NAME	SYSNAME	The name of the constrained table
CONSTRAINT_CATALOG	NVARCHAR(128)	The name of the database in which the table constraint is defined
CONSTRAINT_SCHEMA	NVARCHAR(128)	The ID of the constraint's owner
CONSTRAINT_NAME	SYSNAME	The name of the table constraint

To display the information schema CONSTRAINT_TABLE_USAGE view information for all tables in the current database that have constraints (even those to which you do not have any access privileges), execute a SELECT statement such as:

 SELECT db_name() 'Table Database',
 user_name(tableobj.UID) 'Table Owner',
 tableobj.name 'Table Name',
 db_name() 'Constraint Database',
 user_name(constraintobj.UID) 'Constraint Owner",
 constraintobj.name 'Constraint Name'
 FROM sysobjects tableobj, sysobjects constraintobj
 WHERE tableobj.ID = constraintobj.parent_obj
 AND constraintobj.xtype IN ('C','UQ','PK','F')

Understanding the Information Schema DOMAIN_CONSTRAINTS View

The DOMAIN_CONSTRAINTS view is a virtual table based on information from the SYSOBJECTS and SYSTYPES system tables that contains one row for each user-defined data type in the current database with a RULE bound to it. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

the DBMS will return the information shown in Table 479.1 about each RULE-bound user-defined data type in the current database to which the login ID you used to connect with the server has access.

Table 479.1: The Columns in the Information Schema DOMAIN_CONSTRAINTS View

Column Name	Data Type	Description
CONSTRAINT_CATALOG	NVARCHAR(128)	The name of the database in which the RULE is defined
CONSTRAINT_SCHEMA	NVARCHAR(128)	The ID of the RULE's owner
CONSTRAINT_NAME	SYSNAME	The name of the RULE
DOMAIN_CATALOG	SYSNAME	The name of the database in which the user-defined data type is defined
DOMAIN_SCHEMA	NVARCHAR(128)	The ID of the user that created the user-defined data type
DOMAIN_NAME	SYSNAME	The name of the user-defined data type
IS_DEFERRABLE	VARCHAR(2)	Whether constraint checking is deferrable; always NO
INITIALLY_DEFERRED	VARCHAR(2)	Whether constraint checking is initially deferred; always NO

To display the information schema DOMAIN_CONSTRAINTS view information for all RULE-bound user-defined data types (even those to which you do not have access), execute a SELECT statement such as:

 SELECT db_name() 'Rule Database/Catalog',
 user_name(sysobjects.UID) 'Rule Owner',
 sysobjects.name 'Rule Name',
 db_name() 'User Data Type Database',
 user_name(systypes.UID) 'User Data Type Owner',
 systypes.name 'User Data Type Name',
 'NO' 'Is Deferrable',
 'NO' 'Initially Deferred'
 FROM sysobjects, systypes
 WHERE sysobjects.xtype = 'R'
 AND sysobjects.ID = systypes.domain
 AND systypes.xusertype > 256

Understanding the Information Schema DOMAINS View

The DOMAINS view is a virtual table based on information from the MASTER.DBO.SPT_ DATATYPE_INFO, SYSTYPES, SYSCOMMENTS, and MASTER.DBO.SYSCHARSETS system tables that contains one row for each user-defined data type, in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.DOMAINS

the DBMS will return the information shown in Table 480.1 about each user-defined data type in the current database to which the login ID you used to connect with the server has access.

Table 480.1: The Columns in the Information Schema DOMAINS View

Column Name/Data Type	Description
DOMAIN_CATALOG NVARCHAR(128)	The name of the database in which the user-define data type is defined
DOMAIN SCHEMA NVARCHAR(128)	The ID of the user that created the user-defined data type
DOMAIN_NAME SYSNAME	The name of the user-defined data type
DATA_TYPE SYSNAME	The system-supplied (SQL) data type
CHARACTER_MAXIMUM_LENGTH SMALLINT	The column's maximum length in characters for columns with character, BINARY, TEXT, and IMAGE data; NULL for columns of other data types
CHARACTER_OCTET_LENGTH SMALLINT	The column's maximum length in bytes for columns with character, BINARY, TEXT, and IMAGE data; NULL for columns of other data types
COLLATION_CATALOG VARCHAR(6)	The name of the database (normally MASTER) in which the sort order for columns with character or TEXT data; NULL for columns of other data types
COLLATION_SCHEMA VARCHAR(3)	The name of the database (normally the ID of the owner of the sort order) for columns with character or TEXT data—always DBO or NULL for columns of other data types
COLLATION_NAME NVARCHAR(128)	The name of the sort order for columns with character or TEXT data; NULL for columns of other data types
CHARACTER_SET_CATALOG VARCHAR(6)	The name of the database in which the sort order is defined for columns with character or TEXT data—always MASTER or NULL for columns of other data types
CHARACTER_SET_SCHEMA VARCHAR(3)	The ID of the owner of the character set for columns with character or TEXT data—always DBO or NULL for columns of other data types
CHARACTER_SET_NAME NVARCHAR(128)	The name of the character set for columns with character or TEXT data; NULL for columns of other data types
NUMERIC_PRECISION TINYINT	Precision for columns with numeric data; NULL for columns of other data types
NUMERIC PRECISION RADIX SMALLINT	Precision radix for columns with numeric data; NULL for columns of other data types
NUMERIC_SCALE TINYINT	Scale for columns with numeric data; NULL for columns of other data types
DATETIME_PRECISION SMALLINT	Subtype code for DATETIME and INTERVAL data; NULL for columns of other data types
DOMAIN_DEFAULT NVARCHAR(4000)	Transact-SQL definition of the domain

To review the Transact-SQL code that defines the information schema DOMAINS view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema KEY_COLUMN_USAGE View

The KEY_COLUMN_USAGE view is a virtual table based on information from the SYSOBJECTS, SYSCOLUMNS, SYSREFERENCES, MASTER.DBO.SPT_VALUES, and SYSINDEXES system tables that contains one row for each column used in either a PRIMARY KEY or a FOREIGN KEY constraint. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

the DBMS will return the information shown in Table 481.1 about the columns in the current database that are part of either a PRIMARY KEY or a FOREIGN KEY in tables to which the login ID you used to connect with the server has at least one of the access privileges.

Table 481.1: The Columns in the Information Schema KEY_COLUMN_USA6E View

Column Name/Data Type	Description
CONSTRAINT_CATALOG NVARCHAR(128)	The name of the database that contains the PRIMARY KEY or FOREIGN KEY constraint that uses the column
CONSTRAINT_SCHEMA NVARCHAR(128)	The ID of the user that owns the key constraint that uses the column
CONSTRAINT_NAME NVARCHAR(128)	The name of the key constraint that uses the column
TABLE_CATALOG NVARCHAR(128)	The name of the database that contains the table with the key that uses the column
TABLE SCHEMA NVARCHAR(128)	The ID of the user that owns the table with the key constraint that uses the column
TABLE_NAME NVARCHAR(128)	The name of the table with the key constraint that uses the column
COLUMN_NAME NVARCHAR(128)	The name of the column that is in a FOREIGN KEY or PRIMARY key constraint
ORDINAL_POSITION INTEGER	The position of the column in the table in which it is defined

To review the Transact-SQL code that defines the information schema KEY_COLUMN_USAGE view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema PARAMETERS View

The PARAMETERS view is a virtual table based on information from the SYSOBJECTS, SYSCOLUMNS, and MASTER.DBO.SYSCHARSETS system tables that contains one row for each parameter passed to a user-defined function or stored procedure. Moreover, the view contains an additional row with return value information for each user-defined function. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

the DBMS will return the information shown in Table 482.1 about the parameters for user-defined functions and stored procedures, and the return value for each function available to the login ID you used to connect with the server.

Table 482.1: The Columns in the Information Schema PARAMETERS View

Column Name/Data Type	Description
SPECIFIC_CATALOG NVARCHAR(128)	The name of the database that contains the function or stored procedure (the routine) that uses the parameter or returns the value described in the current row of the PARAMETERS view
SPECIFIC_SCHEMA NVARCHAR(128)	The ID of the owner of the of the routine that uses the parameter returns the value
SPECIFIC_NAME NVARCHAR(128)	The name of the routine that uses the parameter or returns the value
ORDINAL_POSITION SMALLINT	The position of the parameter in the routine's parameter list (starting at 1), or 0 for the return value for a function
PARAMETER_MODE NVARCHAR(10)	IN for an input parameter; OUT for an output parameter; INOUT for an input/output parameter
IS_RESULT NVARCHAR(10)	YES if the parameter is the return value from a function, or NO if it is not
AS_LOCATOR NVARCHAR(10)	YES if the parameter is declared as a locator, or NO if it is not
PARAMETER_NAME NVARCHAR(128)	The name of the parameter, or NULL if the parameter is the return value for a function
DATA_TYPE NVARCHAR(128)	The parameter's data type
CHARACTER_MAXIMUM_LENGTH INTEGER	The maximum length in characters for character and TEXT data; NULL for parameters of other data types
CHARACTER_OCTET_LENGTH INTEGER	The maximum length in bytes for columns that contain character or TEXT data; NULL for columns of other data types
COLLATION_CATALOG NVARCHAR(128)	The name of the database that contains the sort order for parameters of one of the character data types; NULL for parameters of other data types
COLLATION_SCHEMA NVARCHAR(128)	The name of the schema that contains the collation (sort order) for parameters of one of the character data types; NULL for parameters of other data types
COLLATION NAME NVARCHAR(128)	The name of the collation (sort order) for parameters of one of the character data types; NULL for parameters of other data types
CHARACTER_SET_CATALOG NVARCHAR(128)	The name of the database that contains the character set for parameters of one of the character data types; NULL for parameters of other data types
CHARACTER_SET_SCHEMA NVARCHAR(128)	The user ID of the owner of the character set for parameters of one of the character data types; NULL for parameters of other data types
CHARACTER_SET_NAME NVARCHAR(128)	The name of the character set for parameters of one of the character data types; NULL for parameters of other data types
NUMERIC_PRECISION TINYINT	Precision for columns with numeric data; NULL for columns of other data types
NUMERIC_PRECISION_RADIX SMALLINT	Precision radix for columns with numeric data; NULL for columns of other data types
NUMERIC_SCALE TINYINT	Scale for columns with numeric data; NULL for columns of other data types
DATETIME_PRECISION SMALLINT	Subtype code for DATETIME and INTERVAL data; NULL for columns of other data types
INTERVAL_TYPE NVARCHAR(30)	NULL (reserved for future use)
INTERVAL_PRECISION SMALLINT	NULL (reserved for future use)
USER_DEFINED_TYPE_CATALOG NVARCHAR(128)	NULL (reserved for future use)
USER_DEFINED_TYPE_SCHEMA NVARCHAR(128)	NULL (reserved for future use)
USER_DEFINED_TYPE_NAME NVARCHAR(128)	NULL (reserved for future use)
SCOPE_CATALOG NVARCHAR(128)	NULL (reserved for future use)
SCOPE_SCHEMA NVARCHAR(128)	NULL (reserved for future use)
SCOPE_NAME NVARCHAR(128)	NULL (reserved for future use)

To review the Transact-SQL code that defines the information schema PARAMETERS view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema REFERENTIAL_CONSTRAINTS View

The REFERENTIAL_CONSTRAINTS view is a virtual table based on information from the SYSOBJECTS, SYSREFERENCES, and SYSINDEXES system tables that contains one row for each FOREIGN KEY constraint in tables in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

the DBMS will return the information shown in Table 483.1 about each FOREIGN KEY constraint in tables to which the login ID you used to connect with the server has at least one of the access privileges.

Table 483.1: The Columns in the Information Schema REFERENTIAL_CONSTRAINTS View

Column Name/Data Type	Description
CONSTRAINT_CATALOG NVARCHAR(128)	The name of the database in which the FOREIGN KEY is defined
CONSTRAINT_SCHEMA NVARCHAR(128)	The user ID of the FOREIGN KEY constraint's owner
CONSTRAINT_NAME SYSNAME	The name of the FOREIGN KEY constraint
UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128)	The name of the database in which the FOREIGN KEY is defined
UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128)	The user ID of the owner of the FOREIGN KEY
UNIQUE_CONSTRAINT_NAME SYSNAME	The name of the FOREIGN KEY constraint—from the SYSREFERENCES table
MATCH_OPTION VARCHAR(7)	Always NONE
UPDATE RULE VARCHAR(9)	Either CASCADE or NO ACTION, depending on whether the DBMS is to change the value of the FOREIGN KEY to match the new value of the PRIMARY KEY that it references if the PRIMARY KEY is changed
DELETE_RULE VARCHAR(9)	Either CASCADE or NO ACTION, depending on whether the DBMS is to remove the row with the FOREIGN KEY if the row with the PRIMARY KEY referenced by the FOREIGN key is deleted

To display the information schema REFERENTIAL_CONSTRAINTS view information for foreign keys in the current database (even those in tables on which you have no access privileges), execute a SELECT statement such as:

 SELECT db_name() 'FOREIGN KEY Database',
 user_name(foreignkey_obj.UID)'FOREIGN KEY Owner',
 foreign_obj.name 'FOREIGN KEY Table',
 db_name() 'PRIMARY KEY Database',
 user_name(primarykey_obj.UID)'PRIMARY KEY Owner',
 sysindexes.name 'FK INDEX Name',
 'NONE' 'Match',
 CASE WHEN (OBJECTPROPERTY
 (sysreferences.constid,'CnstIsUpdateCascade')=1)
 THEN 'CASCADE'
 ELSE 'NO ACTION' END 'UPDATE Rule',
 CASE WHEN (OBJECTPROPERTY
 (sysreferences.constid,'CnstIsDeleteCascade')=1)
 THEN 'CASCADE'
 ELSE 'NO ACTION' END 'DELETE Rule'
 FROM sysobjects foreignkey_obj, sysreferences, sysindexes,
 sysobjects primarykey_obj
 WHERE foreignkey_obj.xtype = 'F'
 AND sysreferences.constID = foreignkey_obj.ID
 AND sysreferences.rkeyID = sysindexes.ID
 AND sysreferences.rkeyindID = sysindexes.indID
 AND sysreferences.rkeyID = primarykey_obj.ID

Understanding the Information Schema ROUTINES View

The ROUTINES view is a virtual table based on information from the SYSOBJECTS, SYSCOLUMNS, MASTER.DBO.SPT_DATATYPE_INFO, and MASTER.DBO. SYSCHARSETS system tables that contains one row for each stored procedure and function in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.ROUTINES

the DBMS will return the information shown in Table 484.1 about the stored procedures and functions in the current database that are accessible by the login ID you used to connect with the server.

Table 484.1: The Columns in the Information Schema ROUTINES View

Column Name/Data Type	Description
SPECIFIC_CATALOG NVARCHAR(128)	The name of the database in which the stored procedure or function is defined
SPECIFIC_SCHEMA	The user ID of the owner of the stored procedure or function
SPECIFIC_NAME NVARCHAR(128)	The name of the stored procedure or function
ROUTINE_CATALOG NVARCHAR(128)	Same as SPECIFIC_CATALOG
ROUTINE_SCHEMA NVARCHAR(128)	Same as SPECIFIC_SCHEMA
ROUTINE_NAME NVARCHAR(128)	Same as SPECIFIC_NAME
ROUTINE_TYPE NVARCHAR(20)	PROCEDURE for stored procedures, and FUNCTION for functions
MODULE_CATALOG NVARCHAR(128)	NULL (reserved for future use)
MODULE_SCHEMA NVARCHAR(128)	NULL (reserved for future use)
UDT_CATALOG NVARCHAR(128)	NULL (reserved for future use)
UDT_SCHEMA NVARCHAR(128)	NULL (reserved for future use)
UDT_NAME NVARCHAR(128)	NULL (reserved for future use)
DATA_TYPE NVARCHAR(128)	Data type returned by a function, or TABLE for table valued functions; NULL for stored procedures
CHARACTER_MAXIMUM_LENGTH INTEGER	The maximum length in characters for functions that return character type data; otherwise, NULL
CHARACTER_OCTET_LENGTH INTEGER	The maximum length in bytes for functions that return character type data; otherwise, NULL
COLLATION_CATALOG NVARCHAR(128)	The name of the database that contains the collation (sort order) for functions that return character type data; otherwise, NULL
COLLATION_SCHEMA NVARCHAR(128)	The ID of the user that owns the collation (sort order) for functions that return character type data; otherwise, NULL
COLLATION_NAME NVARCHAR(128)	The name of the collation (sort order) for functions that return character type data; otherwise, NULL
CHARACTER_SET_CATALOG NVARCHAR(128)	The name of the database that contains the character set for functions that return character type data; otherwise, NULL
CHARACTER_SET_SCHEMA NVARCHAR(128)	The ID of the owner (normally DBO) of the database that contains the character set for functions that return character type data; otherwise, NULL
CHARACTER_SET_NAME NVARCHAR(128)	The name of the character set for functions that return character type data; otherwise, NULL
NUMERIC_PRECISION SMALLINT	The precision of the value returned by functions that return numeric data; otherwise, NULL
NUMERIC_PRECISION_RADIX SMALLINT	The precision radix of the value returned by functions that return numeric data; otherwise, NULL
NUMERIC_SCALE SMALLINT	The scale of the value returned by functions that return numeric data; otherwise, NULL
DATETIME_PRECISION SMALLINT	The fractional precision of the seconds in a DATETIME value for functions that return a DATETIME value; otherwise, NULL
INTERVAL_TYPE NVARCHAR(30)	NULL (reserved for future use)
INTERVAL_PRECISION SMALLINT	NULL (reserved for future use)
TYPE_UDT_CATALOG NVARCHAR(128)	NULL (reserved for future use)
TYPE_UDT_SCHEMA NVARCHAR(128)	NULL (reserved for future use)
TYPE_UDT_NAME NVARCHAR(128)	NULL (reserved for future use)
SCOPE_CATALOG NVARCHAR(128)	NULL (reserved for future use)
SCOPE_SCHEMA NVARCHAR(128)	NULL (reserved for future use)
SCOPE_NAME NVARCHAR(128)	NULL (reserved for future use)
MAXIMUM_CARDINALITY BIGINT	NULL (reserved for future use)
DTD_DENTIFIER NVARCHAR(128)	NULL (reserved for future use)
ROUTINE_BODY NVARCHAR(30)	Always SQL
ROUTINE_DEFINITION NVARCHAR(4000)	The text of the Transact-SQL statements that define the stored procedure or function—if unencrypted; otherwise, NULL
EXTERNAL_NAME NVARCHAR(128)	NULL (reserved for future use)
EXTERNAL_LANGUAGE NVARCHAR(30)	NULL (reserved for future use)
PARAMETER_STYLE NVARCHAR(30)	NULL (reserved for future use)
IS_DETERMINISTIC NVARCHAR(10)	YES for deterministic functions; NO for for nondeterministic functions and stored procedures
SQL_DATA_ACCESS NVARCHAR(30)	READS for all functions and MODIFIES for all stored procedures
IS_NULL_CALL NVARCHAR(10)	Always YES
SQL_PATH NVARCHAR(128)	NULL (reserved for future use)
SCHEMA_LEVEL_ROUTINE NVARCHAR(10)	Always YES
MAX_DYNAMIC_RESULT_SETS SMALLINT	0 for functions and -1 for stored procedures
IS_USER_DEFINED_CAST NVARCHAR(10)	Always NO
IS_IMPLICITLY_INVOCABLE NVARCHAR(10)	Always NO
CREATED DATETIME	The date and time that the function or stored procedure was created
LAST_ALTERED INTEGER	The date and time that the function or stored procedure was last modified

To review the Transact-SQL code that defines the information schema ROUTINES view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema SCHEMATA View

The SCHEMATA view is a virtual table based on information from the MASTER.DBO.SYS-DATABASES and MASTER.DBO.SYSCHARSETS system tables that contains one row for each database accessible on the current MS-SQL Server. For example, if you execute the SELECT statement

 SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

the DBMS will return the information shown in Table 485.1 about each of the database managed by the DBMS:

Table 485.1: The Columns in the Information Schema SCHEMATA View

Column Name	Data Type	Description
CATALOG_NAME	NVARCHAR(128)	The name of the database
SCHEMA_NAME	NVARCHAR(128)	The user ID of the database owner-normally DBO
SCHEMA_OWNER	NVARCHAR(128)	Same as SCHEMA_NAME
DEFAULT_CHARACTER_SET_CATALOG	NVARCHAR(128)	Always NULL
DEFAULT_CHARACTER_SET_CATALOG	VARCHAR(3)	Always NULL
DEFAULT_CHARACTER_SET_SCHEMA	VARCHAR(3)	Always NULL
DEFAULT_CHARACTER_SET_NAME	SYSNAME	The name of the default character set

Understanding the Information Schema TABLE_CONSTRAINTS View

The TABLE_CONSTRAINTS view is a virtual table based on information from the SYSOBJECTS system table that contains one row for each table constraint in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

the DBMS will return the information shown in Table 486.1 about the table constraints on tables in the current database to which the login ID you used to connect with the server has at least one of the access privileges.

Table 486.1: The Columns in the Information Schema TABLE_CONSTRAINTS View

Column Name/Data Type	Description
CONSTRAINT_CATALOG NVARCHAR(128)	The name of the database that contains the CHECK, FOREIGN KEY, PRIMARY KEY, or UNIQUE constraint (for example, the name of the database that contains the table constraint)
CONSTRAINT_SCHEMA NVARCHAR(128)	The ID of the user that owns the table constraint
CONSTRAINT_NAME SYSNAME	The name of the table constraint
TABLE_CATALOG NVARCHAR(128)	The name of the database that contains the table on which the table constraint is defined
TABLE_SCHEMA NVARCHAR(128)	The ID of the user that owns the table with the table constraint
TABLE_NAME SYSNAME	The name of the table whose contents are constrained by the table constraint
CONSTRAINT_TYPE VARCHAR(11)	The type of table constraint: CHECK, FOREIGN KEY, PRIMARY KEY, or UNIQUE constraint
IS_DEFERRABLE VARCHAR(2)	Always NO
INITIALLY_DEFERRED VARCHAR(2)	Always NO

To display the information schema TABLE_CONSTRAINTS view information for all table constraints in the current database (even those on tables to which you have no access privileges), execute a SELECT statement such as:

SELECT db_name() 'Constraint Database',
 user_name(constraintobj.UID) 'Constraint Owner',
 constraintobj.name 'Constraint Name',
 db_name() 'Table Database',
 user_name(tableobj.UID) 'Table Owner',
 tableobj.name 'Table Name',
 CASE constraintobj.xtype
 WHEN 'C' THEN 'CHECK'
 WHEN 'UQ' THEN 'UNIQUE'
 WHEN 'PK' THEN 'PRIMARY KEY'
 WHEN 'F' THEN 'FOREIGN KEY'
 END 'Constraint Type',
 'NO' 'Is Deferrable',
 'NO' 'Initially Deferred'
FROM sysobjects constraintobj, sysobjects tableobj
WHERE tableobj.ID = constraintobj.parent_obj
 AND constraintobj.xtype IN ('C','UQ','PK','F')

Understanding the Information Schema TABLE_PRIVILEGES View

The TABLE_PRIVILEGES view is a virtual table based on information from the SYSPROTECTS and SYSOBJECTS system tables that contains one row for each privilege granted to or by a user on each table in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

the DBMS will return the information shown in Table 487.1 about the privileges on each table either granted by or granted to the login ID you used to connect with the server.

Table 487.1: The Columns in the Information Schema TABLE_PRIVILEGES View

Column Name	Data Type	Description
GRANTOR	NVARCHAR(128)	The user ID of the account granting the privilege
GRANTEE	NVARCHAR(128)	The user ID of the account to which the privilege was granted
TABLE_CATALOG	NVARCHAR(128)	The name of the table on which the privilege was granted
TABLE_SCHEMA	NVARCHAR(128)	The user ID of the table owner
TABLE_NAME	SYSNAME	The name of the table
PRIVILEGE_TYPE	VARCHAR(10)	The type of privilege granted
IS_GRANTABLE	VARCHAR(3)	YES if the grantee can grant the privilege to another user; NO if not

To review the Transact-SQL code that defines the information schema TABLE_PRIVILEGES view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema TABLES View

The TABLES view is a virtual table based on information from the SYSOBJECTS system table that contains one row for each table in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.TABLES

the DBMS will return the information shown in Table 488.1 about each table in the current database to which the login ID you used to connect with the server has at least one of the access privileges.

Table 488.1: The Columns in the Information Schema TABLES View

Column Name	Data Type	Description
TABLE_CATALOG	NVARCHAR(128)	The name of the database in which the table is defined
TABLE_SCHEMA	NVARCHAR(128)	The ID of the user that owns the table
TABLE_NAME	SYSNAME	The name of the table
TABLE_TYPE	VARCHAR(10)	The table type: either VIEW or BASE TABLE

To display the information schema TABLES view information for all tables in the current database (including those in tables on which you have no access privileges), execute a SELECT statement such as:

 SELECT db_name() 'Database/Catalog Name',
 user_name(sysobjects.UID) 'Table Owner',
 sysobjects.name 'Table Name',
 CASE sysobjects.xtype
 WHEN 'U' THEN 'BASE TABLE'
 WHEN 'V' THEN 'VIEW'
 END 'Table Type'
 FROM sysobjects
 WHERE sysobjects,xtype IN ('U','V')

Understanding the Information Schema VIEW_COLUMN_USAGE View

The VIEW_COLUMN_USAGE view is a virtual table based on information from the SYSOBJECTS, SYSDEPENDS, and SYSCOLUMNS system tables that contains one row for each column used in a view definition. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

the DBMS will return the information shown in Table 489.1 about each column in the current database that is used in a view definition to which the login ID you used to connect with the server has at least one of the access privileges.

Table 489.1: The Columns in the Information Schema VIEW_COLUMN_USAGE View

Column Name	Data Type	Description
VIEW_CATALOG	NVARCHAR(128)	The name of the database in which the view that uses the column is defined
VIEW_SCHEMA	NVARCHAR(128)	The ID of the user that owns the view that uses the column
VIEW_NAME	SYSNAME	The name of the view that uses the column
TABLE_CATALOG	NVARCHAR(128)	The name of the database in which the table that contains the column is defined
TABLE_SCHEMA	NVARCHAR(128)	The ID of the user that owns the table that contains the column
TABLE_NAME	SYSNAME	The name of the table that contains the column
COLUMN_NAME	SYSNAME	The name of the column used in the view

To display the information schema VIEW_COLUMN_USAGE view information on columns used in any of the views in the current database (including columns used in views on which you have no access privileges), execute a SELECT statement such as:

SELECT db_name() 'View Database/Catalog',
 user_name(viewobj.UID) 'View Owner',
 viewobj.name 'View Name',
 db_name() 'Table Database/Catalog',
 user_name(tableobj.UID) 'Table Owner',
 tableobj.name 'Table Name',
 syscolumns.name 'Column Name'
FROM sysobjects viewobj, sysobjects tableobj, sysdepends,
 syscolumns
WHERE viewobj.xtype = 'V'
 AND sysdepends.ID = viewobj.ID
 AND sysdepends.depID = tableobj.ID
 AND tableobj.ID = syscolumns.ID
 AND sysdepends.depnumber = syscolumns.colID

Understanding the Information Schema VIEW_TABLE_USAGE View

The VIEW_TABLE_USAGE view is a virtual table based on information from the SYSOBJECTS and SYSDEPENDS system tables that contains one row for each table used in a view definition. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

the DBMS will return the information shown in Table 490.1 about each table in the current database that is used in a view definition to which the login ID you used to connect with the server has at least one of the access privileges.

Table 490.1: The Columns in the Information Schema VIEW_TABLE_USAGE View

Column Name	Data Type	Description
VIEW_CATALOG	NVARCHAR(128)	The name of the database in which the view based on the table is defined
VIEW_SCHEMA	NVARCHAR(128)	The ID of the user that owns the view based on the table
VIEW_NAME	SYSNAME	The name of the view based on the table
TABLE_CATALOG	NVARCHAR(128)	The name of the database in which the table is defined
TABLE_SCHEMA	NVARCHAR(128)	The ID of the user that owns the table
TABLE_NAME	SYSNAME	The name of the table

To display the information schema VIEW_TABLE_USAGE view information on tables used in any of the views in the current database (including tables used in views on which you have no access privileges), execute a SELECT statement such as:

 SELECT db_name() 'View Database/Catalog',
 user_name(viewobj.UID) 'View Owner',
 viewobj.name 'View Name',
 db_name() 'Table Database/Catalog',
 user_name(tableobj.UID) 'Table Owner',
 tableobj.name 'Table Name'
 FROM sysobjects viewobj, sysobjects tableobj, sysdepends
 WHERE viewobj.xtype = 'V'
 AND sysdepends.ID = viewobj.ID
 AND sysdepends.depID = tableobj.ID

Understanding the Information Schema ROUTINE_COLUMNS View

The ROUTINE_COLUMNS view is a virtual table based on information from the SYSOBJECTS, SYSCOLUMNS, and MASTER.DBO.SYSCHARSETS system tables that contains one row for each column returned by a table-valued function. (Table-valued functions include FN_VIRTUALFILESTATS, which returns the I/O statistics for database files [including the log file], and FN_HELPCOLLATIONS, which lists each collation [sort order] supported by the MS-SQL Server.) When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS

the DBMS will return the information shown in Table 491.1 about the columns returned by table-valued functions in the current database available to the login ID you used to connect with the server.

Table 491.1: The Columns in the Information Schema ROUTINE_COLUMNS View

Column Name/Data Type	Description
TABLE_CATALOG NVARCHAR(128)	The name of the database that contains the table-valued function
TABLE_SCHEMA NVARCHAR(128)	The ID of the user that owns the table-valued function
TABLE_NAME NVARCHAR(128)	The name of the table-valued function
COLUMN_NAME NVARCHAR(128)	The name of the column returned by the table-valued function
ORDINAL_POSITION SMALLINT	The column's unique identification number within the table-valued function. ORDINAL_POSITION values are unique within each function but not across all functions.
COLUMN_DEFAULT NVARCHAR(4000)	The default value for a column
IS_NULLABLE VARCHAR(3)	YES if the column is allowed to hold a NULL value; NO if not
DATA_TYPE NVARCHAR(128)	The column's SQL data type
CHARACTER_MAXIMUM_LENGTH SMALLINT	The maximum length in characters for columns with binary, character, TEXT, or IMAGE data; otherwise, NULL
CHARACTER OCTET_LENGTH SMALLINT	The maximum length in bytes for columns with binary, character, TEXT, and IMAGE data; otherwise, NULL
NUMERIC_PRECISION TINYINT	The precision for columns with numeric type data; otherwise, NULL
NUMERIC_PRECISION_RADIX SMALLINT	The precision radix for columns with numeric type data; otherwise, NULL
NUMERIC_SCALE TINYINT	The scale for columns with numeric type data; otherwise, NULL
DATETIME_PRECISION SMALLINT	The subtype code for columns with DATETIME and INTEGER data; otherwise, NULL
CHARACTER_SET_CATALOG VARCHAR(6)	Always MASTER
CHARACTER_SET_SCHEMA VARCHAR(3)	Always DBO
CHARACTER_SET_NAME NVARCHAR(128)	The name of the character set for columns with TEXT or character type data; otherwise, NULL
COLLATION_CATALOG VARCHAR(6)	Always MASTER
COLLATION_SCHEMA VARCHAR(3)	Always DBO
COLLATION_NAME NVARCHAR(128)	The name of the sort order for columns with TEXT or character type data; otherwise, NULL
DOMAIN_CATALOG NVARCHAR(128)	The name of the database in which the user-defined data type was created if the column contains data of a user-defined data type; otherwise, NULL
DOMAIN_SCHEMA NVARCHAR(128)	The ID of the user that created the user-defined data type for columns that contain data of a user-defined data type; otherwise, NULL
DOMAIN_NAME NVARCHAR(128)	The name of the user-defined data type if the column contains data of a user-defined data type; otherwise, NULL

To review the Transact-SQL code that defines the information schema ROUTINE_COLUMNS view, perform the procedure outlined in Tip 493, "Using the MS-SQL Server Enterprise Manager to View the Contents of an Information Schema View."

Understanding the Information Schema VIEWS View

The VIEWS view is a virtual table based on information from the SYSOBJECTS and SYSCOMMENTS system tables that contains one row for each view in the current database. When you execute the query

 SELECT * FROM INFORMATION_SCHEMA.VIEWS

the DBMS will return the information shown in Table 492.1 about the views in the current database accessible to the login ID you used to connect with the server.

Table 492.1: The Columns in the Information Schema VIEWS View

Column Name/Data Type	Description
TABLE_CATALOG NVARCHAR(128)	The name of the database in which the view is defined
TABLE_SCHEMA NVARCHAR(128)	The user ID of the account that owns the view
TABLE_NAME NVARCHAR(128)	The name of the view
VIEW_DEFINITION NVARCHAR(4000)	The text of the view definition if the length of the view definition's text is less than or equal to 4,000 characters; otherwise, NULL
CHECK_OPTION VARCHAR(7)	CASCADE if the statement used to create the view included the WITH CHECK OPTION; otherwise, NONE
IS_UPDATABLE VARCHAR(2)	Always NO

To display the information schema VIEWS view information for all views in the current database (including those views on which you have no access privileges), execute a SELECT statement such as:

SELECT db_name() 'Database/Catalog Name',
 user_name(sysobjects.UID) 'View Owner',
 sysobjects.name 'View Name',
 CASE WHEN EXISTS
 (SELECT * FROM syscomments
 WHERE syscomments.ID = sysobjects.ID
 AND syscomments.colID > 1) THEN
 CONVERT(NVARCHAR(4000),NULL)
 ELSE syscomments.text
 END 'View Definition',
 CASE WHEN EXISTS
 (SELECT * FROM syscomments
 WHERE syscomments.ID = sysobjects.ID
 AND CHARINDEX('WITH CHECK OPTION',
 UPPER(syscomments.text)) > 0 THEN
 'CASCADE'
 ELSE 'NONE'
 END 'Check Option',
 'NO' 'Is Updateable'
FROM sysobjects, syscomments
WHERE sysobjects.xtype = 'V'
 AND sysobjects.ID = syscomments.ID
 AND syscomments.colID = 1

Using the MS SQL Server Enterprise Manager to View the Contents of an Information Schema View

As you learned from the sample code at the end of Tip 492, "Understanding the Information Schema VIEWS View," the MS-SQL Server stores the definition of a view the SELECT statement that returns the view's results set) in the TEXT column of the SYSOBJECTS table. Therefore, you can use a query (such as the one shown at the end of Tip 492) to display a view's definition. Alternatively, you can use the MS-SQL Server Enterprise manager to view the definition of a view by performing the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu, select the Microsoft SQL Server 2000 option, and click your mouse pointer on Enterprise Manager. Windows will start Enterprise Manager in the SQL Server Enterprise Manager application window.
3. Click your mouse pointer on the plus (+) to the left of SQL Server Group to display the list of MS-SQL Servers available on your network.
4. Click your mouse pointer on the plus (+) to the left of the SQL server that has the view whose definition you want to display (and perhaps modify). Enterprise Manager will display the Databases, Data Transformation, Management, Security, and Support Services folders for the SQL Server you selected.
5. Click your mouse pointer on the plus (+) to the left of the Databases folder to display the list of databases currently on the SQL server, and then click on the plus (+) to the left of the database that has the view whose definition you wish to display. For the current project, click on the plus (+) to the left of the Master folder.
6. To display the list of views in the database that you expanded in Tip 5, "Understanding the Relational Database Model," click your mouse pointer on the Views icon. Enterprise Manager will use its right pane to display the list of the views in the database similar to that shown in Figure 493.1.

   
   Figure 493.1: The MS-SQL Server Enterprise Manager listing views in the MASTER database

7. Double-click your mouse pointer on the view whose definition you want to display. For the current project, double-click your mouse pointer on COLUMN_PRIVILEGES (in the right pane). Enterprise Manager will display the text of the view definition in a View Properties dialog box similar to that shown in Figure 493.2.

   
   Figure 493.2: The View Properties dialog box

8. To exit the View Properties dialog box (and return to Enterprise Manager's main window, press the Esc (Escape) key on your keyboard (or click your mouse pointer on the OK button or the Cancel button at the bottom of the View Properties dialog box).

In addition to displaying the text of view, you can also use the Enterprise Manger to edit a view's definition. To edit a view, make any changes that you want in the Text area of the View Properties dialog box after you complete Step 7 of the preceding procedure. Then, instead of pressing the Esc key (or clicking your mouse pointer on the Cancel button), exit the View Properties dialog box by clicking your mouse pointer on the OK button. The Enterprise Manager will check the syntax of the view definition and (if the syntax is correct) store the updated view. (If the syntax is incorrect, the Enterprise Manager will display a message, which gives the source of the problem, so you can make the necessary corrections.)

Understanding the MS SQL Server System Database Tables

When you need information about a database managed by MS-SQL Server, you should use one or more of the Information Schema views (discussed in Tips 472–492), system stored procedures, and Transact-SQL statements and functions. By not working directly with the system tables, you avoid having to rewrite SQL code and prevent introducing errors when Microsoft changes the structure of a system table or the meaning of one or more of its columns from one version of MS-SQL Server to the next. However, MS-SQL Server does let you work directly with the system tables it uses to manage tables, indexes, foreign keys, users, permissions, and so on.

Table 494.1 describes each of the system tables that MS-SQL Server places in each database you create on an MS-SQL Server.

Table 494.1: The System Database Table That MS-SQL Server Maintains in Each Database It Manages

Table Name	Description
SYSCOLUMNS	Contains one row for each column in every table and view in the database. Each row's columns give the target column's attributes (such as name, location, and data type) and describe its behavior.
SYSCOMMENTS	Contains one or more rows for each CHECK constraint, DEFAULT constraint, rule, stored procedure, trigger, and view in the database. The table's TEXT column contains the SQL or Transact-SQL statement that defines the constraint, rule, stored procedure, trigger, or view. Since definitions can be up to 4MB in size while the TEXT column can hold only 4,000 bytes (characters), a single object may be described by more than one row in the SYSCOMMENTS table.
SYSCONSTRAINTS	Contains one row that describes the name, ID, and type for each constraint in the database. The table's ID column gives the name of the table that owns the constraint.
SYSDEPENDS	Describes the dependencies between objects (stored procedures, views, and triggers) and the objects (stored procedures, stored procedures, and views) they contain in their definitions.
SYSFILEGROUPS	Contains one row for each filegroup in the database.
SYSFILES	Contains one row for each physical disk file combined into filegroups and used to store the objects in a database. Each row's columns describe such things as the file's physical location on disk, its file name, its current size, the maximum size, and its growth rate.
SYSFOREIGNKEYS	Contains one row for each FOREIGN KEY constraint used in a table definition.
SYSFULLTEXTCATALOGS	Contains one row for each full-text index created in the database.
SYSINDEXES	Contains one row for each index, table, and BLOB in the database. The columns in each row describe such things as the location of the object's first page of data, the row size, the number of keys, row count, the name of the table, and the name of the key columns.
SYSINDEXKEYS	Contains one row with the table ID, index ID, column ID, and ordinal position of the column in the index for each column in every index in a database.
SYSMEMBERS	Contains one row that gives the user ID, (Windows) group ID, and role ID for each member of every role and (Windows) group in the database.
SYSOBJECTS	Contains one row for each object (constraint, default, index, log, rule, stored procedure, table, and so on) in the database.
SYSPERMISSIONS	Contains one row with the object ID, grantor, and grantee for each permission granted or denied each user or role on a database object.
SYSPROTECTS	Contains one row for each permission granted or denied on database objects by executing GRANT and DENY statements. The columns in each row describe the type of privilege granted or denied, the user or role ID, the object ID, the grantor, and (for SELECT and UPDATE permissions) a bitmap to the columns to which the permission (or denial) applies.
SYSREFERENCES	Contains one row for each FOREIGN KEY constraint in every table in the database.
SYSTYPES	Contains one row for each system-supplied and user-defined data type in the database.
SYSUSERS	Contains one row for each Windows user, Windows group, MS-SQL Server user, and MS-SQL Server role in the database.

Defining the Physical Location of the Database

When you used the CREATE DATABASE statement in Tip 41, "Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log," you specified the physical location for the file in which the DBMS is to place all database objects in the FILENAME clause as:

 CREATE DATABASE SQLTips
 ON (NAME = SQLTips_data,
 FILENAME = 'c:mssql7dataSQLTips_data.mdf',
 SIZE = 10, FILEGROWTH = 1MB)
 LOG ON (NAME = 'SQLTips_log',
 FILENAME = 'c:mssql7dataSQLTips_log.ldf',
 SIZE = 3, FILEGROWTH = 1MB)

Thus, in the current example, the DBMS will place each table you create in the file named SQLTIPS_DATA.MDF in the MSSQL7DATA folder on the server's C drive. (The server will expand the size of the SQLTIPS_DATA.MDF beyond the 10MB initial size, 1MB at a time, as necessary, to accommodate the storage space required to hold the data in the tables you create and fill.)

To split the single large database (MDF) file into multiple smaller files or to allow it to span disk volumes, the MS-SQL Server lets you define one more physical data files in filegroups. The MS-SQL Server will then grow the individual, physical files in each filegroup as necessary to store its table data and will let you specify the filegroup in which to place the table (and its data) as part of the CREATE TABLE statement you use to create the table.

For example, the CREATE DATABASE statement

CREATE DATABASE company_db
ON PRIMARY (NAME = company_data,
 FILENAME = 'c:mssqldataco_data.mdf',
 SIZE = 10, FILEGROWTH = 1MB),
FILEGROUP marketing (NAME = marketing data,
 FILENAME = 'c:mssqldatamkt_data.mdf,
 SIZE = 10, FILEGROWTH = 1MB),
FILEGROUP office (NAME = office_data,
 FILENAME = 'd:mssqldataofce_data.mdf',
 SIZE = 10, FILEGROWTH = 1MB)
LOG ON (NAME = 'co_db_log',
 FILENAME = 'c:mssgldatadb_log.ldf',
 SIZE = 3, FILEGROWTH = 1MB)

will create three filegroups: PRIMARY, MARKETING, and OFFICE. You can then use a CREATE TABLE statement such as

CREATE TABLE call_history
(call_time DATETIME,
 hangup_time DATETIME,
 called_by VARCHAR(3),
 disposition VARCHAR(4)) ON marketing

to place the CALL_HISTORY table in the MARKETING filegroup on the C drive, and a CREATE TABLE statement such as

CREATE TABLE orders
(customer_ID INTEGER,
 order_date DATETIME,
 order_total MONEY,
 date_shipped DATETIME) ON office

to place the ORDERS table in the OFFICE filegroup on the D drive.

Distributing tables within a database across two or more drives lets you take advantage of parallel disk I/O operations. With the CALL_HISTORY table on one drive and the ORDERS table on another, the DBMS can issue simultaneous writes to add a row to the CALL_HISTORY table while adding a row to the ORDERS table. If both tables are on the same drive, the system must complete one I/O operation before starting the other.

Note 

Each DBMS product has its own way for specifying the physical location of database files and of the tables within each database. As such, check your system manual for the statement syntax required by your DBMS. The important thing to understand now is that all DBMS products allow the DBA to manage the physical location of the database file(s) and to select the file in which the DBMS is to place individual database tables.

Adding Files and Filegroups to an Existing Database

In Tip 41, "Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log," you learned how to use the CREATE DATABASE statement to create an MS-SQL Server database with multiple filegroups and multiple files distributed across two physical disk drives. If you are managing an SQL server in a typical business environment, database storage requirements will eventually exceed the initial available storage capacity as the company collects and retains marketing, sales, and customer data. Moreover, advances in technology and additional funds available to your department will make it possible for you to bring new, larger-capacity, and faster storage solutions online. Fortunately, the MS-SQL Server provides the Transact-SQL ALTER DATABASE command, which lets you add additional drives to those already in use to hold DBMS files.

For example, if you created the SQLTips database in Tip 41 and you later add a new hard drive (or RAID disk array) with the logical drive letter D, you can use an ALTER DATABASE statement such as

 ALTER DATABASE SQLTips
 ADD FILE (NAME = SQLTips_data2,
 FILENAME = 'd:mssqldataSQLTips_data2.mdf',
 SIZE = 30, FILEGROWTH = 5MB)
 ALTER DATABASE SQLTips
 ADD LOG FILE (NAME = 'SQLTips_log2',
 FILENAME = 'd:mssqldataSQLTips_log2.ldf',
 SIZE = 3, FILEGROWTH = 1MB)

to allow the database and its log file to grow on the D drive. (After executing the ALTER DATABASE statement, which adds a new drive, the DBMS will automatically start using the new [disk] file space and will split data in individual tables across the two disk files as necessary.)

In addition to adding new files to the database, you can also use the ALTER DATABASE statement to add new filegroups and add files to existing filegroups. Suppose, for example, that you created the COMPANY_DB database in Tip 495, "Defining the Physical Location of the Database." If you execute an ALTER DATABASE statements such as

 ALTER DATABASE company_db
 ADD FILE (NAME = marketing_data2,
 FILENAME = 'e:mssqldatamkt_data.mdf',
 SIZE = 10, FILEGROWTH = 1MB)
 TO FILEGROUP marketing
 ALTER DATABASE company_db ADD FILEGROUP multimedia
 ALTER DATABASE company_db
 ADD FILE (NAME = blobs,
 FILENAME = 'i:mssqldatalob_data.mdf',
 SIZE = 100, FILEGROWTH = 25MB)
 TO FILEGROUP multimedia

then the MS-SQL Server will begin using a second file (MARKETING_DATA2, in the current example) to store tables created in the MARKETING filegroup. Moreover, you can create tables with TEXT and IMAGE (BLOB) data on the I drive in the new filegroup MULTIMEDIA.

Note 

By placing tables with BLOB data on a separate drive, you can reduce bottlenecks that occur when users execute queries that return a small amount of data and have to wait for the hardware to return the data in a BLOB being retrieved by another user. Remember, BLOB (TEXT and IMAGE) data can be exceptionally large-hundreds of megabytes or several gigabytes in length. Therefore, BLOB data can take much longer to retrieve than a result set for a typical query, which will normally run a few thousand or a few tens of thousands of bytes.

Using the MS SQL Server Enterprise Manager to Add Files and Filegroups to an Existing Database

When you need to change the files or filegroups in a database, you can use the ALTER DATABASE statement (as you learned to do in Tip 496, "Adding Files and Filegroups to an Existing Database"), or you can use the MS-SQL Server Enterprise Manager. For example, to add the file BLOBS in the filegroup MULTIMEDIA to the SQLTips database, perform the following steps:

1. Click your mouse pointer on the Start button. Windows will display the Start menu.
2. Move your mouse pointer to Programs on the Start menu, select the Microsoft SQL Server 2000 option, and click your mouse pointer on Enterprise Manager. Windows will start Enterprise Manager in the SQL Server Enterprise Manager application window.
3. Click your mouse pointer on the plus (+) to the left of Microsoft SQL Servers, and then click on the plus (+) to the left of SQL Server Group to display the list of MS-SQL Servers available on your network.
4. Click your mouse pointer on the plus (+) to the left of the SQL server that has the database whose files or filegroups you want to modify. Enterprise Manager will display the Databases, Data Transformation, Management, Security, and Support Services folders for the SQL Server you selected.
5. Click your mouse pointer on the folder to the left of Databases. Enterprise Manager will display the databases being managed by the SQL server (which you selected in Step 4) in Enterprise Manager's right pane.
6. Double-click your mouse pointer on the icon of the database whose files or filegroups you want to change. For the current project, double-click your mouse pointer on the SQLTips database icon. Enterprise Manager will display the SQL Properties dialog box.
7. Click your mouse pointer on the Data Files tab to display the current list of files and file-groups in the database, similar to that shown in Figure 497.1.

   
   Figure 497.1: The MS-SQL Server Enterprise Manager database Properties Dialog box, Data Files tab

8. To add a new file, click your mouse pointer in the blank field at the bottom of the File Name column, and type the name of the new logical file name into the field. For the current project, enter BLOBS into the File Name field.
9. After you complete Step 8, Enterprise Manager will enter a default physical file location (and name) into the Location field. For the current project, accept the default. However, you can change the physical file name or its location by clicking your mouse pointer on the Location column and then changing the pathname in the Location field.

   Note 

   The folders in the pathname you enter into the Location field must already exist-Enterprise Manager will not create them for you. Therefore, if you want to place the physical database file into a new folder, use the MS-DOS MD command or Windows Explorer to create the new folder before entering its pathname into the Location field.

10. By default, Enterprise Manager will set the initial space allocation for the file to 1MB. To change the initial database file size, click your mouse pointer in the Space Allocated (MB) column and enter the initial size of the database file in megabytes. For the current project, click your mouse pointer on the Space Allocated (MB) column and then enter 5 into the Space Allocated (MB) field.
11. By default, Enterprise Manager assigns new files to the default filegroup (normally PRIMARY). To assign the new file to another filegroup, click your mouse pointer on the Filegroup column, and then enter the name of the filegroup into the Filegroup field. For the current project, enter MULTIMEDIA into the filegroup field.

After you complete Step 11, click your mouse pointer on the OK button. Enterprise Manager will create the new database file and filegroup, and then return to the main application window.

Understanding the Advantages and Disadvantages of Using Single and Multi database Architectures

In a single-database architecture (such as that used by a DB2 or ORACLE SQL Server), the DBMS supports (and manages) a single database. (The database may consist of multiple physical files, but all database objects exist in a single logical database file.) As shown in Figure 498.1, sets of individual tables in the database are often owned by the user ID of the person or the group ID (role) responsible for the application used to enter and maintain the data in a given group of tables.

Figure 498.1: Single-database SQL server architecture

In the current example, user ID JAMES owns the CALLS, APPOINTMENTS, and SALES tables. The OFFICE role (or user group) owns the ORDERS and CUSTOMERS tables. User ID LINDA owns the EMPLOYEES, TIMECARDS, OFFICES, and PAYROLL tables.

The main advantage of a single-database architecture is that all tables reside in the same database and can therefore easily reference each other. For example, both the SQL-89 and SQL-92 standards require the DBMS to support FOREIGN KEY references in the CUSTOMERS table (in Figure 498.1) that relate CUSTOMERS rows with rows in the EMPLOYEES and OFFICES tables. In short, relating tables within a single-database (even those owned by different IDs) is a standard SQL feature. Therefore, in a single-database system, you will have no problems linking a customer (in the CUSTOMERS table) with the salesperson (in the EMPLOYEES table) that made the sale and the office (in the OFFICES table) responsible for servicing the customer. Moreover, after the owners of the various tables GRANT the necessary privileges, users and applications can easily run queries that combine data from tables maintained by various applications (and departments).

Gaining access to database tables on a single-database server is a simple process. After you log in to the SQL server, you need not select a database, since there is only one. For example, if JAMES wants to display data from the CUSTOMERS table (owned by OFFICE) and EMPLOYEES table (owned by LINDA), he needs to specify only the table owner along with the table name in a SELECT statement such as:

 SELECT c.f_name, c.l_name, c.salesperson, e.f_name, e.l_name
 FROM office.customers c, linda.employees e
 WHERE c.salesperson = e.emp_ID

The main disadvantage of using the single-database architecture is that the database will grow very large with many complex relationships in a short amount of time. In addition to the data file size expanding as applications and users fill tables with data, the number of tables will also increase at an exponential rate as each user adds the tables the user needs to support applications. Then, as the number of tables and the database file size grows, performing backups, recovering data, and analyzing and tuning performance will require a full-time DBA.

A multi-database architecture, as shown in Figure 498.2, lets you group tables (by owner) within multiple database files. Instead of keeping tables for multiple applications in the same database file (as is the case in a single-database architecture), each database will normally support a single (or set of related) applications. When you add a new, different application, you will typically create a new database to hold that tables used to support it.

Figure 498.2: Multi-database SQL server architecture

The main advantage of a multi-database architecture (over the single-database architecture) is that the multi-database architecture lets you divide the "database" management tasks into smaller pieces. Instead of having a single DBA, each person responsible for an application will be responsible for acting as the DBA for the department's database. Then, when the organization adds a new department, the department's programmers can deploy a new database to support their applications without having to restructure or change existing databases. Moreover, the number of tables and the size of the database file for each database will remain small (as compared to the table count and database file size in a single-database architecture).

Gaining access to database tables on a multi-database server is slightly more complex than on single-database server. After you log in to the SQL server, you must either select a database you want to use or (if the server supports it) include the database name as well as the object owner's ID in each fully qualified name. For example, if JAMES wants to display data from the CUSTOMERS table (owned by OFFICE) and the EMPLOYEES table (owned by LINDA), he must specify both the database and the table owner's ID in a SELECT statement such as:

SELECT c.f_name, c.l_name, c.salesperson, e.f_name, e.l_name
FROM office.office.customers c, hr.linda.employees e
WHERE c.salesperson = e.emp_ID

Unfortunately, many SQL servers that support the multi-database architecture do not let you set up relationships and do not support queries among tables in different databases. As a result, it may become necessary to duplicate tables in multiple databases or to combine several databases (with related data) into a single database, thereby incurring the disadvantages of a single-database architecture.

Simplifying Multi table Queries by Creating a Joined Table View

After working with SQL for a while, writing multi-table joins will become second nature, since almost all queries involve extracting and relating data from two or more tables. When you find that you need the same combination of data from several tables on an ongoing basis, creating and using a joined table view is more convenient than retyping the same complex query repeatedly. Moreover, many managers who are unfamiliar with SQL and who don't have the time to learn it thoroughly may know how to write only simple, single-table queries. As such, you will often get requests to de-normalize the database so that the information management needs resides in a single table instead of being spread across multiple related tables which managers must join to get the summary reports they need.

Rather than denormalize the database and risk database corruption due to the modification anomalies you learned about in Tips 200–203, create a view or virtual table instead. By using a view to combine data from multiple tables, the manager will have a single (albeit virtual) table with data from several tables and will be able to use a single-table SELECT statement to get the desired information from the database.

Suppose, for example, that the marketing manager needs to generate a productivity report for each phone representative in the sales department. The manager will need the employee name from the EMPLOYEES table, the count of appointments and order volume from the APPOINTMENTS table, and the number and volume of delivered sales from the CUSTOMERS table. After you create a view to join the three tables (EMPLOYEES, SALES, and CUSTOMERS), such as

 CREATE VIEW vw_productivity (ID, Phone_Rep_Name,
 Appointments, Sales, Sales_Volume, Deliveries,
 Total_Revenue) AS
 SELECT emp_ID, f_name + ' ' + l_name,
 (SELECT COUNT(*) FROM appointments
 WHERE phone_rep = emp_ID),
 (SELECT COUNT(*) FROM appointments
 WHERE phone_rep = emp_ID AND disposition = 'Sold')
 COALESCE((SELECT SUM(order_total)
 FROM appointments WHERE phone_rep = emp_ID),0),
 (SELECT COUNT(*)
 FROM customers WHERE phone_rep = emp_ID),
 COALESCE((SELECT SUM(contract_total)
 FROM customers WHERE phone_rep = emp_ID),0)
 FROM employees
 WHERE department = 'Marketing' AND status = 'A'

the marketing manager can execute a single-table query

SELECT * FROM vw_productivity ORDER BY total_revenue DESC

to get a list of active phone representatives in descending order by the amount of revenue each has generated for the company.

Understanding the WITH SCHEMABINDING Clause in a CREATE VIEW Statement

One of the problems with creating a view based on columns from one or more tables is that a table's owner may alter the table definition and drop a column used in the view, or perhaps drop the table altogether. Unfortunately, neither the ALTER TABLE statement nor the DROP TABLE statement will generate any errors or warnings to let you know when you are dropping a column or table used in a view. As such, if you execute the ALTER TABLE statement

 ALTER TABLE customers DROP COLUMN contract_total

the DBMS will remove the CONTRACT_TOTAL column from the CUSTOMERS table used in Tip 499, "Simplifying Multi-table Queries by Creating a Joined Table View," without warning or error. However, when the marketing manager attempts to execute a SELECT statement on the VW_PRODUCTIVITY view, the DBMS will abort the query with error messages similar to:

Server: Msg 207, Level 16, State 3, Procedure Productivity,
 Line 4.
Invalid column name 'contract total'.
Server: Msg 4413, Level 16, State 1, Linel
Could not use view or function 'vw_productivity' because of
 binding errors.

The manager will most likely fire off an unpleasant e-mail to the DBA, who will have to figure out who dropped the column, why, and what can be done to fix the problem. For infrequently used views, the errors caused by table modifications may not manifest themselves for several months.

Fortunately, the CREATE VIEW statement lets you use the WITH SCHEMABINDING clause to prevent a table owner (or other user with the proper privilege) from dropping a table used in a schema-bound view and to abort any ALTER TABLE statements that affect the view definition. For example, if you create the view

CREATE VIEW vw_sales (ID, Phone_Rep_Name, Appointments,
 Sales, Sales_Volume) WITH SCHEMABINDING AS
SELECT emp_ID, f_name + ' ' + l_name,
 (SELECT COUNT(*) FROM frank.appointments
 WHERE phone_rep = emp_ID),
 (SELECT COUNT(*) FROM frank.appointments
 WHERE phone_rep = emp_ID AND disposition = 'Sold')
 COALESCE((SELECT SUM(order_total)
 FROM frank.appointments WHERE phone_rep = emp_ID),0)
FROM linda.employees
WHERE department = 'Marketing' AND status = 'A'

the DBMS will not allow FRANK to DROP the APPOINTMENTS table or LINDA to DROP the EMPLOYEES table without first dropping the VW_SALES view or changing the view definition so that it does not include the table to be dropped. Moreover, no user will be able to execute an ALTER TABLE statement on either table that drops a column used in the view.

Note 

When using the WITH SCHEMABINDING clause, the SELECT statement(s) used in the view must include two-part names for tables, views, and user-defined functions.