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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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:

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.