Friday, March 24, 2017

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'

  

No comments:

Post a Comment