Is there a way to have SQL server validate object references in Stored Procs?

Question

The following code executes fine in SQL Server

create proc IamBrokenAndDontKnowIt as
select * from tablewhichdoesnotexist

Of course if I try to run it, it fails with

Invalid object name 'tablewhichdoesnotexist'.

Is there any way to compile or verify that Stored Proc are valid?

Answer 1

You could run sp_depends (see http://msdn.microsoft.com/en-us/library/ms189487.aspx) and use that information to query the information schema (http://msdn.microsoft.com/en-us/library/ms186778.aspx) to see if all objects exist. From what I read here (http://msdn.microsoft.com/en-us/library/aa214346(SQL.80).aspx) you only need to check referenced tables, which is doable.

Answer 2

You can use

SET FMTONLY ON
EXEC dbo.My_Proc
SET FMTONLY OFF

You'll need to capture the error(s) somehow, but it shouldn't take much to put together a quick utility application that takes advantage of this for finding invalid stored procedures.

I haven't used this extensively, so I don't know if there are any side-effects to look out for.

Answer 3

You used to get a warning message when you tried to create a stored procedure like that. It would say:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.nonexistenttable'. The stored procedure will still be created.

For some reason I'm not getting it now, I'm not sure if it's been changed or if there's just some setting that turns the warning on or off. Regardless, this should give you a hint as to what's happening here.

SQL Server does track dependencies, but only dependencies which actually exist. Unfortunately, none of the dependency tricks like sp_depends or sp_MSdependencies will work here, because you're looking for missing dependencies.

Even if we could hypothetically come up with a way to check for these missing dependencies, it would still be trivial to concoct something to defeat the check:

CREATE PROCEDURE usp_Broken
AS

DECLARE @sql nvarchar(4000)
SET @sql = N'SELECT * FROM NonExistentTable'
EXEC sp_executesql @sql

You could also try parsing for expressions like "FROM xxx", but it's easy to defeat that too:

CREATE PROCEDURE usp_Broken2
AS

SELECT *
FROM
    NonExistentTable

There really isn't any reliable way to examine a stored procedure and check for missing dependencies without actually running it.

You can use SET FMTONLY ON as Tom H mentions, but be aware that this changes the way that the procedure is "run". It won't catch some things. For example, there's nothing stopping you from writing a procedure like this:

CREATE PROCEDURE usp_Broken3
AS

DECLARE @TableName sysname

SELECT @TableName = Name
FROM SomeTable
WHERE ID = 1

DECLARE @sql nvarchar(4000)
SET @sql = N'SELECT * FROM ' + @TableName
EXEC sp_executesql @sql

Let's assume you have a real table named SomeTable and a real row with ID = 1, but with a Name that doesn't refer to any table. You won't get any errors from this if you wrap it inside a SET FMTONLY ON/OFF block.

That may be a contrived problem, but FMTONLY ON does other weird things like executing every branch of an IF/THEN/ELSE block, which can cause other unexpected errors, so you have to be very specific with your error-handling.

The only truly reliable way to test a procedure is to actually run it, like so:

BEGIN TRAN

BEGIN TRY
    EXEC usp_Broken
END TRY
BEGIN CATCH
    PRINT 'Error'
END CATCH

ROLLBACK

This script will run the procedure in a transaction, take some action on error (in the CATCH), and immediately roll back the transaction. Of course, even this may have some side-effects, like changing the IDENTITY seed if it inserts into a table (successfully). Just something to be aware of.

To be honest, I wouldn't touch this problem with a 50-foot pole.

Answer 4

No (but read on, see last line)

It's by design: Deferred Name Resolution

Erland Sommarskog raised an MS Connect for SET STRICT_CHECKS ON

The connect request has a workaround (not tried myself):

Use check execution plan. The only weakness is that you may need permissions to see the execution plan first

Answer 5

You could check information_schema.tables to check whether a table exist or not and then execute the code

here is quickly slung function to check

 create function fnTableExist(@TableName varchar(64)) returns int as
    begin
        return (select count(*) from information_schema.tables where table_name=@tableName and Table_type='Base_Table')
    end

go        

    if dbo.fnTableExist('eObjects') = 0 
        print 'Table exist'
    else
        print 'no suchTable'

like wise you can check for existance of Stored procs / functions in

.INFORMATION_SCHEMA.ROUTINES.Routine_name for th name of the Stored proc/function

Answer 6

In SQL 2005 or higher, you can test a stored procedure using transactions and try/catch:

BEGIN TRANSACTION

BEGIN TRY
  EXEC (@storedproc)
  ROLLBACK TRANSACTION
END TRY
BEGIN CATCH
  WHILE @@TRANCOUNT > 0
    ROLLBACK
END CATCH

The algorithm to test all stored procedures in a database is a bit more complicated, as you need to workaround SSMS restrictions if you have many SPs which return many result sets. See my blog for complete solution.

Answer 7

I've just discovered that VS2010 with database projects will do syntax and name reference checking. Seems like the best option.