sql-query-to-get-all-empty-tables-in-database

SQL Query to get all empty tables in database

by

This post explains how to use sql query to get all empty tables in a database and email them in a table format.

Problem Statement : SQL query to return list of tables in a database without any rows and send email in a table format.

Solution:

SELECT Schema_name(tab.schema_id) + '.' + tab.NAME AS [table],rows
      FROM   sys.tables tab
             INNER JOIN sys.partitions part
                     ON tab.object_id = part.object_id
      WHERE  part.index_id IN ( 1, 0 ) -- 0 - table without PK, 1 table with PK
			 --AND tab.Name not like '%EH%' --if you want ignore any tables
      GROUP  BY Schema_name(tab.schema_id) + '.' + tab.NAME,rows
      HAVING Sum(part.rows) = 0
      ORDER  BY [table]

SQL Send Email in tablular format

Now we need to send the above result set as an email in tabular format. To do so we can use below query.

  DECLARE @xml NVARCHAR(max)
      DECLARE @body NVARCHAR(max)

      SET @xml = Cast((SELECT [table] AS 'td','',rows as 'td',''
                       FROM   #temp
                       ORDER  BY [table]
                       FOR xml path('tr'), elements) AS NVARCHAR(max))
      SET @body ='<html><body><H3>Tables with 0 Data Count</H3> <table border = 1>  <tr> <th> Table Name </th><th> Row Count </th> </tr>'
      SET @body = @body + @xml + '</table></body></html>'

      PRINT @body

      EXEC msdb.dbo.Sp_send_dbmail
        @profile_name = 'Your SQL Profile',-- replace with your SQL Database Mail Profile 
        @body = @body,
        @body_format ='HTML',
        @recipients = 'Your Email Address',-- replace with your email address
        @subject = 'Data volume Notification';

Final Thoughts

Below is the final query which can be used as a stored procedure to send email with tabular format for all the tables which are empty in a database.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DataValidation]
AS
  
  BEGIN
      
	  --DROP TABLE #TEMP
	  SELECT Schema_name(tab.schema_id) + '.' + tab.NAME AS [table]
      INTO   #temp
      FROM   sys.tables tab
             INNER JOIN sys.partitions part
                     ON tab.object_id = part.object_id
      WHERE  part.index_id IN ( 1, 0 ) -- 0 - table without PK, 1 table with PK
			 --AND tab.Name not like '%EH%' --required tablesonly
      GROUP  BY Schema_name(tab.schema_id) + '.' + tab.NAME
      HAVING Sum(part.rows) = 0
      ORDER  BY [table]

	  --SELECT * FROM  #TEMP
      DECLARE @xml NVARCHAR(max)
      DECLARE @body NVARCHAR(max)

      SET @xml = Cast((SELECT [table] AS 'td',
                              ''
                       FROM   #temp
                       ORDER  BY [table]
                       FOR xml path('tr'), elements) AS NVARCHAR(max))
      SET @body ='<html><body><H3>Tables with 0 Data Count</H3> <table border = 1>  <tr> <th> Table Name </th> <th> Row Count </th> </tr>'
      SET @body = @body + @xml + '</table></body></html>'

      PRINT @body

      EXEC msdb.dbo.Sp_send_dbmail
        @profile_name = 'profile', -- replace with your SQL Database Mail Profile 
        @body = @body,
        @body_format ='HTML',
        @recipients = 'your email',-- replace with your email address
        @subject = 'Data volume Notification';

      DROP TABLE #temp
  END

Leave a Comment