Alerting if table is empty

• ramana3327

  SSCoach

  Points: 19722

  More actions

  October 3, 2016 at 7:47 am

  #400829

  Hi All,

  I am looking for a way to get an alert from SQL Agent if any table is empty in a database with the name of the table.

• Jeff Moden

  SSC Guru

  Points: 1004650

  More actions

  October 3, 2016 at 7:35 pm

  #1904584

  ramana3327 (10/3/2016)

  ---

  Hi All,

  I am looking for a way to get an alert from SQL Agent if any table is empty in a database with the name of the table.

  I believe there's no such thing. You'll need to write a query or stored proc that checks for the number of rows in the table (perhaps by one of the allocation DMVs) and run a scheduled job.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RTaylor2208

  SSChampion

  Points: 13298

  More actions

  October 5, 2016 at 3:10 am

  #1904831

  If you set this up as a SQL Agent job as Jeff mentioned should give you what you want providing you have dbmail setup on your SQL server:

  --Create a temp table to store the list of DB's and tables with zero rows

  if OBJECT_ID('tempdb..#EmptyTables') IS NOT NULL

  DROP table #EmptyTables

  CREATE TABLE #Emptytables (DBname sysname, TableName sysname)

  GO

  --Use the undocumented system stored procedure to cycle over each db and gather a list of tables with zero rows

  exec sp_MSforeachdb '

  ;WITH CTE_RowCounts AS

  (

  selectt.name,

  SUM ( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) as NoRows

  from [?].sys.dm_db_partition_stats ps

  join [?].sys.tables t

  on ps.object_id = t.object_id

  Group by t.name

  )

  INSERT INTO #EmptyTables

  SELECT ''?'', Name AS Tablename

  FROM CTE_RowCounts

  WHERE NoRows = 0

  '

  --If any tables have zero rows send an email with the details

  IF (SELECT COUNT(1) FROM #Emptytables) > 0

  BEGIN

  DECLARE @Body varchar(MAX)

  DECLARE @EmailRecipient varchar(1000)

  DECLARE @SubjectText varchar(1000)

  DECLARE @ProfileName varchar(1000)

  SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile

  SET @EmailRecipient = 'You@Yourcompany.com'

  SELECT @SubjectText = @@SERVERNAME + ' tables with zero rows !!!'

  SET @Body = N'<H3 style="color:red; font-family:verdana">' + CAST(@@SERVERNAME AS VARCHAR) + ' Tables with zero rows</H3>' +

  N'<table border="3"; style="font-family:verdana; text-align:left; font-size:8pt">' +

  N'<tr><th>DBname</th><th>TableName</th>' +

  CAST ( ( SELECT

  td = DBname, '',

  td = TableName, ''

  FROM #Emptytables

  WHERE DBname NOT IN ('master','msdb','tempdb')

  ORDER BY DBname, TableName

  FOR XML PATH('tr'), TYPE

  ) AS NVARCHAR(MAX) ) +

  N'</table>' ;

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = @ProfileName,

  @recipients = @EmailRecipient,

  @body_format = 'HTML',

  @body = @Body,

  @subject = @SubjectText;

  END

  DROP TABLE #Emptytables

  MCITP SQL 2005, MCSA SQL 2012