Checking all views for errors after upgrade

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[posted and mailed, please reply in news]

sherkozmo (skosmicki@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"sherkozmo" <skosmicki@.sfmc-gi.org> wrote in message
news:3ff08a65.0401060912.728b917@.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