Sunday, February 5, 2023
HomeHow ToHow to Count All SQL Server Object Types in the Database

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. 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.

The easiest way to get the number of objects in the SQL Server AdventureWorks2019 sample database

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.

Object Type CodeObject TypeObject Type
AFAggregate function (CLR)AGGREGATE_FUNCTION
CCHECK constraintCHECK_CONSTRAINT
DDEFAULT (constraint or stand-alone)DEFAULT_CONSTRAINT
FFOREIGN KEY constraintFOREIGN_KEY_CONSTRAINT
FNSQL scalar functionSQL_SCALAR_FUNCTION
FSAssembly (CLR) scalar-functionCLR_SCALAR_FUNCTION
FTAssembly (CLR) table-valued functionCLR_TABLE_VALUED_FUNCTION
IFSQL inline table-valued functionSQL_INLINE_TABLE_VALUED_FUNCTION
ITInternal tableINTERNAL_TABLE
PSQL Stored ProcedureSQL_STORED_PROCEDURE
PCAssembly (CLR) stored-procedureCLR_STORED_PROCEDURE
PGPlan guidePLAN_GUIDE
PKPRIMARY KEY constraintPRIMARY_KEY_CONSTRAINT
RRule (old-style, stand-alone)RULE
RFReplication-filter-procedureREPLICATION_FILTER_PROCEDURE
SSystem base tableSYSTEM_TABLE
SNSynonymSYNONYM
SOSequence objectSEQUENCE_OBJECT
SQService queueSERVICE_QUEUE
TAAssembly (CLR) DML triggerCLR_TRIGGER
TFSQL table-valued-functionSQL_TABLE_VALUED_FUNCTION
TRSQL DML triggerSQL_TRIGGER
TTTable typeTABLE_TYPE
UUser-defined tableUSER_TABLE
UQUNIQUE constraintUNIQUE_CONSTRAINT
VViewVIEW
XExtended stored procedureEXTENDED_STORED_PROCEDURE

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.

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

You can search for objects easily and quickly with the help of the Search tool built into dbForge Studio for SQL Server. 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 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 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.

Download dbForge Studio for SQL Server to evaluate its cutting-edge features and capabilities
RELATED ARTICLES

Whitepaper

Social

Topics

Products