3eLn7ycA

1USE HCDataMart_Devt --OHDSI -- HC_DM --
2GO
3
4BEGIN TRY
5	 EXEC ('CREATE PROCEDURE dbo.USP_Drop_Object AS DECLARE @A varchar(100); SET @A=ISNULL(OBJECT_NAME(@@PROCID), ''unknown'')+'' was not created!''; RAISERROR(@A,16,1);return 9999')
6END TRY BEGIN CATCH END CATCH
7GO
8
9ALTER PROCEDURE dbo.USP_Drop_Object (
10	 @Object_Name NVARCHAR(500)
11	,@Object_Schema NVARCHAR(100) = NULL
12	,@Object_Type	VARCHAR(50) = 'TABLE' -- valid values: 'TABLE', 'VIEW', 'PROCEDURE'
13	,@Debug_Mode INT = 0
14)
15AS
16BEGIN
17/* ===========================================================================================
18Nicole Lindner-Miles
19SP Version: 2018.05.27
20
21Utility SP to conditionally drop an object if it exists. 
22*	Which databases: Drops temp tables (in tempdb "database") or the current database
23	You could add the database as an input parameter (or use PARSENAME() function) to drop
24	objects in other databases, but I would rather not do that.
25*	Schema/Owner: Defaults to dbo if not provided, but will do this for any schema/owner 
26*	Object types: Should work with Table, View, and Procedure
27
28----------------------------------------------------------------------------------------------
29CHANGE LOG
30*	2018.05.27
31	*	Initial creation
32=========================================================================================== */
33	
34	DECLARE @SQL NVARCHAR(MAX)
35
36	SET @SQL = 
37	N'IF OBJECT_ID(N'''
38	+ CASE 
39			WHEN @Object_Name LIKE '#%' THEN N'tempdb..' 
40			WHEN @Object_Schema IS NOT NULL AND @Object_Schema <> N'' THEN QuoteName(@Object_Schema) + N'.'
41			ELSE N'dbo.' 
42		END
43	+ QUOTENAME(@Object_Name)
44	+ N''') IS NOT NULL 
45   BEGIN 
46      DROP '
47		+ @Object_Type + ' '
48		+ CASE WHEN @Object_Schema IS NOT NULL AND @Object_Schema <> N'' THEN QuoteName(@Object_Schema) + N'.' ELSE N'' END
49		+ QUOTENAME(@Object_Name)
50		
51		-- print to the log if we call it with Debug_Mode <> 0
52		+ CASE WHEN @Debug_Mode = 0 THEN N'' ELSE CHAR(10) + CHAR(13) + N' print ''Dropped '
53		+ @Object_Type + N' '
54		+ CASE WHEN @Object_Schema IS NOT NULL AND @Object_Schema <> N'' THEN QuoteName(@Object_Schema) + N'.' ELSE N'' END
55		+ QUOTENAME(@Object_Name)
56   + N'''' END
57   + N'
58   END'
59
60	IF @Debug_Mode <> 0
61	BEGIN
62		PRINT @SQL
63	END
64		
65	EXEC (@SQL)
66END
67
68GO