How to Get the Database Size and Count All SQL Server Object Types in the Database

In our routine database management tasks, it’s often necessary to learn the size of the database and count the objects within all SQL Server tables. Fortunately, we have established methods and tools to swiftly provide this information. In this article, we will explore these methods and tools. Let’s get started.

Contents

• How to get the size of the database using standard scripts
• How to get the number of objects in a database with standard methods
• How to search for objects using dbForge Studio for SQL Server
• Conclusion

How to get the size of the database using standard scripts

Knowing the size of the database is vital for resource planning, performance tuning, scalability, backup and recovery, and many other aspects that contribute to stable, efficient, and cost-effective database performance.

In SQL Server, the two most common methods to get the database size information are:

• Querying the sys.database_files system view
• Using the sp_spaceused system stored procedure

Let’s explore both methods in detail. For demo purposes, we’ll apply dbForge Studio for SQL Server – the most powerful alternative to the standard SQL Server Management Studio (SSMS) and one of the best IDEs that can help you easily cope with any database tasks related to development, management, testing, and DevOps automation.

Using the sys.database_files system view

In our test case, we want to check the size of the test AdventureWorks2019 database. The query syntax is below, let us execute this query:  

SELECT
    DB_NAME() AS [database_name],
    CONCAT(CAST(SUM(
        CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
    ) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;

Using the sp_spaceused system stored procedure

The EXEC sp_spaceused command in SQL Server displays the amount of space taken by the entire database or a specific database table. This stored procedure allows us to get the size of the database in total and the size of an unallocated space.

EXEC sp_spaceused;

Using this procedure can be a more straightforward method.

How to get the number of objects in a database with standard methods

Counting the number of objects in any database is a common and routine task. For instance, we need to migrate some data from one table into another. In this case, we may need to verify which and how many objects are present in both tables before starting the migration.

Assume we want to get the number of objects in the AdventureWorks2019 sample database. The first option is querying the sys.objects system catalog view, which allows you to view all objects in an SQL Server database. For ease, we can group the database objects by object type.

Let us execute the following statement that utilizes the COUNT() function to return the number of rows found in a database:

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.

How to 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 to not only 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

Counting objects and checking the size of databases are routine tasks in daily SQL Server database management. SQL Server provides built-in methods to easily obtain this information, which we’ve detailed in this article through standard scripts and dbForge Studio for SQL Server. Additionally, we demonstrated how to search for objects and categorize them by type using dbForge Studio. This feature is just one of the many tools available in this powerful IDE, designed to make database development, management, and administration as seamless as possible.