Home > SQL Challenge > information_schema – SQL-92 standard

information_schema – SQL-92 standard

I’ve known about the INFORMATION_SCHEMA views (or system tables) in SQL Server. Recently I also learned that they are actually part of the SQL-92 standard and supported on other database platforms.

The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. Because the structure of these tables are standardized you can write SQL statements that work on various database platforms.

You can  use INFORMATION_SCHEMA views to build some automatic tasks. With the INFORMATION_SCHEMA you can get the datatype, max character length, and if null values are allowed, and perform validation before it hits the database. And if a column is made wider, you don’t have to make any code changes. Here is a simple example to drop a table if it exists in a procedure.

if exists
    (
        select *
        from information_schema.tables
        where table_name=’daily_tbl’
        and table_schema=’dbo’
    )
        drop table dbo.daily_tbl

Here is a list of the information schema views:

INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.STATISTICS
INFORMATION_SCHEMA.USER_PRIVILEGES
INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
INFORMATION_SCHEMA.CHARACTER_SETS
INFORMATION_SCHEMA.COLLATIONS
INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TRIGGERS
INFORMATION_SCHEMA.PROFILING

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: