Execute the following command to drop the IN_DB_UTIL package from a database.
BEGIN
DECLARE
@V_TYPE SYSNAME,
@V_NAME SYSNAME,
@V_SQL NVARCHAR(500),
@V_ERROR NVARCHAR(500),
@V_SCHEMA SYSNAME = 'inuser',
@V_SCHEMA_ID INTEGER,
@V_DROP_PROCS TINYINT = 0;
SET @V_SCHEMA_ID = SCHEMA_ID(@V_SCHEMA);
-- Drop tables unless it appears they are actively being used
IF (SELECT OBJECT_ID(@V_SCHEMA+'.IN_DB_UTIL_SESSION_ATTRIBUTES')) IS NULL SET @V_DROP_PROCS+=1;
ELSE
BEGIN TRY
IF (SELECT COUNT(*) FROM inuser.IN_DB_UTIL_SESSION_ATTRIBUTES WHERE STATUS <> 'IDLE') = 0
BEGIN TRY
PRINT 'DROP TABLE inuser.IN_DB_UTIL_SESSION_ATTRIBUTES;';
DROP TABLE inuser.IN_DB_UTIL_SESSION_ATTRIBUTES;
SET @V_DROP_PROCS+=1;
END TRY
BEGIN CATCH
PRINT CHAR(10)+'NOTICE: Could not drop the IN_DB_UTIL_SESSION_ATTRIBUTES table.'+CHAR(10);
END CATCH;
ELSE
BEGIN;
PRINT CHAR(10)+'NOTICE: There are active sessions currently using the
IN_DB_UTIL_SESSION_ATTRIBUTES table.'+CHAR(10);
END;
END TRY
BEGIN CATCH
END CATCH;
IF (SELECT OBJECT_ID(@V_SCHEMA+'.IN_DB_UTIL_CALLSTACK')) IS NULL SET @V_DROP_PROCS+=1;
ELSE
BEGIN TRY
IF (SELECT COUNT(*) FROM inuser.IN_DB_UTIL_CALLSTACK) = 0
BEGIN TRY
PRINT 'DROP TABLE inuser.IN_DB_UTIL_CALLSTACK;';
DROP TABLE inuser.IN_DB_UTIL_CALLSTACK;
SET @V_DROP_PROCS+=1;
END TRY
BEGIN CATCH
PRINT CHAR(10)+'NOTICE: Could not drop the IN_DB_UTIL_CALLSTACK table.'+CHAR(10);
END CATCH;
ELSE
BEGIN;
PRINT CHAR(10)+'NOTICE: There are active sessions currently using the IN_DB_UTIL_CALLSTACK
table.'+CHAR(10);
END;
END TRY
BEGIN CATCH
END CATCH;
-- Drop programs if both tables were dropped
IF @V_DROP_PROCS = 2
BEGIN
-- Populate the IN_DBUTIL_OBJECTS cursor with function and procedure names
DECLARE IN_DBUTIL_OBJECTS CURSOR FAST_FORWARD FOR
SELECT CASE type_desc
WHEN 'SQL_SCALAR_FUNCTION' THEN 'FUNCTION'
WHEN 'SQL_STORED_PROCEDURE' THEN 'PROCEDURE'
END AS type
,name
FROM sys.objects
WHERE schema_id = @V_SCHEMA_ID
AND name LIKE 'IN_DB_UTIL%'
AND type IN ('P', 'FN')
UNION
SELECT 'TYPE', NAME
FROM sys.types
WHERE schema_id = @V_SCHEMA_ID
AND name LIKE 'IN_DB_UTIL%'
UNION
SELECT 'SYNONYM', NAME
FROM sys.synonyms
WHERE schema_id = @V_SCHEMA_ID
AND base_object_name LIKE '%IN_DB_UTIL%'
ORDER BY 1,2;
-- Open the Cursor
OPEN IN_DBUTIL_OBJECTS
-- Start Fetching rows from the cursor
FETCH NEXT FROM IN_DBUTIL_OBJECTS INTO @V_TYPE, @V_NAME;
-- Loop through all the rows in the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @V_SQL = N'DROP '+@V_TYPE+' '+@V_SCHEMA+'.'+@V_NAME+';';
PRINT @V_SQL;
EXECUTE (@V_SQL);
END TRY
BEGIN CATCH
PRINT @V_SQL;
SELECT @V_ERROR = N'ERROR_NUMBER:
'+CONVERT(NVARCHAR,ERROR_NUMBER())+NCHAR(10)+N'ERROR_MESSAGE: '+ERROR_MESSAGE();
PRINT @V_ERROR;
END CATCH;
FETCH NEXT FROM IN_DBUTIL_OBJECTS INTO @V_TYPE, @V_NAME;
END;
CLOSE IN_DBUTIL_OBJECTS;
DEALLOCATE IN_DBUTIL_OBJECTS;
END;
ELSE
-- IN_DB_UTIL tables must be in use so do not proceed
BEGIN
-- Display IN_DB_UTIL sessions for all identifiers
EXEC inuser.IN_DB_UTIL_SP_SESSIONS_DISPLAY 'IN_DB_UTIL';
EXEC inuser.IN_DB_UTIL_SP_SESSIONS_DISPLAY 'ALL';
PRINT NCHAR(10)+'NOTICE: The IN_DB_UTIL package was not removed since it appears that it may be
currently in use.'+NCHAR(10);
END;
END;
GO