Microsoft SQL Server 2000 System Catalog

Microsoft SQL Server 2000 provides several ways of obtaining system information — through INFORMATION_SCHEMA views and/or through system stored procedures and functions.

Tip 

One of the ways to obtain system information about the Microsoft SQL Server 2000 is direct querying of the system tables — that is, tables and views that contain information about the current database (e.g., sysobjects, sysindexes, sysusers, etc. — up to a total of 19 tables). Those stored in Master database contain information about the RDBMS itself. While it is possible — for a user with sufficient privileges — to query these views and tables, Microsoft strongly discourages such practice, stating that the system tables are for the exclusive use of the SQL Server itself, and that the names and structures might change in future releases (and they certainly do — each version of the SQL Server brings new tables, old ones are dropped, and the names get changed). Our advice is to resist the temptation of using this "backdoor" but instead use legitimate interfaces to obtain information.

MS SQL Server 2000 INFORMATION_SCHEMA Views

The INFORMATION_SCHEMA system views were first introduced in Microsoft SQL Server 7.0 for SQL92 standard compliance. These views are defined in each database contained in RDBMS. They are based on system tables, which should not be queried directly (see Tip above).

Table 13-8 contains information about MS SQL Server 2000 INFORMATION_SCHEMA views.

Table 13-8: Microsoft SQL Server 2000 INFORMATION_SCHEMA Views

INFORMATION_SCHEMA View

Contains Information About

CHECK_CONSTRAINTS

All check constraints for the current database; based on sysobjects and syscomments system tables. Shows only constraints for which the current user has permission.

COLUMN_DOMAIN_USAGE

All user-defined data types; based on sysobjects, syscolumns, and systypes system tables. Shows only data-types for which the current user has permission.

COLUMN_PRIVILEGES

All privileges either granted to or by the user; based on sysprotects, sysobjects, and syscolumns system tables. Shows only privileges for which the current user has permission.

COLUMNS

Every column in every table in the current database accessible to the user; based on sysobjects, spt_data type_info, systypes, syscolumns, syscomments, sysconfigures, and syscharsets system tables. Shows only columns for which the current user has permission.

CONSTRAINT_COLUMN_USAGE

Every column in the database that has constraint put on it; based on sysobjects, syscolumns, and systypes system tables. Shows only columns for which the current user has permission.

CONSTRAINT_TABLE_USAGE

Each table in the current database that has a constraint defined on it; based on sysobjects system table. Shows only tables for which the current user has permission.

DOMAIN_CONSTRAINTS

Every user-defined data type that has a rule bound to it; based on sysobjects and systypes system tables. Shows only types for which the current user has permission.

DOMAINS

User-Defined Types declared in the current database; based on spt_data type_info, systypes, syscomments, sysconfigures, and syscharsets system tables. Shows only UDT(s) for which the current user has permission.

KEY_COLUMN_USAGE

Each column declared as a key (primary or foreign) for every table in the current database; based on sysobjects, syscolumns, sysreferences, spt_values, and sysindexes system tables. Shows only key columns for which the current user has permission.

PARAMETERS

Each parameter defined for a user-defined stored procedure or function; also shows return parameter for a function; based on sysobjects and syscolumns system tables. Shows only information for the stored procedures and functions for which the current user has permission.

REFERENTIAL_CONSTRAINTS

Each foreign key constraint defined in the current database; based on sysreferences, sysindexes, and sysobjects system tables. Shows only constraints for which the current user has permission.

ROUTINES

Every stored procedure or function defined in the current database; based on sysobjects and sysscolumns system tables. Shows only functions for which the current user has permission.

ROUTINE_COLUMNS

User functions that are table-valued (containing a SELECT statement); based on sysobjects and syscolumns system tables. Shows only functions for which the current user has permission.

SCHEMATA

All databases accessible to a user; based on sysdatabases, sysconfigures, and syscharsets system tables. Shows only databases for which the current user has permission.

