THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

A script to check for the existence of data in tables or views

This is the second time I have needed this, so that elevates it to blog status (code for mine and your reuse being the second most important reason I blog.  The most important one is the notoriety, glamour, and big bucks :)

I have a database that I have recently needed to check to discover if data existed in them, and in some cases, views that might have errors in them.  So the following query builds a temp table, then loads the temp table using a query like this query (using AdventureWorks.  I built this query looking only at views.)

select 'if object_id(''tempdb..#tableStatus'') is not null drop table #tableStatus'
union all
select 'go'
union all
select 'set nocount on'
union all
select 'create table #tableStatus (tableName sysname primary key, dataExistsFlag bit,error varchar(1000) NULL)'
union all
select ' begin try exec(''insert into #tableStatus(tableName, dataExistsFlag) select '''
              + quoteName(schema_name(schema_id) + '.' + name,'''')
              + ''',case when exists (select * from ' + quoteName(schema_name(schema_id)) + '.'
              + quoteName(name) + ') then 1 else 0 end '') end try
           begin catch insert into #tableStatus (tableName, dataExistsFlag,error) 
                   select ' + quoteName(schema_name(schema_id) + '.' + name,'''') + ',0,
                                 ERROR_MESSAGE() end catch'
from sys.objects
where is_ms_shipped = 0
and type = 'V'
--and type = 'U'
--and name like '%'

This returns the following (abbreviated) result set, which is a batch of its own:

if object_id('tempdb..#tableStatus') is not null drop table #tableStatus
go
set nocount on
create table #tableStatus (tableName sysname primary key, dataExistsFlag bit,
                                       error varchar(1000) NULL)
begin try 
       exec('insert into #tableStatus(tableName, dataExistsFlag) 
                select ''Person.vAdditionalContactInfo'',
                 case when exists (select * from [Person].[vAdditionalContactInfo]) then 1 else 0 end ')
end try
begin catch 
          insert into #tableStatus (tableName, dataExistsFlag,error) 
          select 'Person.vAdditionalContactInfo',0,ERROR_MESSAGE()
end catch
begin try 
       exec('insert into #tableStatus(tableName, dataExistsFlag) 
                select ''HumanResources.vEmployee'',
                 case when exists (select * from [HumanResources].[vEmployeeDepartment])
                                   then 1 else 0 end ')
end try
begin catch 
          insert into #tableStatus (tableName, dataExistsFlag,error) 
          select 'HumanResources.vEmployee',0,ERROR_MESSAGE()
end catch

And then execute a query like the following to see what you have:

select *
from  #tableStatus

tableName                                                 dataExistsFlag   error
---------------------------------------------------  ------------------  -----------------
HumanResources.vEmployee                     1                       NULL
HumanResources.vEmployeeDepartment  1                       NULL

You can tweak the code easily enough to do actual counts (or whatever), but that can be slow and my need is usually very simple.  The error column is useful for view validation for the case where the view cannot be executed due to binding issues.

Crossposted to http://drsql.spaces.live.com

Published Friday, November 03, 2006 9:55 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Scott Bateman said:

What a sweet script.  Thanks, Louis!

January 18, 2008 12:56 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement