Syntax check views and stored procs
-
Hi all
How to i validate the syntax of all views & stored procs in my DB?
Ive had numerous senarios where view and stored proc code has
syntax errors, ie. referencing missing objects, or invalid users etc.
Its not until you run them, OR, script and try and re-create do you
find the errors... so.. how can I pre-empt this? (sp_recompile is
not the answer).
A classic one is the reported "owner" in sysobjects is, say, DBO, but
the create view statement as another owner!
I just like to say as well, EE when editing stored procs is crap! the syntax checker is way too basic, sometimes it can validate queries, and other times (im not talking dynamic sql here) it doesnt get close.
Sorry, im getting frustrated here! remain
chris..Cheers
Chris k
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
For the views I guess you could loop through and do a select from each, log the ones that fail. Procs a little harder, you'd have to run each inside a transaction (rollback at end) to prevent unwanted changes during testing.
Andy
-
You can also use sp_depends to help with views to check their dependencies. Also I posted a script awhile back to go thru DB objects and help you find strings in them located at http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=184&CategoryNm=T-SQL%20Aids&CategoryID=19 .
What you do wih it is say you have a table tblX and you plan to make a change you run the procedure looking for tblX as your search criteria and it will return the objects with that in there text. Views, Procedures, Triggers, Functions can be searched to help you find the items that may need to be updated in addition to the table.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply