Information Schema Views

How do we get meta data about our sql server ? how do we know what all databases exists, and that are the different tables, stored procedures inside each ?

MS Sql Server provides two ways for doing it.
1. Using System Stored Procedures.
2. Information Schema Views

But owing to difficulties of remembering all the internal stored procedures and thier hierarchies, Information Scheme Views are best recommended for the programmers.

Some of the commonly used quieries on same are.

1. To get all Database in your sql server

select *from information_schema.SCHEMATA

2. To get all tables in a DataBase

select *
from information_schema.tables
where table_catalog = 'MyDb'
and Table_type = 'Base Table'

3. To get all coumns in a Table

select *
from information_schema.columns
where table_catalog = 'Consumables'
and Table_Name = 'viewaccessories'

4. To Get all Stored Procedures in a DB

select *
from information_schema.routines
where routine_type='PROCEDURE'
and Specific_Catalog = 'Consumables'

5. To get parameter list for a stored procedure

select *
from information_schema.parameters
where specific_Name = 'sp_Update_Consumable'
and Specific_Catalog = 'Consumables'