How to Find Empty Tables with Dependencies in SQL Server

Keywords: SQL Server, empty tables, dependencies, stored procedures, views

Hashtags: #SQLServer #EmptyTables #Dependencies #StoredProcedures #Views 

Do you need to find empty tables in your SQL Server database and determine if they have any dependencies on stored procedures or views? If so, you can use a SQL query to get this information quickly and easily.

The query below returns a list of empty tables in your database along with their schema, name, and row count. It also indicates if the table has any stored procedures or views that reference it.

SELECT t.name AS table_name, s.name AS schema_name, o.name AS object_name, SUM(p.rows) AS total_rows, ( SELECT STUFF( ( SELECT ', ' + p.name FROM sys.sql_expression_dependencies d JOIN sys.objects p ON d.referencing_id = p.object_id WHERE d.referenced_id = t.object_id AND p.type_desc = 'SQL_STORED_PROCEDURE' FOR XML PATH ('') ), 1, 2, '' ) ) AS procedure_dependencies, CASE WHEN EXISTS (SELECT 1 FROM sys.dm_sql_referencing_entities(QUOTENAME(s.name) + '.' + QUOTENAME(t.name), 'OBJECT') WHERE referencing_class_desc = 'VIEW') THEN 'Yes' ELSE 'No' END AS has_view_dependencies FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id JOIN sys.partitions p ON t.object_id = p.object_id JOIN sys.objects o ON t.object_id = o.object_id WHERE p.index_id IN (0, 1) GROUP BY t.name, s.name, o.name, t.object_id HAVING SUM(p.rows) = 0;

By running this query on your SQL Server database, you can quickly identify any empty tables that have dependencies on stored procedures or views. This can help you streamline your database cleanup efforts and ensure that you don't inadvertently delete any objects that are still in use.

Give it a try and let us know how it works for you! #SQLServer #EmptyTables #Dependencies #StoredProcedures #Views