SQL Bible [Electronic resources] نسخه متنی

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.

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.

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.

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.

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).

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.

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.

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'

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.

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

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.

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.