Blog Detail

We are working on to synchronize our new SQL instance, with existing database and need to make sure all SQL OBJECTS are created on the SQL server instance, so we can execute the same stored procedures from the new SQL instance.

Our assignment is to create same replica of existing database to new SQL instance. We were also pretty much sure because all our Stored Procedures are also created on the new instance. As soon as we try to execute the stored procedure, we are getting, very simple error like: 
Invalid object name 'XXXXXX'. 

When we debug that, we come to know that TABLES are not created on the new SQL instance because of one error. Our main question here is: How come SQL SERVER allows to create Stored procedures, if table doesn’t exists?

After review it, we come to know that SSMS 2012 has an option, that we can configure this restriction to allow or not to allow, Check for object existence

 

By default, "Check for object existence” is configures as FALSE, so SQL Server allows user to create stored procedure without checking dependent object is exists or not. Lets see one simple demo:

--Checking, if table exists or not. If it is exists, delete the table
if object_id('SQLYoga') IS NOT NULL
    DROP TABLE SQLYoga
--Creating a Stored procdedure which using the object "SQLYoga" which we have just deleted 
CREATE PROC StoredProcedureWithoutObject
As
BEGIN
    SELECT * FROM SQLYoga
END

This is very simple example that first we have checked that, if table called “SQLYoga” exists in the database then drop that table and create a stored procedure which uses this table. As Check for object existence configuration is setup as “to allow”, we are able to create the Stored procedure though table is not exists on the table. Stored procedure won’t be executed and will give an error at the time of execution, as table doesn’t exists. To enable this configuration, go to SSMS->Tools->Options->SQL Server Object Explorer and setup “Check for object existence” to TRUE.

Please let me know your comments