Post reply

Check All Stored Procedures

  • August 30, 2005 at 12:54 pm

    #165831

    I need to move several databases. All of which have a couple hundred stored procedures. Each DB has a few stored procedures that are not used by the application but are still in the DB. Over time the tables have changed for the stored procedures so when I go to move the database I get an error because the stored procedure is not valid anymore.
     
    Is there a SQL statement that will loop though all of the stored procedures checking to see if they will still compile? I can open each one up by hand and click "check Syntax" but this will take along time.
     
    Thanks you!

  • August 30, 2005 at 1:09 pm

    #586276

    Can you make a backup of the db and restore it under a new name?

  • August 30, 2005 at 1:17 pm

    #586277

    Yes I can. I'm moving the DB's to a new server. Would I still need to restore under a new name?

  • August 30, 2005 at 1:22 pm

    #586279

    I never tried this but I think it's possible :

    Drop all the procs on the copied db. Then check out yesterday's articles on how to execute a result set.

    Then you'll be able to do something like this (I hope) :

    exec xp_execresultset 'Select text from olddb.dbo.SysComments where (proc only)'

    now all you'd have to do if to check for errors, or missing sps.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply