How to Count All SQL Server Object Types in the Database

The article provides a quick way to count the number of all objects grouped by an object type in a SQL Server database using the COUNT() function and the sys.objects system view.

In your day-to-day database-related routine, retrieving the object counts from all SQL Server tables is likely to be a common practice. For example, when you migrate data between tables and need to check if there are any objects in one database but not in the other. To achieve this, you can search for object types using standard scripts or tools. For ease, you can group the database objects by type.

Further in the article, we’ll see how to quickly count all SQL Server objects from all the tables regardless of the database size. For demo purposes, we’ll use dbForge Studio for SQL Server, one of the best IDE that can help you easily cope with any database tasks related to development, management, testing, and DevOps automation.

To get the number of objects in the AdventureWorks2019 sample database, we’ll query the sys.objects system catalog view, which allows you to view all objects in a SQL Server database. In the query, we’ll use the COUNT() function to return the number of rows found in a database. The objects will be grouped by object type.

So, the easiest way to group SQL Server objects and count them is to execute the following SELECT statement:

SELECT
  obj.type ObjType
 ,COUNT(*) ObjCount
FROM sys.objects obj
GROUP BY obj.type
ORDER BY ObjCount DESC;

It outputs the total number of objects grouped by object type and displays them in descending order as stated in the ORDER BY clause.

As you can see, the statement returns the abbreviations of object types. You can refer to the following table to view the name and description of the object type.

Since it is not convenient to search for the explanation of the object type each time you retrieve SQL Server objects, you can execute the following SELECT statement:

SELECT
  obj.type ObjType
 ,'ObjDescription' =
  CASE obj.type
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK Constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'EC' THEN 'Edge Constraint'
    WHEN 'ET' THEN 'External Table'
    WHEN 'F' THEN 'FOREIGN KEY Constraint'
    WHEN 'FN' THEN 'SQL Scalar functions'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL Inline Table-valued Function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'Primary Key'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter procedure'
    WHEN 'S' THEN 'System base table'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence Object'
    WHEN 'ST' THEN 'STATS_TREE'
    WHEN 'SQ' THEN 'Service Queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE Constraint'
    WHEN 'U' THEN 'User Table'
    WHEN 'V' THEN 'View'
    WHEN 'X' THEN 'Extended stored procedure'
    ELSE obj.type
  END
 ,COUNT(*) ObjCount
FROM sys.objects obj
GROUP BY obj.type
ORDER BY ObjCount DESC

In the result, you will see the grid displaying the object type code, its description, and the total number of objects found in the AdventureWorks2019 database.

Search for objects using dbForge Studio for SQL Server

With the help of the Search tool built into dbForge Studio for SQL Server, you can easily and quickly search for objects in databases of any capacity. The tool allows you not only to search for and group objects by object type but also to view the DDL script of the selected object and navigate to the selected object in the Database Explorer tree.

Now, let’s see how to search for objects in dbForge Studio. In Database Explorer, right-click the required database and select Find Object. In the Search document that opens, do the following:

• Select Search objects by name and DDL.
• In the Search text box, specify the search string, for example, humanresources.
• In the search options, select the search mode such as Match Case, Match Whole Word, or Use WildCards.
• Group the results by clicking the corresponding control on the Search toolbar.
• From the All object types dropdown, select the database objects for which the search will be performed.
• In the Search text box, click Search.

When it is complete, the results will be displayed in the grid. To expand the group, click the expander arrow. In addition, clicking the object in the grid will display the DDL statement of the selected object in the Preview window. That’s it!

In our example, the result displays all the objects containing the humanresources string entries in their object names, as well as groups them by object types.

The Find Objects feature is one of the many capabilities that dbForge Studio can offer. If you want to try more features and functionalities of the Studio, download its 30-day free trial version.

Conclusion

In the article, we have examined how to get the object counts from the SQL Server database of any size using the COUNT() function and sys.objects system catalog view. In addition, we have described how easy it is to search for objects and group them by object types using dbForge Studio for SQL Server.