Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > User Device Tracker (UDT) > Remove UDT from the database

Remove UDT from the database

Table of contents

Updated September 23, 2016

Overview

This article provides steps to remove UDT tables, views, functions and procedures from a customer's database. 

Environment

UDT v1  and later

Steps

Warning: SolarWinds strongly recommends that you create a backup of your data. Executing the following steps will remove all UDT functions from your database. 

  1. Open SQL Management Studio.
  2. Run Query 1.
  3. Refresh the database to verify if the UDT tables, views, and functions are still present. 
    Note: Run the following query if the UDT functions are still present after Query 1 is finished. 
  4. Run Query 2.
    Note: Running Query 2 will result to another set of queries. 
  5. Copy the results from Query 2.
  6. Open a new query and paste the results of Query 2.
  7. Run the new query. This will remove all procedures.

 

Query 1:

DECLARE @table_object_id int
DECLARE @table_name varchar(max)
DECLARE @table_type varchar(max)
DECLARE @fk_name varchar(max)
DECLARE @fk_parent_name varchar(max)
DECLARE @stmt nvarchar(max)
DECLARE table_cursor CURSOR FAST_FORWARD FOR
SELECT o.object_id, o.name, o.[type]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE s.name='dbo' AND o.[type] IN ('U','V') AND o.name LIKE 'UDT%'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_object_id, @table_name, @table_type
WHILE @@FETCH_STATUS=0
BEGIN
    DECLARE fk_cursor CURSOR FAST_FORWARD FOR
    SELECT fk.name, parent.name
    FROM sys.foreign_keys fk
    JOIN sys.objects parent ON fk.parent_object_id=parent.object_id
    JOIN sys.schemas fk_s ON fk.schema_id=fk_s.schema_id
    JOIN sys.schemas parent_s ON parent.schema_id=parent_s.schema_id
    WHERE fk_s.name='dbo' AND parent_s.name='dbo' AND fk.referenced_object_id=@table_object_id
    OPEN fk_cursor
    FETCH NEXT FROM fk_cursor INTO @fk_name, @fk_parent_name
    WHILE @@FETCH_STATUS=0
    BEGIN
 
SET @stmt = 'ALTER TABLE [dbo].['+@fk_parent_name+'] DROP CONSTRAINT ['+@fk_name+']'
EXEC [dbo].sp_executesql @statement = @stmt
            FETCH NEXT FROM fk_cursor INTO @fk_name, @fk_parent_name
    END
    CLOSE fk_cursor
    DEALLOCATE fk_cursor
    SET @stmt = CASE @table_type WHEN 'V' THEN 'VIEW' ELSE 'TABLE' END
    SET @stmt = 'DROP '+@stmt+' [dbo].['+@table_name+']'
    EXEC [dbo].sp_executesql @statement = @stmt
    FETCH NEXT FROM table_cursor INTO @table_object_id, @table_name, @table_type
END 
CLOSE table_cursor
DEALLOCATE table_cursor
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetUdtDbVersion]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[GetUdtDbVersion]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_FormatMACAddressForUI]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_FormatMACAddressForUI]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetTableRowCount]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetTableRowCount]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetDateTimeOfLastSuccessfulPoll]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetDateTimeOfLastSuccessfulPoll]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetMaxDataRetainDate]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetMaxDataRetainDate]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetRetainStatsDailyDate]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetRetainStatsDailyDate]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetRetainStatsDetailDate]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetRetainStatsDetailDate]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_fxnGetAllNetworkConnectionByDNS]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_fxnGetAllNetworkConnectionByDNS]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_fxnGetAllNetworkConnectionByIP]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_fxnGetAllNetworkConnectionByIP]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_fxnGetAllNetworkConnectionByMAC]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_fxnGetAllNetworkConnectionByMAC]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetCurrentPortHistory]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetCurrentPortHistory]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetHistoryPortHistory]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetHistoryPortHistory]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetIpEndpointCurrentConnections]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetIpEndpointCurrentConnections]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetIpEndpointHistoryConnections]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetIpEndpointHistoryConnections]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetMacEndpointCurrentConnections]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetMacEndpointCurrentConnections]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_fxnGetMacEndpointHistoryConnections]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_fxnGetMacEndpointHistoryConnections]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionCurrentByDNS]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionCurrentByDNS]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionCurrentByDNSForL2L3]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionCurrentByDNSForL2L3]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionCurrentByIP]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionCurrentByIP]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionCurrentByMAC]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionCurrentByMAC]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionHistoryByDNS]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionHistoryByDNS]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionHistoryByDNSForL2L3]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionHistoryByDNSForL2L3]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionHistoryByIP]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionHistoryByIP]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_GetAllNetworkConnectionHistoryByMAC]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_GetAllNetworkConnectionHistoryByMAC]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetLatestHistoryDNSForWatchID]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetLatestHistoryDNSForWatchID]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetLatestHistoryIPForWatchID]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetLatestHistoryIPForWatchID]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetLatestHistoryMACForWatchID]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetLatestHistoryMACForWatchID]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_IPAM_fxnGetMACCurrentInfo]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_IPAM_fxnGetMACCurrentInfo]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UDT_IPAM_fxnGetMACInfo]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[UDT_IPAM_fxnGetMACInfo]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_SplitToTable]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_SplitToTable]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_GetRetainStatsHourlyDate]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_GetRetainStatsHourlyDate]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[udt_IsColumnPartOfKey]') AND xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[udt_IsColumnPartOfKey]
 

Query 2:

 

Select 'drop procedure ' + name from sys.procedures where name like 'UDT%'

 

 

Last modified
20:03, 22 Sep 2016

Tags

Classifications

Public