Presents your 
SQL SERVER E-NEWSLETTER for January 30, 2003

<------------------------------------------->

VIEW DATABASE METADATA WITH SYSTEM VIEWS

INFORMATION_SCHEMA system views are mechanisms that contain metadata
about database structures within the database. Instead of querying the
system tables directly, it's highly advised to use metadata functions, 
system stored procedures, or the INFORMATION_SCHEMA system views.

Microsoft may modify the system tables that the views refer to but will
maintain the view data. It's an abstraction that allows you to base your
queries on the INFORMATION_SCHEMA system views, which are not likely to
fail, unlike on the system tables, which will fail if the system tables
have been modified.

INFORMATION_SCHEMA is actually the owner of the system views, which
include:

* CHECK_CONSTRAINTS
* COLUMN_DOMAIN_USAGE
* COLUMN_PRIVILEGES
* COLUMNS
* CONSTRAINT_COLUMN_USAGE
* CONSTRAINT_TABLE_USAGE
* DOMAIN_CONSTRAINTS
* DOMAINS
* KEY_COLUMN_USAGE
* PARAMETERS
* REFERENTIAL_CONSTRAINTS
* ROUTINE_COLUMNS
* ROUTINES
* SCHEMATA
* TABLE_CONSTRAINTS
* TABLE_PRIVILEGES
* TABLES
* VIEW_COLUMN_USAGE
* VIEW_TABLE_USAGE
* VIEWS

Here is a query to find the list of views available:

select name from sysobjects
where user_name(uid) = 'INFORMATION_SCHEMA'
order by name

Using the USER_NAME function shows that INFORMATION_SCHEMA is a special
type of user. INFORMATION_SCHEMA can't be modified in any fashion, which
is why it's necessary to qualify the name of the views with
INFORMATION_SCHEMA to retrieve the information.

The following script demonstrates how to retrieve database information
from INFORMATION_SCHEMA to find out who has privileges to two particular
tables and what those privileges are:

EXEC sp_addlogin 'JANE', null
GO
EXEC sp_addlogin 'MARK', null
GO
EXEC sp_adduser 'JANE'
GO
EXEC sp_adduser 'MARK'
GO

CREATE TABLE T1
(PKEY1 INT IDENTITY NOT NULL
  CONSTRAINT PK_T1 PRIMARY KEY,
COL1 INT NULL)
GO
CREATE TABLE T2
(PKEY1 INT IDENTITY NOT NULL
  CONSTRAINT PK_T2 PRIMARY KEY,
COL1 INT NULL)
GO

GRANT ALL ON T1 TO JANE
GO

SETUSER 'JANE'
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN ('T1','T2')
GO
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_NAME IN
('T1','T2')
GO

SETUSER ''
GO

SETUSER 'MARK'
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN ('T1','T2')
GO
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_NAME IN
('T1','T2')
GO

SETUSER ''
GO
DROP TABLE T1,T2
GO
EXEC sp_dropuser 'JANE'
GO
EXEC sp_dropuser 'MARK'
GO

EXEC sp_droplogin 'JANE'
GO
EXEC sp_droplogin 'MARK'
GO

----------------------------------------