Script to check the Syntax on all stored procedures in a database
in [Programming SQL]
|
Prev: OPENROWSET/OPENDATASOURCE with Excel 2007/SQL 2005
Next: SQL 2008 - FILESTREAM OpenSQLFilestream invalid handle
From: opokad06 on 6 Nov 2009 15:18 Is there a script or functionality in SQL Server 2000 that can check the syntax on all stored procedures in a database? A column was dropped from a table and the list of stored procedures using that column was identified and fixed using sp searchcode. That is not sufficient for the decision makers and they want more..... a script or something in SQL Server that will check the syntax on all stored procs in the database as a way to verify that nothing borke by deleting the column. Any ideas would be greatly appreciated. Thanks. -- KPoku
From: Eric Isaacs on 6 Nov 2009 15:59 Script out all the stored procedures in the database and change the CREATE PROCEDURE to ALTER PROCEDURE (or script it out as ALTER PROCEDURE) and run the script. If the script errors, there's a problem with that procedure. If it's scripted as ALTER PROCEDURE, it won't drop the existing procedure before it attempts to change it, so the existing procedure will stay in place, even though it's technically broken. So if the procedure works partially, it should continue to work that way. You can always replace your stored procedures, those types of scripts you can run anytime without affecting the integrity of the db. But if you drop a procedure and try to recreate it and an error occurs, that procedure has been dropped and no longer exists, so application errors will likely occur due to the missing procedure. ALTER PROCEDURE helps to address this because it won't replace the procedure unless the alteration compiles. -Eric Isaacs
From: Erland Sommarskog on 7 Nov 2009 02:55
opokad06 (opokad06(a)discussions.microsoft.com) writes: > Is there a script or functionality in SQL Server 2000 that can check the > syntax on all stored procedures in a database? > > A column was dropped from a table and the list of stored procedures using > that column was identified and fixed using sp searchcode. > > That is not sufficient for the decision makers and they want more..... > > a script or something in SQL Server that will check the syntax on all > stored procs in the database as a way to verify that nothing borke by > deleting the column. The problem is that if you have a query with a temp table that refers to this dropped column, SQL Server will not scream blue murder, until you actually run the code. Since the temp table does not exist when you create the procedure, SQL Server will defer checking the statement until the temp table exists. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |