System Views

Q)An information schema view is one of several methods SQL Server 2005 provides for obtaining metadata.

Information schema views provide an internal, system table-independent view of the SQL Server metadata.
Information schema views enable applications to work correctly although significant changes have been
made to the underlying system tables. The information schema views included in SQL Server 2005 comply
with the SQL-92 standard definition for the INFORMATION_SCHEMA.


1)INFORMATION_SCHEMA.CHECK_CONSTRAINTS
Returns one row for each CHECK constraint in the current database.
This information schema view returns information about the objects to
which the current user has permissions.

2)INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
Returns one row for each column in the current database that
has an alias data type.
This information schema view returns information about the objects to
which the current user has permissions.

3)INFORMATION_SCHEMA.COLUMN_PRIVILEGES
Returns one row for each column that has a privilege
that is either granted to or
granted by the current user in the current database.
To See the Columns in the table

4)INFORMATION_SCHEMA.COLUMNS
TO FIND THE COLUMN NAME IN PERTICULAR DATABASE R IN A TABLE

select * from INFORMATION_SCHEMA.COLUMNS
where table_name='TargetLocation'

5)INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Returns one row for each column in the current database that has a constraint
defined on the column. This information schema view returns
information about the objects to which the current user has permissions.

6)INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
Returns one row for each table in the current database that has a constraint
defined on the table. This information schema view returns information about
the objects to which the current user has permissions.

7)INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
Returns one row for each alias data type in the current database that has a rule
bound to it and that can be accessed by current user.

8)INFORMATION_SCHEMA.DOMAINS
Returns one row for each alias data type that can be accessed by
the current user in the current database.

9)INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Returns one row for each column that is constrained
as a key in the current database. This information
schema view returns information about the objects to which
the current user has permissions.

10) To See the parameteres of the perticular storedprcedure
select * from INFORMATION_SCHEMA.PARAMETERS
where specific_name='sp_promo'

11)INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Returns one row for each FOREIGN KEY constraint in
the current database. This information
schema view returns information about the objects
to which the current user has permissions.

12)INFORMATION_SCHEMA.ROUTINES
Returns one row for each stored procedure and function
that can be accessed by the
current user in the current database. The columns that
describe the return value apply only to functions.
For stored procedures, these columns will be NULL.

13)INFORMATION_SCHEMA.ROUTINE_COLUMNS
Returns one row for each column returned by the table-valued
functions that can be accessed by the current user in the current database.

14)To See the Schema
select * from INFORMATION_SCHEMA.SCHEMATA

15)INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Returns one row for each table constraint in the current database.This information schema view returns information about the objects to Which the current user has permissions.

16)INFORMATION_SCHEMA.TABLE_PRIVILEGES
Returns one row for each table privilege that is granted to or
granted by the current user in the current database.

17)To See the ALL TableS in a GIVEN Database
select * from INFORMATION_SCHEMA.TABLES

18)INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
Returns one row for each column in the current database that is used
in a view definition. This information schema view returns
information about the objects to which the current user has permissions.
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE VIEW_NAME='vw_promos'

19)INFORMATION_SCHEMA.VIEW_TABLE_USAGE
Returns one row for each table in the current database that is used in a view.
This information schema view returns information about
the objects to which the current user has permissions.

20)To see the Views in the Database
select * from INFORMATION_SCHEMA.VIEWS

21)To See the Entire Data Base.
select * from sys.databases where name='master'

22)see the column in db
select *from sys.all_columns

23)see the physical databasefile
select * from sys.database_files

24)see the all the objects in the db
select * from sys.objects

25)SEE THE SERVERNAME CONNECTED TO THE SYSTEM
select * from sys.servers

26)SEE THE TABLE IN A DATABASE
SELECT * FROM sys.tables

27)SEE THE VIEWS IN THE DATABASE
SELECT * FROM sys.views