Posted By

Mithun on 05/04/11


Tagged

sql mssql T-SQL


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

johnw


Count number of tables, views, stored procedures or functions


 / Published in: SQL
 

URL: http://www.hibbins.com/2008/09/04/tsql-count-number-of-stored-procedures-views-tables-or-functions/

You can use TSQL to Count Number Of Stored Procedures, Views, Tables or Functions in a Database by using the Database INFORMATION_SCHEMA view

  1. /* Count Number Of Tables In A Database */
  2. SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
  3.  
  4. /* Count Number Of Views In A Database */
  5. SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS
  6.  
  7. /* Count Number Of Stored Procedures In A Database */
  8. SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
  9.  
  10. /* Count Number Of Functions In A Database */
  11. SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

Report this snippet  

You need to login to post a comment.