Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

List all empty tables in your SQL Server database

From Wiki

Jump to: navigation, search

If you want to quickly get a list of all the tables in your database that are empty you can use the following query.

  1. exec sp_MSforeachtable 'if not exists (select 1 from ?) print ''?'' '


If you want to list all the tables that are not empty, you just have to change from NOT EXISTS to EXISTS

  1. exec sp_MSforeachtable 'if  exists (select 1 from ?) print ''?'' '


You can also combine the two and print is NOT empty for the ones that are not empty and print IS empty for the ones that are empty, below is the code for that.

  1. exec sp_MSforeachtable 'if  exists (select 1 from ?)
  2.                         print ''?'' + ''is NOT empty''
  3.                         else print ''?'' + ''IS  empty '' '


Just so that you understand, the sp_MSforeachtable stored procedure is undocumented, so be careful about using this stored procedure in production code.


Here is another way which does not use an undocumented procedure, this was suggested by Niikola

  1. DECLARE @Cmd NVARCHAR(MAX) = ''                        
  2. SELECT @Cmd += 'UNION ALL SELECT ''[' + schema_name(schema_id) + '].[' + name + ']'' as TableName, Case When Exists (Select 1 from [' + schema_name(schema_id) + '].[' + name + ']) Then ''Empty'' Else ''NOT Empty'' End as isEmpty '   FROM sys.tables WHERE is_ms_shipped=0  
  3. SELECT @Cmd = STUFF(@cmd,1,10,'')
  4. EXEC (@cmd)

Very similar query:

  1. declare @SQL nvarchar(max)
  2.  
  3. select @SQL = coalesce(@SQL + '
  4. UNION ALL
  5. ',';with cte as (') + 'select case when exists (select 1 from ' +
  6. quotename(Table_Name) +') then 1 else 0 end as RecordsExist, '
  7. + QUOTENAME (Table_Name,'''') +  ' as [Table Name] ' from INFORMATION_SCHEMA.TABLES order by TABLE_NAME
  8.  
  9. select @SQL = @SQL + ')
  10. select [Table Name] from cte where RecordsExist = 1 order by [Table Name]'
  11.  
  12. --print @SQL
  13. execute(@SQL)


Contributed by: --SQLDenis 16:26, 9 August 2010 (GMT)

Part of SQL Server Admin Hacks

787 Rating: 2.4/5 (74 votes cast)