MS SQL Server
  Home arrow MS SQL Server arrow Page 2 - A Primer on INFORMATION_SCHEMA Views in SQ...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MS SQL SERVER

A Primer on INFORMATION_SCHEMA Views in SQL Server 2000
By: Jagadish Chaterjee
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 39
    2005-05-23

    Table of Contents:
  • A Primer on INFORMATION_SCHEMA Views in SQL Server 2000
  • High-level overview of all INFORMATION_SCHEMA views
  • Most commonly used queries on INFORMATION_SCHEMA views
  • Internals of INFORMATION_SCHEMA views
  • Summary

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    A Primer on INFORMATION_SCHEMA Views in SQL Server 2000 - High-level overview of all INFORMATION_SCHEMA views


    (Page 2 of 5 )

    Now, the issue is how to get all the information schema views in the form of a list. The following command helps to rescue us (but make sure to execute the command from the "master" database).

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.Views

    WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'

    ORDER BY TABLE_NAME

     

    The above command lists out all the views you can use with information_schema. Following is a table which shows a brief description of all of the views available from the above command (which has been highly summarized from Books Online for the sake of convenience).

     

    CHECK_CONSTRAINTS

    Contains one row for each CHECK constraint in the current database

    COLUMN_DOMAIN_USAGE

    Contains one row for each column, in the current database, that has a user-defined data type.

    COLUMN_PRIVILEGES

    Contains one row for each column with a privilege either granted to or by the current user in the current database

    COLUMNS

    Contains one row for each column accessible to the current user in the current database.

    CONSTRAINT_COLUMN_USAGE

    Contains one row for each column, in the current database, that has a constraint defined on it.

    CONSTRAINT_TABLE_USAGE

    Contains one row for each table, in the current database, that has a constraint defined on it.

    DOMAIN_CONSTRAINTS

    Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it

    DOMAINS

    Contains one row for each user-defined data type accessible to the current user in the current database

    KEY_COLUMN_USAGE

    Contains one row for each column, in the current database, that is constrained as a key

    PARAMETERS

    Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.

    REFERENTIAL_CONSTRAINTS

    Contains one row for each foreign constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.

    ROUTINE_COLUMNS

    Contains one row for each column returned by the table-valued functions accessible to the current user in the current database

    ROUTINES

    Contains one row for each stored procedure and function accessible to the current user in the current database.

    SCHEMATA

    Contains one row for each database that has permissions for the current user

    TABLE_CONSTRAINTS

    Contains one row for each table constraint in the current database.

    TABLE_PRIVILEGES

    Contains one row for each table privilege granted to or by the current user in the current database

    TABLES

    Contains one row for each table in the current database for which the current user has permissions

    VIEW_COLUMN_USAGE

    Contains one row for each column, in the current database, used in a view definition

    VIEW_TABLE_USAGE

    Contains one row for each table, in the current database, used in a view

    VIEWS

    Contains one row for views accessible to the current user in the current database

     

    To use any of the above views, just add the respective view to the "information_schema" word separated with a dot as follows.

    select * from information_schema.tables 

     

    More MS SQL Server Articles
    More By Jagadish Chaterjee


     

    MS SQL SERVER ARTICLES

    - Windows Server 2008 as a Workstation OS
    - An Overview of Windows Server 2008 R2
    - LINQ to MySQL, Oracle and PostgreSQL Provide...
    - Completing the Introduction to Transact-SQL
    - A Brief Introduction to Transact-SQL
    - Lookups and Blocking Bad Data
    - Field Validation Rules for Blocking Bad Data
    - Using Masks to Block Bad Data
    - Blocking Bad Data
    - Using @@ROWCOUNT and TABLE Variables for Dat...
    - How to Use Variables, IF and CASE in Databas...
    - Creating Important Aspects of Notification S...
    - Working wth Variables in Database Interactio...
    - Delving Deeper into Notification Services
    - Notification Services





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 3 Hosted by Hostway
    Stay green...Green IT