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.

In previous article I have explained how to read XML file and display data usingasp.net MVC, how to export table to Excel file using Angularjs in asp.net MVC and how to import XML file data into Sql table using Sqlbulkcopy in asp.net.

 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'