TABLE_CONSTRAINTS

Table constraints defined in the current database; based on sysobjects system table. Shows only constraints for which the current user has permission.

INFORMATION_SCHEMA View

Contains Information About

TABLE_PRIVILEGES

Each table privilege either granted to or by the user; based on sysobjects and sysprotects system tables. Shows only constraints for which the current user has permission.

TABLES

Every table defined in the current database; based on sysobjects system table. Shows only tables for which the current user has permission.

VIEW_COLUMN_USAGE

Each column used in a view definition; based on sysobjects and sysdepends system tables. Shows only columns for which the current user has permission.

VIEW_TABLE_USAGE

Tables used as a base table for the views; based on sysobjects and sysdepends system tables. Shows only tables for which the current user has permission.

VIEWS

Views in the current database accessible to a user; based on sysobjects and syscomments system tables.

The INFORMATION_SCHEMA views are queried just like any other view or table in the database with one important distinction: the view name must be preceded with the INFORMATION_SCHEMA qualifier. Each view contains several columns, and search conditions can be specified on the columns these views contain (for the full list of the INFORMATION_SCHEMA views columns, please refer to the vendor documentation).

Here are several examples illustrating the use of the INFORMATION_SCHEMA views in SQL Server 2000.

The following query returns information about every column in the ACME database. (The results displayed were shortened somewhat, because the query returns all rows for each table in the database, including 19 system tables, used by the SQL Server to keep track of the objects; only 4 columns were requested, since the view contains a total of 23 columns.)

SELECT table_name,   column_name, column_default, data_type FROM information_schema.columns   TABLE_NAME COLUMN_NAME DATA_TYPE ---------------- --------------- -------------   ORDER_LINE ORDLINE_ID_N int ORDER_LINE ORDLINE_ORDHDRID_FN int . . . . . . . .   . SHIPMENT SHIPMENT_ID_N int SHIPMENT SHIPMENT_BOLNUM_S varchar SHIPMENT   SHIPMENT_SHIPDATE_D datetime SHIPMENT   SHIPMENT_ARRIVDATE_D datetime . . . . . . . . . . . . STATUS STATUS_ID_N int   STATUS STATUS_CODE_S char STATUS STATUS_DESC_S varchar . . . . . . . . .   v_customer_totals customer_name varchar v_customer_totals order_number varchar   . . . . . . . . . SALESMAN SALESMAN_NAME_S varchar SALESMAN SALESMAN_STATUS_S   char (112 row(s) affected)

For example, if you would like to query the table for information only on table SALESMAN, then the query should look like the following:

SELECT table_name,   column_name, column_default, data_type FROM information_schema.columns WHERE   table_name = 'SALESMAN' TABLE_NAME COLUMN_NAME DATA_TYPE ----------------   --------------- ------------- SALESMAN SALESMAN_ID_N int SALESMAN   SALESMAN_CODE_S varchar SALESMAN SALESMAN_NAME_S varchar SALESMAN   SALESMAN_STATUS_S char (4 row(s) affected)

It is possible to join INFORMATION_SCHEMA views just as you would the "regular" views or tables, or use any other SQL expressions. For example, to find out what tables do not have constraints declared on their columns, the following query can be used:

SELECT tbl.table_name,   tbl.table_type FROM information_schema.tables tbl WHERE tbl.table_name NOT IN   (SELECT table_name FROM information_schema.constraint_column_usage) TABLE_NAME   TABLE_TYPE ---------------- --------------- sysconstraints VIEW syssegments   VIEW v_contact_list VIEW   v_customer_status VIEW v_customer_totals VIEW . . . . . . v_fax_number VIEW   v_phone_number VIEW v_wile_bess_orders VIEW (10 row(s)   affected)

As it turns out, every single table in the ACME database has some type of constraint (at least primary or foreign keys) declared for it, but views do not. Note that the final resultset includes two system views.

Microsoft SQL Server system stored procedures

There are many categories of system stored procedures supplied with Microsoft SQL Server 2000, depending on the purpose and tasks performed. Only catalog procedures are discussed at relative length in this chapter.

Table 13-9 contains information about MS SQL Server 2000 INFORMATION_SCHEMA system stored procedure categories.

Table 13-9: Microsoft SQL Server 2000 System Stored Procedure Categories

Category

Description

Active Directory Procedures

Used to register SQL Server with Microsoft Windows Active Directory.

Catalog Procedures

Returns information about system objects; implements ODBC data dictionary functions.

Cursor Procedures

Implements cursor functionality (see Chapter 14 for more information on cursors).

Database Maintenance Plan Procedures

Used to set up and perform core database maintenance tasks.

Distributed Queries Procedures

Used to execute and manage Distributed Queries.

Full-Text search Procedures

Pertains to the Full-Text search capabilities; requires special setup to use full text indices.

Log Shipping Procedures

Used in configuration and administration of the SQL Server Log shipping.

OLE Automation Procedures

Allows for using ActiveX (OLE) automation objects to be used within standard Transact SQL code.

Replication Procedures

Used to set up and manage replication process.

Security Procedures

Security management procedures.

SQL Mail Procedures

Integrates e-mail operations with SQL Server.

SQL Profiler Procedures

Used by the SQL Profiler add-on to monitor SQL Server performance.

SQL Server Agent Procedures

Used by SQL Server Agent to manage scheduled activities.

System Maintenance Procedures

Used for the entire RDBM system maintenance tasks.

Web Assistant Procedures

Used by SQL Server Web assistant to publish information with Internet Information Server.

XML Procedures

Used to perform operation on XM documents (see Chapter 17 on XML/SQL integration).

General Extended Procedures

Refers to the stored procedures capable of accessing resources of the underlying Windows OS.

The Catalog stored procedures will be the focus of this chapter, and we will look into some of the most useful as examples. The Microsoft SQL Server 2000 lists 12 stored procedures that provide information about the system. You can use these procedures directly from the command-line interface of OSQL, from SQL Query Analyzer, or from a client application accessing the SQL Server through any of the programming interfaces provided. Initially, the purpose of these procedures was to implement ODBC data dictionary functions to isolate ODBC applications from possible changes to the SQL Server system tables structure.

Tip 

The use of system stored procedures is unique to Microsoft SQL Server and Sybase adaptive Server, since they both have their origins in a joint project initiated by Microsoft, Sybase, and Ashton-Tate in 1988, whereas INFORMATION_SCHEMA views were introduced starting with version 7.0 of the SQL Server.

