Ways to make best use of INFORMATION_SCHEMA Views - Stored procedures & Functions?
How many of you took help from using INFORMATION_SCHEMA views in SQL Server?
Have you ever wondered in returning the informaiton of a Stored procedure or function, you may be thinking using SP_HELPTEXT would get you. Parts of the solution is yes with SP_HELPTEXT you can get but still there is lot more to get, in this case you can make best use of INFORMATION_SCHEMA (IS) views that are enhanced since SQL Server 2000 version.
Its a pity that most of DBAs and Developers miss out using these views when they need to fetch such an information. Books Online has got information with regard to these IS views by retrieving information with a specification of fully qualified name of INFORMATION_SCHEMA.view_name. Similarly using TSQL routines with IS views will return each stored procedure and function that can be accessed by the current user. For instance,
Select * from INFORMATION_SCHEMA.Routines
This View returns one row for each stored procedure and function that can be accessed by the current user in the current database. Similarly you can use the INFORMATION_SCHEMA.ROUTINES view to retrieve information about stored procedures.
--For Functions
SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = N'FUNCTION';
For example, the following query returns owner, name and definition text of stored procedures in the current database:
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
--For Triggers
SELECT Routine_definition
FROM INFORMATION_SCHEMA.Routines
Where Routine_Name = 'SomeTriggerName'
--To list Views
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME;
--To List Constraints
SELECT constraint_name, constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = DB_NAME() AND table_name = 'TEST2';
As referring above these INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.
