-- This script will delete the metadata for all Devices in the system.

-- NOTE:  If necessary, change the following line to match the name of the ApplicationModules database on your system.
USE [ApplicationModules]

-- ** PART #1:  Gather the information required to delete entries from the database. **

-- Determine the target ID for Device metadata, which will be used to find all metadata for Devices.
DECLARE @DeviceMetadataId INT
SET @DeviceMetadataId = (SELECT [TargetTypeId] FROM [Metadata].[MetadataTargetType] WHERE [TargetType] = N'Device')

-- ** PART #2:  Delete the entries from the database.  **
BEGIN TRY
	BEGIN TRAN
	
	-- Delete the Device metadata, starting with entries from the [MetadataItemValue] table.
	DELETE FROM
		[Metadata].[MetadataItemValue]
	WHERE
		[MetadataItemId] IN
		(
			SELECT
				DISTINCT MI.[MetadataItemId]
			FROM
				[Metadata].[MetadataItem] MI
			JOIN
				[Metadata].[MetadataItemValue] MIV
				ON MIV.[MetadataItemId] = MI.[MetadataItemId]
			WHERE
				MI.[TargetTypeId] = @DeviceMetadataId
		)

	-- Next, delete entries from [MetadataItem].
	DELETE FROM
		[Metadata].[MetadataItem]
	WHERE
		[MetadataItemId] NOT IN 
		(
			SELECT
				DISTINCT [MetadataItemId]
			FROM
				[Metadata].[MetadataItemValue]
		)

	-- Finally, delete entries from [MetadataValue].	
	DELETE FROM
		[Metadata].[MetadataValue]
	WHERE
		[MetadataValueId] NOT IN
		(
			SELECT
				DISTINCT [MetadataValueId]
			FROM
				[Metadata].[MetadataItemValue]
		)
		
	DELETE FROM
		[Metadata].[MetadataValue]
	WHERE
		[MetadataNameId] IN
		(
			SELECT
				[MetadataNameId]
			FROM
				[Metadata].[MetadataName]
			WHERE
				[Name] LIKE 'Device.MDName.%'
		)
	
	-- Commit the transaction, since the deletions were successful.
	IF (@@TRANCOUNT > 0)
	BEGIN
		COMMIT
	END
END TRY
BEGIN CATCH
	-- Because the deletions failed in some way, roll back the transaction.
	IF (@@TRANCOUNT > 0)
	BEGIN
		ROLLBACK
	END
	
	-- Output error information to describe what went wrong.
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_STATE() AS ErrorState,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;

END CATCH