AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1380&pId=-1
Information Schema and SQL Server 2005
page
by Uday Denduluri
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24304/ 58

Introduction

Information Schema is the part of the SQL-92 standard which exposes the metadata of the database. In SQL server a set of views are created in each of the database which exposes the metadata of the database. The information schema is kept in a separate schema - information schema - which exists in all databases, but which is not included in the search path by default. It is implemented as a set of views returning specially defined data types as required by the standard. The access rights of these views do not fall under the login type "dbo." Using Information Schema we can get the information about tables, views, constraints, etc.

Description

The table given below shows the views that are supported by SQL server 2005 as a part of information schema.

Table 1

View

Contains information about

COLUMNS

Columns accessible to the current user in the current database.

INDEXES

Indexes in the current database.

KEY_COLUMN_USAGE

Keys in the current database.

PROVIDER_TYPES

Data types supported in SQL Server Mobile.

TABLES

Tables accessible to the current user in the current database.

TABLE_CONSTRAINTS

Table constraints in the current database.

REFERENTIAL_CONSTRAINTS

Foreign constraint in the current database

The table given below explains the usage pattern of Information Schema.

Table 2

select * from INFORMATION_SCHEMA.Columns where table_name = "tablename"

Returns the schema information about the columns in the table.

select * from INFORMATION_SCHEMA.Tables where table_name = "tablename"

Returns the metadata information about the table name specified.

select * from INFORMATION_SCHEMA.Columns

Returns the column information of the entire tables in the database.

select * from INFORMATION_SCHEMA.Tables

Returns the table information of all the tables in the database.

select * from INFORMATION_SCHEMA.Table_Constraints where Table_name = "tablename"

Returns the constraint information of the table in the database.

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Returns the information of the referential constraints in the database.

References
Conclusion

The functionality of the Information_Schema is often confused with sysobjects, for example "select * from sysobjects where type = 'U.'" But, there are many differences between Information_Schema and sysobjects. Sysobjects are not the part of the SQL-92 standard and cannot be relied upon using Information_Schema views displays tables on which the user has permissions.



©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-09-21 6:05:35 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search