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  

Comments

RSS Icon Subscribe to comments
Posted By: kevinmiller on July 23, 2011

It’s hard to find knowledgeable people on this topic, but you sound like you know what you’re talking about! Thanks web design online banner design brochure design online

You need to login to post a comment.