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