Check automatically if an SQL Server object already exists before creation with SSMS option

Add or remove the object existence checks in SQL creation scripts with SQL Server Management Studio 2012? To add dynamically the existence check before generating the create statement for you SQL Objects like tables, views,  functions or stored procedures, change these options of your SSMS 2012 client.

 

For example, when developing SQL Server Stored procedures, you need to check their existence first, to avoid this error messages. It’s very useful when you have to change big stored procedures and deploying many times on the server for tests purposes.

For a Table, a View a Stored Procedure or a Function, the message is similar:

Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘Your-Table’ in the database.

Add automatically a script to check if the object you are creating already exists in the database?

  1. To do so, you just need to change the options, from the top menu, click “Tools > Options”.
  2. In the “SQL Server Object Explorer” section, chose “Scripting”.
  3. Then set the “Check for object existence” option to “True”.

 

Then it’s adding this existence check before the code creation of your procedure. To generate the code, right click on any object, for example, a stored procedure and select

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourStoredProcedure]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[YourStoredProcedure]
(


 

 

 

 

It’s very useful for a stored procedure for example because you don’t need to handle any data content or data structure, like for tables for example.

 

Be the first to comment

Leave a Reply