TSQL DROPs for temp tables, procs, functions, SQLAgent jobs, synonyms

This is probably the most simple TSQL I will ever post.

However, it becomes useful more often than it would seem.

Many times I will write a script/proc or get someone else’s code which doesn’t do any checking or clean up.

Temp table drop TSQL

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL
	DROP TABLE #tbl

Stored procedure drop TSQL

--proc
IF OBJECT_ID('dbname.dbo.proc1') IS NOT NULL
	DROP PROC dbo.proc1;
GO
--if you do this, then you can just DROP and CREATE all the time
--ALTER is no longer needed.
CREATE PROC proc1
AS
SELECT 1

Function DROP/CREATE TSQL

IF EXISTS 
(
	SELECT *
	FROM sysobjects
	WHERE id = object_id(N'fnFunctionName')
		AND xtype IN 
		(
			N'FN' --FN = Scalar Function
			,N'IF' --IF = Inlined Table Function
			,N'TF' --TF = Table Function 
		)
) 
DROP FUNCTION [dbo].[fnFunctionName] 
GO   

CREATE FUNCTION [dbo].[fnFunctionName] (@strText VARCHAR(1000)) 
RETURNS VARCHAR(1000) AS 
SELECT 1

SQL Agent job drop TSQL

DECLARE @myjobId BINARY (16)

SELECT @myjobId = job_id
FROM msdb.dbo.sysjobs
WHERE NAME = N'job name'

IF (@myjobId IS NOT NULL)
BEGIN
	EXEC msdb.dbo.sp_delete_job @myjobId
END

SELECT @myjobId = job_id
FROM msdb.dbo.sysjobs
WHERE NAME = N’job name’

IF (@myjobId IS NOT NULL)
BEGIN
	EXEC msdb.dbo.sp_delete_job @myjobId
END