Count Number Of Stored Procedures, Views, Tables and Functions using Query

Hi friends ,

Sometimes you may need to count number of table in your database and if your database have hundreds of table then it is almost impossible to count tables in your database.

So here is the small query, using which you can count Stored procedure you have used ,table you have made, views you may have created.

/* Count Number Of Tables In A Database */
SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE=‘BASE TABLE’

/* Count Number Of Views In A Database */
SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS

/* Count Number Of Stored Procedures In A Database */
SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘PROCEDURE’

/* Count Number Of Functions In A Database */
SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘FUNCTION’

The same methodology can be used to query for information :

/* Select Table Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=‘BASE TABLE’

/* Select View Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.VIEWS

/* Select Stored Procedure Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘PROCEDURE’

/* Select Function Information For A Database */
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘FUNCTION’

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com