Tuesday, December 11, 2007

How To Find What Are All The Tables Available In Particular Schema In SQL SEREVER

Query To List All The Tables In a Particular Schema

Query

SELECT * FROM INFORMATION_SCHEMA.TABLES


Description
This Query List All The Tables In a Particular Schema(Database).


For Example

If You Are in the Pubs Schema(Database) In SQL SERVER,This Query List All The Tables available within the particular Schema including systable and user table

Query To List All The Columns Available In A Particular Schema


Query

SELECT * FROM INFORMATION_SCHEMA.COLUMNS


Description

This Query List All The Columns Available In A Particular Schema(Database)


For Example
If You Are in the Pubs Schema(Database) In SQL SERVER, This Query List All The Columns available within the particular Schema

Query To List All The Users Tables In a Particular Schema


Query

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME


Description

This Query List All The Users Tables In a Particular Schema(Database).

For Example
If You Are in the Pubs Schema(Database) In SQL SERVER, This Query List All The Users Tables available within the particular Schema

Query To List All The Columns Available In A Particular Schema


Query

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AUTHORS'


Description
This Query List All The Columns Available In A Particular Schema(Database) for the Particular Table Authors

To Find The Primary Key Of The Table.


Syntax:


SP_PKEYS YourTableName


Description

This System Procedure Returns the Primary Key Reference Of The Particular Table In That Schema(Database).

Example: SP_PKEYS
AUTHORS

Note:Giving the tablename within the single quotes is not mandatory

To Find The Foreign Key Of The Table.

Syntax:


SP_FKEYS YourTableName


Description

This System Procedure Returns the Foreign Key Reference Of The Particular Table In That Schema(Database).

Example: SP_FKEYS
AUTHORS
Note: Giving the tablename within the single quotes is not mandatory