The information returned by these stored procedures usually pertains to the current database; in the case of the server scope, it pertains to the databases in the current installation. In addition to that, these procedures might return information about objects accessible through a database gateway (i.e., registered as legitimate data sources with the SQL Server). Since the details of implementation differ from data source to data source, some information might be unavailable (for example, Oracle's concept of a "database" is totally different from that of MS SQL Server, so sp_database procedure will not be able to return it).

Tip 

Every procedure listed in the following sections accepts none, one or more arguments; we are going to specify only the most basic of the arguments, often only required arguments in our examples, as a full listing will require many more pages and really belongs in a Microsoft SQL Server book.

sp_tables

This procedure returns a list of tables and views (and some related information about them) in the current database. The format of the returned data set is given in Table 13-10.

Table 13-10: Result Set Returned by sp_tables System Stored Procedures

Column Name

Description

TABLE_QUALIFIER

Table qualifier name. In the SQL Server, this column represents the database name. This field can be NULL.

TABLE_OWNER

Contains the table owner name, usually DBO (database owner).

TABLE_NAME

Contains the name of the table.

TABLE_TYPE

Specifies the type of the object: a table, system table, or a view.

REMARKS

Comments can be added to the table, but usually this column returns NULL.

The procedure accepts a number of optional parameters that correspond to the column name in the Table 13-10; if omitted, every single table and view in the current database will be listed. Here is an example:

1>  exec sp_tables 2> go TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE  REMARKS --------------- ----------- ----------- ----------- ------ acme dbo  syscolumns SYSTEM TABLE NULL . . . . . . . . . . . . . . . acme dbo ADDRESS  TABLE NULL acme dbo CUSTOMER TABLE NULL acme dbo DISCOUNT TABLE NULL . . . . .  . . . . . . . . . . acme dbo v_fax_number VIEW NULL

Note that this procedure lists all the system tables and views in the context of the current database.

Note 

If the server property ACCESSIBLE_TABLES returned by the system stored procedure sp_server_info (see later in the chapter) is Y, then, only tables that the current user has permission to access will be returned.

sp_columns

This procedure returns all the columns (and column-specific information) accessible in the current session. Here is a basic example:

exec sp_columns  'CUSTOMER'
Cross-References 

The procedure is equivalent to the ODBC API SQL columns. See Chapter 16 for more details on ODBC.

sp_server_info

Executing the procedure without any parameters returns 29 rows, while specifying the attribute ID gives you exactly one row of data. The information returned represents a subset of the data provided by an ODBC call SQLGetInfo.

1>  exec sp_server_info 2> go attribute_id attribute_name attribute_value  ------------ -------------------------- --------------------- 1 DBMS_NAME  Microsoft SQL Server . . . . . . . . . 12 MAX_OWNER_NAME_LENGTH 128 13  TABLE_LENGTH 128 . . . . . . . . . 112 SP_RENAME Y 113 REMOTE_SPROC Y 500  SYS_SPROC_VERSION 8.00.178 (29 row(s) affected)

This procedure is capable of returning information about non-SQL Server RDBMS, provided that a database gateway or linked data source is specified.

sp_databases

This procedure returns a list of all SQL Server databases. Here is an example of the returned data, on the MS SQL Server installation performed as described in Appendix D:

1> exec sp_databases  2> go DATABASE_NAME DATABASE_SIZE REMARKS -----------------------  ------------- -------------- acme 2176 NULL master 14208 NULL model 1280 NULL  msdb 14336 NULL Northwind 4352 NULL pubs 2560 NULL tempdb 8704 NULL  

The data returned are contained in the table SYSDATABASES of the Master database. It has no corresponding ODBC functions.

start sidebar
Getting Help

One of the most useful procedures in obtaining information about any database object is the sp_help<> group of stored procedures.

Stored Procedure

Description

sp_help

Returns information about database objects in the current database.

sp_helpuser

Returns information about database users, database roles, etc.

sp_helptrigger (<tabname>)

Returns information about triggers defined on the specified table for the current database.

sp_helprotect

Returns information about user permissions in the current database.

sp_helpindex

Returns information about the indices on a table or view.

SP_HELP is probably the most universal of the lot. If used without any arguments, it will returned information about every single database object (table, view, stored procedure, index, default, etc.) listed in the sysobjects table of the current database.

1> exec sp_help 2> go Name Owner Object_type ---------------------- ------------- ----------------- sysconstraints dbo view . . . v_fax_number dbo view v_phone_number dbo view . . . . . . . . . ADDRESS dbo user table CUSTOMER dbo user table . . . . . . . . . sysindexes dbo system table . . . . . . . . . PK_ADDRPRIMARY dbo primary key cns PK_CUSTPRIMARY dbo primary key cns . . . . . . . . . sp_productadd dbo stored procedure . . . . . . . . . CHK_ADDR_TYPE dbo check cns CHK_CUST_CREDHOLD dbo check cns

Queried again, with the object name passed as a parameter, it returns a wealth of information about the object, for example, CUSTOMER table of the ACME sample database:

exec sp_help 'CUSTOMER'

The above example returns multiple resultsets, each describing the table CUSTOMER. Because of its sheer size, the output is not shown here.

There is a sp_help<object> system stored procedure for virtually every database object. Please refer to the Microsoft SQL Server 2000 documentation for more information. When using these procedures, keep in mind that usually more than one resultset is returned.

end sidebar

Microsoft SQL Server 2000 system functions

Microsoft SQL Server 2000 also provides a number of functions that return information about the RDBMS server and contained objects. The full list of the functions is given in Appendix G. The functions' grouping follows that of Microsoft documentation, and the number of functions might increase in future releases.

Configuration functions

The @@CONNECTIONS unary function returns number of connections (or attempted connections) for the period since the RDBMS was started.

SELECT @@CONNECTIONS AS  'Connections Total' Connections Total -----------------    49 (1 row(s) affected)

The unary function @@VERSION returns the full version information about particular installation of the SQL Server.

1>  SELECT @@VERSION AS ' SQL Server Info' 2> go SQL Server Info  ------------------------------------------------------------- Microsoft SQL  Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000  Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.0 (Build  2195: Service Pack 2) (1 row(s) affected)

Microsoft SQL Server 2000 lists 15 functions in the Configuration Functions category.

Metadata functions

The DB_NAME function returns name of the current database if no parameters were specified or a database name corresponding to a numeric database ID. The function DB_ID returns the numeric ID of the current database (if no parameters were specified), or — if a numeric ID was specified — a database name corresponding to that ID.

1> USE ACME 2> SELECT  DB_NAME() AS 'database', 3> DB_NAME(7) AS 'db_by_id', 4> DB_ID() AS 'id'  5> go db_by_id databaseid id ------------------ ----------- ------ acme acme  7 (1 row(s) affected)

The example above uses both the DB_NAME and DB_ID functions; the USE ACME keyword makes sure that the ACME database context is specified. There is a total of 25 metadata functions supplied with MS SQL Server 2000.

System functions

The APP_NAME function returns the name of the application (if set by the application) that initialized connection. For example, the following output was produced using OSQL command-line utility (see Appendix E for more information).

1> select app_name() as  'application' 2> go application --------------- OSQL-32 (1 row  affected)

The @@ROWCOUNT returns the number of rows affected by the last SELECT statement for the session's scope. For instance, if issued immediately after the statement from the above example, it will produce the following results:

1> select @@ROWCOUNT as  'rows' 2> go rows ---------- 1 (1 row affected)

Note, that the statement itself affects the result: if, for example, the previous statement returned 100 records, the statement SELECT @@ROWCOUNT will return 100, issued immediately after that, it will return 1 (i.e., rows affected by the statement itself).

Here is an example of the SUSER_SNAME and USER_NAME system functions usage. The first function is supposed to return the login identification name, given a user identification number (or the current user, if the number is omitted), and the second function returns the database user name. Start two OSQL sessions or establish two connections in the SQL Query Analyzer; then use Windows authentication for the first session and SQL Server Authentication (UserID 'acme,' password 'acme') for the second session; please see Appendix E for details.

Here are the results returned by the function in the first session (Windows Authentication); the login name is represented by the computer name/user name.

1> SELECT suser_sname()  AS LOGIN_NAME, 2> user_name() AS DB_USER 3> go LOGIN_NAME DB_USER  -------------------------- ----------- ALEX-KRIEGEL\alex_kriegel dbo (1 row(s)  affected)

The following example shows the same query executed within a session initiated through SQL Server Authentication:

1> SELECT suser_sname()  AS LOGIN_NAME, 2> user_name() AS DB_USER 3> go LOGIN_NAME DB_USER  -------------------------- ----------- acme dbo (1 row(s)  affected)

There is total of 38 functions in the Microsoft SQL Server 2000.




SQL Bible
Microsoft SQL Server 2008 Bible
ISBN: 0470257040
EAN: 2147483647
Year: 2005
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net