Sql Server: Count number of Tables, Views, Store procedures and Triggers in a database - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial


Sql Server: Count number of Tables, Views, Store procedures and Triggers in a database

In this article I am going to explain how to count number of Tables, Views, Store procedures and Triggers in a sql server database.


 Description:
We have 2 options to count the number of tables, procedures etc. in a database.

Method 1: using database sys

SELECT count(*) AS [Total Tables] from sys.tables

SELECT count(*) AS [Total Procedures] from  sys.procedures

SELECT count(*) AS [Total Triggers] from  sys.triggers

SELECT count(*) AS [Total Views] from  sys.views


Method 2: using database information_schema

SELECT count(*) AS [Total Tables] from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

SELECT count(*) AS [Total Views] from INFORMATION_SCHEMA.VIEWS

SELECT count(*) AS [Total Procedures] from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

SELECT count(*) AS [Total Functions] from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

SELECT count(*) AS [Total Triggers] from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'Triggers'

  

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon