SSMS option to check object existence in SQL scripts

How to add or remove the object existence checks in SQL creation scripts with SQL Server Management Studio? To add dynamically the existence check before generating the create statement for you SQL Objects like tables, views,  functions or stored procedures, it’s easy. Simply activate this SSMS option to check object existence in your SQL Server Management Studio software.

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.

How to add automatically an existence check script to your SQL Server scripts generated with SSMS?

To do so, you just need to activate this SSMS option to check object existence:

  1. From the top left 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] AS' 
END
GO

ALTER PROCEDURE [dbo].YourStoredProcedure 
AS 
BEGIN
SELECT GETDATE()
END 
GO

Note: In case the object does not exists, it’s simply creating an empty stored procedure and then simply using an alter statement on the same object, in order to limit the length of the script.

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.

This section of the blog is about the common SQL Server Errors like arithmetic overflow for example.

Be the first to comment

Leave a Reply

Your email address will not be published.


*