Friday, October 19, 2007

Access SQL Server catalog

Sometimes, you need to know schema in details of your database. You may use system objects such as sysobjects/ sys.objects, syscolumns/ sys.columns, etc or system stored procedures to query that information.
Another way to get that information is using INFORMATION SCHEMA in SQL Server 2005:

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

Source: SQL Server Book online
For example:
If you want to know the schema of tblEmployee Table, you can query like that:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblEmployee'

No comments:

Post a Comment

Oracle Query