T-SQL- Dropping SQL Objects Cleanly

For today's blog post, we want to help solve this common problem: how to clean up custom objects like #Temp or ##Temp tables in a SQL database when they are no longer needed.

Introduction

As we create custom objects in a SQL database like the DR, we need to be mindful of cleaning them up when they are no longer needed, or when we need to clear an old object out, before re-creating it.

The first type of SQL object we need to clean up that comes to mind are:

#Temp or ##Temp tables

TEMP tables in SQL have a number of quirks necessitating housekeeping:

1. The Microsoft document implies that #temp tables clean up automatically, but this is not always true. The question revolves around “when the session ends”, which may not be when you think it does.
2. Global ##temp tables are even “stickier” than #temp tables.
3. Both kinds of temp tables consume tempdb space, and you don’t want that to blow out, so you need to clean up after yourself.

WARNING! Here’s an extra word of caution. If you have a stored procedure that creates a #temp table, and then that SP invokes another SP that also creates a #temp table with the same name, on some versions of SQL Server you will not get a second temp table with a different structure. Instead, the second SP will be given the first #temp table and may then fail due to missing fields or primary key violations.

The duplicated CREATE TABLE statement in the second SP will not error out. Despite what the MS documentation says, the engine will not add a numeric suffix to differentiate them internally. Instead you'll get an unexpected error and won’t know why.

As a result, it’s best practice to name each #temp table uniquely when you have SP’s calling SP’s.

Solution

Given all that, there are two ways to clean up the #temp tables after you’re done with them:

In pre-SQL 2016 environments you add code like this at the bottom of your stored procedure or script:

IF OBJECT_ID('tempdb.dbo.#DS') IS NOT NULL DROP TABLE #DS;

NOTE! When you do this, make sure to confirm that your temp database is really called tempdb.

In a SQL 2016+ environment, things are easier! A new clause has been added to the DROP TABLE syntax, so you can do this instead:

DROP TABLE IF EXISTS #Docs, #DS, #Templates, #ResultSet ;

So much better! We can just send a whole list of #temp tables, and they are dropped if they exist, and no error if they do not. Plus, we don’t have to worry about tempdb not being the temp db!

But wait… if they added this for #temp tables, did they add it for other objects as well?

Yes, they did!

We also recommend DROP-ing Procedures, Views, Functions and so on when you need to re-create them.

There are a number of reasons for this approach:

1. Any statistics or stored execution plans for an SP, view, function, etc. are cleared out when you drop the parent object, so this is a handy way to get a refreshed plan.
2. If the parameter list on an sp or function changes, you may have to drop it to get the new parameters to show up.
3. Sometimes it seems that ALTER does not work as expected, and DROP gets you a clear start.

In pre-SQL 2016 versions, you need to do that DROP like this:

For a scalar function:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT [type]
            FROM  sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[FunctionName]')
                  AND [type] IN ( N'FN', N'IF',N'FS' ) )
BEGIN
    DROP FUNCTION [dbo].[FunctionName]
END ;
GO

For a table-valued function:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT [type]
           FROM sys.objects
           WHERE object_id = OBJECT_ID(N'[dbo].[YourFunctionName]')
                 AND [type] IN ( N'FT',N'TF' ) )
BEGIN
    DROP FUNCTION [dbo].[YourFunctionName]
END ;
GO

For a stored procedure:

USE [zcus];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS ( SELECT *
          FROM  sys.objects
          WHERE object_id = OBJECT_ID(N'[dbo].[YourProcedureName]')
                AND type IN ( N'P', N'PC' ) )
BEGIN
    DROP PROCEDURE [dbo].[YourProcedureName]
END ;
GO

And finally for a view:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT *
            FROM  sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[YourViewName]')
                  AND [type] IN ( N'V' ) )
BEGIN
    DROP VIEW [dbo].[YourViewName]
END ;
GO

Note that in this approach we need to switch to the database and also set the [type] of the object we are looking for. And there is a fair bit of code to cut/paste/edit into each object script.

Post-SQL 2016 we have a much cleaner approach:

For a scalar function:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP FUNCTION IF EXISTS [dbo].[YourScalarFunctionNameHere] ;
GO

For a stored procedure:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS [dbo].[ReplaceWithYourSpName] ;
GO

For a table-valued function:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP FUNCTION IF EXISTS [dbo].[TableValuedFunctionNameHere] ;
GO

And for a view:

USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP VIEW IF EXISTS [dbo].[YourViewNameHere] ;
GO

There you go!

Extra Credit

Make code templates for each kind of object and always start from them. Then the DROP code can be built in and you won’t have to hunt for it!

Update all of your off-line copies of your code (you ARE keeping an off-line copy, right? Of every single custom SQL object in your database? If not, do that RIGHT NOW. Always work on an off-line copy and update it into LIVE).

And…

As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!