Checking all views for errors after upgrade

[sherkozmo]

I just upgraded from SQL 7.0 to 2000. I know I have an error with the
CAST and CONVERT functions I used in some views. Anyway, is there a
sp I can run that will run all my views and give me back a list of
those which have an error instead of going through each view one by
one?

Thanks.
Sherry

[Erland Sommarskog]

[posted and mailed, please reply in news]

sherkozmo (sk*******@sfmc-gi.org) writes:

I just upgraded from SQL 7.0 to 2000. I know I have an error with the
CAST and CONVERT functions I used in some views. Anyway, is there a
sp I can run that will run all my views and give me back a list of
those which have an error instead of going through each view one by
one?

SELECT 'SELECT * FROM ' + name + char(13) + char(10) + 'go'
FROM sysobjects
WHERE xtype = 'V'
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY name

Run from Query Analyzer with results set to text. Cut and paste result
into query window.

I added the go between each SELECT, as it is likely that SQL Server will
abort the batch in case of an error and not run remaining views.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mountain man]

"sherkozmo" <sk*******@sfmc-gi.org> wrote in message
news:3f*************************@posting.google.co m...

I just upgraded from SQL 7.0 to 2000. I know I have an error with the
CAST and CONVERT functions I used in some views. Anyway, is there a
sp I can run that will run all my views and give me back a list of
those which have an error instead of going through each view one by
one?

You can bundle all your views into one script file by EM scripting.
Whether this is an advantage in examining them all at once?
This can be tested by creating a test database etc.


Pete Brown
Falls Creek
Oz