Advanced Search

Return to Snippet

Revision: 45588
at May 4, 2011 19:13 by Mithun
Updated Code 
/* 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'

Revision: 45587
at May 4, 2011 19:11 by Mithun
Initial Code 
/* 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'
Initial URL 
http://www.hibbins.com/2008/09/04/tsql-count-number-of-stored-procedures-views-tables-or-functions/
Initial Description 
You can use TSQL to Count Number Of Stored Procedures, Views, Tables or Functions in a Database by using the Database INFORMATION_SCHEMA view
Initial Title 
Count number of  tables, views, stored procedures or functions
Initial Tags 
sql
Initial Language 
SQL