A Primer on INFORMATION_SCHEMA Views in SQL Server 2000 - Most commonly used queries on INFORMATION_SCHEMA views
(Page 3 of 5 )
The following queries may help application programmers to get the most out of INFORMATION_SCHEMA views in a practical way. You can execute these queries on any database of any SQL Server 2000 instance.
The following command gives the list of tables available in the database:
select table_name
from information_schema.tables
where table_type='BASE TABLE'
The following command gives the list of columns of every table in the database, including their data types and widths. You can also use a WHERE clause, if you would like to deal with only one table.
select table_name,column_name, data_type, character_maximum_length as width
from information_schema.columns
order by table_name,ordinal_position
The following command gives the list of all views and their definitions from the database.
select table_name as view_name, view_definition from information_schema.views
The following command gives all the list stored procedures and their definitions (unless they are encrypted) available in the database. To get the list of stored functions just replace the word ‘PROCEDURE’ with ‘FUNCTION’.
select routine_name, routine_definition
from information_schema.routines
where routine_type='PROCEDURE'
The following command gives you all the foreign key constraints (relationships) together with their respective primary keys of parents.
select constraint_name as 'foreign key', unique_constraint_name as 'parent primary key'
from information_schema.referential_constraints
The following gives you all the lists of parameters of every routine (stored procedure or stored function) along with their data types and widths.
select specific_name as [routine name],parameter_name, data_type, character_maximum_length as [width]
from information_schema.parameters
order by specific_name,ordinal_position
The above examples are just to help you understand how this works. You can also design more complicated queries by joining the INFORMATION_SCHEMA views together according to the requirements.
Next: Internals of INFORMATION_SCHEMA views >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 39 
