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
1 2 | IF OBJECT_ID('tempdb..#tbl') IS NOT NULL DROP TABLE #tbl |
Stored procedure drop TSQL
1 2 3 4 5 6 7 8 9 | --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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 |