Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Information Schema Views

By Brian Kelley,

Information Schema Views

Introduction

It's always been my nature to find out how and why things work.  I know when I was young I drove my parents crazy with all my questions.  Now my dad smiles that smile of sweet revenge since my four year-old has inherited that aspect of me.  My wife just glares at me when our little boy starts on his stream of questions.  Naturally, as a DBA, I'm interested in how SQL Server works.  As a result, I feel in my element delving into the system tables.  However, Microsoft reserves the right to change those tables at any time.  So if I'm writing scripts to hand a developer or someone less curious about SQL Server, I want to make sure I'm going to hand them something that's not in danger of breaking when Microsoft rolls out the next version of SQL Server or even the next service pack.  A quick glance of changes between SQL Server 6.x and 7.0/2000 show a lot of differences in the system tables.    

Handing a solution using system tables to another SQL Server DBA is in most cases just fine.  DBAs tend to be an inquisitive lot and if a script breaks, finding out what broke and why is part of the fun.  But if I'm handing off to a developer who's primary focus is VB or VC++ or even Java, it is doubtful that the developer has the time to investigate SQL Server's system tables, especially since there's always something to be learned in the developer's primary language(s).  If the query I've written is part of some reporting solution for a manager, I cannot expect the manager to delve into SQL Server's internals to figure out how to fix it when it breaks, since most managers don't have enough hours in the day as it is.  Thankfully, for most of the information we may need about the database, there are the Information Schema views.  Microsoft implements Information Schema views for compliance with the SQL-92 standard:

In each catalog in an SQL-environment, there is a schema, the Information Schema, with the name INFORMATION_SCHEMA, containing a number of view descriptors, one base table descriptor, and several domain descriptors. The data accessible through these views is a representation of all of the descriptors in all of the schemas in that catalog.

Information Schema is mandated by SQL-92 in order to provide metadata about a given database (catalog).  Since it's there and it's part of a standard, it represents a possible choice for reporting about a database if the solution is intended for non-DBAs.  Our solutions should be sound, but they also need to be tailored for use based on our audience.  On that note, let's take a little journey into the world of Information Schema views.

The List

Let's start our review of Information Schema views by using one of them, INFORMATION_SCHEMA.Views.  There are a couple of columns we're interested in: TABLE_SCHEMA and TABLE_NAME. TABLE_SCHEMA corresponds to the view owner, and in this case we're looking at all views where the owner is INFORMATION_SCHEMA.  The TABLE_NAME is self-explanatory, the name of the view itself.  The following query:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Views
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME

returns:

TABLE_NAME
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
(list truncated)

There are 20 Information Schema views for SQL Server 2000.  SQL Server 7.0 has quite a few as well, 17 all told.  Since there are more than a handful, let's group them based on the information they report.  In a couple of cases, the particular Information Schema view could belong in multiple categories, but for simplicity's sake, we'll just put each of them in only one category.

Information Schema Views

View Name Description
Databases
Schemata Contains information on each database on the SQL Server
Tables and Views
Columns Contains information on each column in the current database
Tables Contains information on each relation (table or view) in the current database
Views Contains information on each view in the current database
View_Column_Usage Contains information on each column used by a view in the current database
View_Table_Usage Contains information on each table used by a view in the current database
Constraints
Check_Constraints Contains information on each check constraint in the current database
Constraint_Column_Usage Contains information on each column used by a constraint in the current database
Constraint_Table_Usage Contains information on each table with a constraint in the current database
Domain_Constraints Contains information on each user-defined database with a rule attached
Key_Column_Usage Contains information on each column used by a foreign or primary key in the current database
Referential_Constraints Contains information on each foreign key constraint in the database
Table_Constraints Contains information on each table-level constraint in the database
User-Defined Data Types
Column_Domain_Usage Contains information on each column in the database that has a user-defined data type 
Domains Contains information on each user-defined data type in the current database
Permissions
Column_Privileges Contains information on each column in the database where a permission has been granted to or granted by the current user
Table_Privileges Contains information on each table in the database where a permission has been granted to or granted by the current user
Stored Procedures and User-Defined Functions *
Parameters Contains information for each parameter of a user-defined function or stored procedure in the current database
Routine_Columns Contains information on each column returned by a user (or system) defined function which returns table values
Routines Contains information on each stored procedure or user-defined function in the current database

* Information Schema views for Stored Procedures and User-Defined Functions are not found in SQL Server 7.0

