Count the Number of Tables, Stored Procedures, Triggers, Functions, Views in Your Database

Get the number of Tables, Stored Procedures, Triggers, Functions and so on in your database. In this article I will tell how you can determine how many types of entities you have in your any database, like:

• Primary Key
• Foreign Key
• Table
• Stored Procedure
• Function
• Table Function
• Trigger
• View

There is a system view named "sysobjects" in every database by which you can get the list of every kind of entity that exists in your databse.

To check it, expand your database then "Views" and then expand "System Views" like here the databse name is "Test".

Expand the "Test" database and Expand the "Views" as in the following:

 

Check it, there is a system view named "sysobjects".

 

You can find some important entities through the sysobjects view like this:

1. Aggregate Function: Get the name of all Aggregate functions.
   
   Select name,type from sysobjects where type='AF'
    
2. Foreign Key: Get the name of all Foreign Keys.
   
   Select name,type from sysobjects where type='F'
    
3. Function: Get the name of all scalar functions.
   
   Select name,type from sysobjects where type='FN'
    
4. Stored Procedure: Get the name of all Stored Procedures.
   
   Select name,type from sysobjects where type='P'
    
5. Primary Key: Get the name of all Primary Keys.
   
   Select name,type from sysobjects where type='PK'
    
6. Table-Valued-Function: Get the name of all Aggregate functions.
   
   Select name,type from sysobjects where type='TF'
    
7. Trigger: Get the name of all DML triggers.
   
   Select name,type from sysobjects where type='TR'
    
8. Tables: Get the name of all Tables (user-defined).
   
   Select name,type from sysobjects where type='U'
    
9. View: Get the name of all Views.
   
   Select name,type from sysobjects where type='V'

The following are all the types of entities in the sysobjects view:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure