Is there a way to get the SQL server to check object references in Stored Procs?

Question

The following code works 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 a way to compile or verify that the stored Proc is valid?

Answer 1

I just discovered that VS2010 with database projects will do syntax and name checks. Seems to be the best option.

Answer 2

you can use

SET FMTONLY ON
EXEC dbo.My_Proc
SET FMTONLY OFF

- , -, .

, , .

Answer 3

, . :

sysdepends , "dbo.nonexistenttable". .

- , , , . , , .

SQL Server , , . , , sp_depends sp_MSdependencies, , .

, -, :

CREATE PROCEDURE usp_Broken
AS

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

"FROM xxx", :

CREATE PROCEDURE usp_Broken2
AS

SELECT *
FROM
    NonExistentTable

.

SET FMTONLY ON, , , "" . . , ​​:

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

, SomeTable ID = 1, Name, - . , SET FMTONLY ON/OFF.

, FMTONLY ON , IF/THEN/ELSE, , .

- , :

BEGIN TRAN

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

ROLLBACK

script , ( CATCH) . , , IDENTITY, (). -, .

, 50- .

Answer 4

( , . )

:

Erland Sommarskog MS Connect SET STRICT_CHECKS ON

( ):

. ,

Answer 5

sp_depends (. http://msdn.microsoft.com/en-us/library/ms189487.aspx) (http://msdn.microsoft.com/en-us/library/ms186778.aspx), , . , (http://msdn.microsoft.com/en-us/library/aa214346(SQL.80).aspx), , .

Answer 6

information_schema.tables, , ,

 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'

, /

.INFORMATION_SCHEMA.ROUTINES.Routine_name proc/function

Answer 7

SQL 2005 try/catch:

BEGIN TRANSACTION

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

, SSMS, SP, . . .