SP_Refreshview on views on all databases

Rainbow002
Rainbow002 used Ask the Experts™
on
After restoring databases, the views need to be refreshed. I can do it individually by sp_refreshview 'view'. Can yuo suggest a way to refresh all views on all databases?

Also, how can I run DBCC CHECKDB (db name) WITH DATA_PURITY  on all databaes?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Could you explain why you need to run sp_refreshview after restoring a DB?

To refresh all the views you would need to write a cursor and generate an execute statement to run sp_refreshview against each view
To run sp_refreshview for each view, the gothamite was right.  You'll create a CURSOR to run over all views.  You'll need

SELECT name FROM sysobjects WHERE xtype='v'

If you need the full cursor, let me know.

To run the DBCC CHECKDB for each database:

exec sp_MSforeachdb @command1='DBCC CHECKDB ? WITH DATA_PURITY'

Hi run this sentence   . Then copy and run the result text again
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V'
Top Expert 2011
Commented:


generate the views requiring refresh via...

select 'exec sp_refreshview N'''+o.name+''''
from sysobjects as o
where objectproperty(id,'isSchemaBound')=0
and type='V'
order by 1

use sp_msforeachdb  to run the dbcc checkdb command

Author

Commented:
For some reason after I restored databasases one environment to another the views didn't contain correct data however after executing sp_refresh view for those views on couple of DBs, the issue was resolved and we were seeing the expected resultset not sure if it has to do with service pack difference. I'll try the above statements but does the issue make sense or is it just me who experienced it?

Author

Commented:
Just a note about: exec sp_MSforeachdb @command1='DBCC CHECKDB ? WITH DATA_PURITY'

question mark is surrounded by small brackets (?).

so it would be: sp_MSforeachdb @command1='DBCC CHECKDB (?) WITH DATA_PURITY'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial