CODEWITHSUNDEEP
sql-servert-sqltestingstored-procedures
PAR
PAR

Reputation: 195

How to execute all views in database through stored procedure

We have a problem with our table schema falling out of sync with our view schema. I would like to know how I could have a stored procedure (for Sql Server) that gets all views in the database, and executes each one via select *

Here is what I imagined (pseudo):

Declare x
Set x = Select object from sysobjects where object = view

foreach view in x
sp_execute 'select * from view'

We could then have an automated test that calls this every night. A SqlException would indicated that something was out of sync.

Upvotes: 4

Views: 14250

Answers (3)

SQLMenace
SQLMenace

Reputation: 135151

should work in 2000 and up

select quotename(table_schema) +'.' + quotename(table_name) as ViewNAme,
 identity(int,1,1) as ID
  into #test
  from information_schema.tables
 where table_type = 'view'


declare @Loopid int,@MaxID int


select @LoopID =1,@MaxID =MAX(id) 
from #test

declare @ViewName varchar(100)

while @LoopID <= @MaxID
begin

select @ViewName = ViewNAme 
from #test
where id = @LoopID

exec ('select top 1 * from ' + @ViewName)
set @LoopID = @LoopID + 1
end

drop table #test

I mostly focused on one part of your question, see also how to make sure that the view will have the underlying table changes by using sp_refreshview

Upvotes: 5

gbn
gbn

Reputation: 432611

I'd really suggest you use WITH SCHEMABINDING to prevent this happening.

Or use sp_refreshview in a loop at least.

SELECT * FROM view is not reliable: how do know if the output is correct or not?

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453807

In SQL 2008 you can use the following to detect unresolved dependencies without having to actually select from the view.

SELECT * 
FROM sys.views v
JOIN sys.sql_expression_dependencies e ON e.referencing_id = v.object_id
and  referenced_id is null

Upvotes: 0

Related Questions