CREATE   PROCEDURE [dbo].[unregisterSite] (
  @SiteID uniqueidentifier
) AS
BEGIN TRAN
DECLARE @SQLStatement nvarchar(2000)
DECLARE @MachineName nvarchar(15)
DECLARE @SiteIndex smallint
DECLARE @TableName nvarchar(25)

-- First delete the entries from the child table so as
-- not to cause any Foreign key constraint violations.
DELETE FROM LogSource WHERE [Site] = @SiteID
-- Then select the machine name and site index from the
-- log site table that corresponds to this registered
-- site so we can build our SQL statement dynamically.
SELECT @MachineName = [MachineName], @SiteIndex = [SiteIndex] FROM
  LogSite WHERE [ID] = @SiteID
-- Remove the site entry.
DELETE FROM LogSite WHERE [ID] = @SiteID

SET @TableName = @MachineName + N'W3SVC' + CONVERT(nvarchar(5), 
  @SiteIndex)

-- And finally, drop the entries table for this site.
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE [ID] = OBJECT_ID(@TableName)
  AND OBJECTPROPERTY([ID], N'IsUserTable') = 1)
  BEGIN
    SET @SQLStatement = N'DROP TABLE ' + @TableName
    EXECUTE sp_executesql @SQLStatement
    SET @SQLStatement = N'DROP VIEW vw_' + @TableName
    EXECUTE sp_executesql @SQLStatement
  END

EXIT_BATCH:
IF (@@ERROR <> 0)
  BEGIN
    ROLLBACK TRAN
  END
ELSE
  BEGIN
    COMMIT TRAN
  END