Points to Remember

Naming Conventions

Information Schema views are based on the SQL-92 standard, and as a result, the column names used do not necessarily match the column names we are accustomed to with SQL Server.  However, the names aren't so foreign that they are unusable.  Here's how they match up:

SQL Server SQL-92  
Database Catalog
Owner Schema
User-Defined Data Type Domain

If the names seem a little unwieldy, we can simply alias them to what we're more accustomed:

SELECT TABLE_CATALOG [Database], TABLE_SCHEMA [Owner], TABLE_NAME [Table], TABLE_Type [Type] 
FROM INFORMATION_SCHEMA.Tables

Otherwise the field names are straightforward and what we are used to seeing. 

Permissions

If we build queries against the system tables we give a user access to those queries either by granting select permissions against the system tables or by putting the queries into stored procedures.  From there, even if a user doesn't have permissions against a particular object, so long as the user has permissions to the system tables, the user is able to retrieve information about the object.  This isn't the case with Information Schema views.  They are built such that a user only sees information about the objects he or she has access to use.  If we list out the view definition for INFORMATION_SCHEMA.Tables (run the sp_helptext from the master database if using SQL Server 2000, which is what I did here), here's what we see:

--Identifies tables accessible to the current user 
create view INFORMATION_SCHEMA.TABLES 
as 
select distinct 
db_name() as TABLE_CATALOG 
,user_name(o.uid) as TABLE_SCHEMA 
,o.name as TABLE_NAME 
,case o.xtype 
when 'U' then 'BASE TABLE' 
when 'V' then 'VIEW' 
end as TABLE_TYPE 
from 
sysobjects o 
where 
o.xtype in ('U', 'V') and 
permissions(o.id) != 0

The view definition makes use of the PERMISSIONS() function, which will evaluate only those objects which the current user can access.  This has both a good side and a bad side.  If we're looking to allow a user to only get information on the objects he or she has access to, Information Schema views are ideal.  The checking is already done in the view definition.  If, however, we want the user to see information on all objects, we've got to either build our own solution or grant the user rights to all the objects (such as through the use of the db_datareader database role).

With all that said, if we don't want to go through the hassle of building queries against the system tables, the Information Schema views represent an ideal place to turn.  Most of the work has been done for us.  And since we'd be accessing the database with db_owner rights, we have permissions on every object.  As a result, with the possible exception of the views concerning privileges, all the metadata on the database will be at our fingertips based on this permission model.

A Few Gaps

Looking at the list of 20 Information Schema views, we do notice a few gaps.  There aren't any views reporting on indexing, nor are there any reporting on users and groups.  In SQL Server 7.0, stored procedures are also not covered.  Remember, the Information Schema views come from the SQL-92 standard and these are areas which aren't covered.  As a result, if we want information on indexes or on users/groups/roles, we still do need to either go to system stored procedures such as sp_helpindex or directly to the system tables to gather the information we need. And in SQL Server 7.0, we'll need to go to sp_stored_procedures and the standby sp_helptext.

Concluding Remarks

Information Schema views are great to use for reporting the metadata about a particular database.  Instituted in compliance with SQL-92, Information Schema views represent a fairly stable set of views to use for this purpose, as opposed to the system tables, which Microsoft reserves the right to alter without notice.  There are quite a few of them, 17 in 7.0 and 20 in 2000, but there are a few gaps.  We won't find information about indexing nor any on users and groups.  Also, the Information Schema views only report on objects which the user has permissions to access.  As a result, if we're fairly restrictive on our permissions, Information Schema views is probably not the best choice. However, with all that said, they are sometimes the ideal focus for our queries reporting on our databases.

Total article views: 17134 | Views in the last 30 days: 18
 
Related Articles
FORUM

How can I Select a View's Creation SQLfrom Information_Schema?

Currently I use a select on VIEWS(INFORMATION_SCHEMA) but...

ARTICLE

Querying the INFORMATION_SCHEMA

Using a macro to query the INFORMATION_SCHEMA

FORUM

Retrieve column headers for INFORMATION_SCHEMA

Get INFORMATION_SCHEMA column headers

FORUM

INFORMATION_SCHEMA

How to hide INFORMATION_SCHEMA from group of uers.

ARTICLE

Problem Solving With Information Schema Columns

Directly accessing system tables is discouraged - so what's the alternative? Using the Information S...

Tags
administration    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones