Check if object exists with SSMS

How to add or remove a script to check if an object exists in SQL creation scripts generated with SQL Server Management Studio (SSMS)? 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.

Automatically add a script to check if an object exists with SQL Server Management Studio (SSMS)

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.

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:

1. Script Stored Procedure AS
2. Then CREATE TO
3. And New Query Editor Window

The script automatically generated by SSMS looks like this one:

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 SELECT 1 as [One]' 
END
GO

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

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

It’s very useful to use error free scripts for a stored procedure. For example because it is not needed to handle any data content or data structure, like for tables for example.

Indeed for tables and especially sensitive or business critical data, it is mandatory to manage a migration script, generate and backup the table contrent before dropping and recreating a SQL table.

So it is definitely a good practice for SQL projects to check if object exists in the SQL database. So generate the scripts with SSMS or manually and use them. In some cases, it is useful to stop the script if the table already exists to prevent dropping a table by mistake.

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