SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

IF NOT EXISTS ( SELECT  *
                FROM    sys.procedures
                WHERE   object_id = OBJECT_ID(N'[dbo].[ReportProc_ActiveSettingsList_Alt]', 'P') )
    EXEC sp_executesql N'CREATE PROCEDURE [dbo].[ReportProc_ActiveSettingsList_Alt] AS SELECT 1;';
GO

ALTER PROCEDURE [dbo].[ReportProc_ActiveSettingsList_Alt]
@IsWatched TINYINT
AS
-- Active Settings List

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- CREATE THE TEMP TABLE

CREATE TABLE #SettingsList
    (
        [ID] [int] IDENTITY
            (
                1,
                1
            ) Not NULL,
        [ObjectTypeName] NVARCHAR ( 200 ) Not NULL,
        [ObjectName] NVARCHAR ( 640 ) Not NULL,
        [ParentObjectTypeName] NVARCHAR ( 200 ) NULL,
        [ParentObjectName] NVARCHAR ( 640 ) NULL,
        [SettingTypeName] NVARCHAR ( 100 ) Not NULL,
        [SettingName] NVARCHAR ( 128 ) Not NULL,
        [ConfiguredValue] NVARCHAR ( 128 ) NULL,
        [OrderBy] INT Not NULL,
        [ParentObjectID] UNIQUEIDENTIFIER NULL,
        [ObjectID] UNIQUEIDENTIFIER NULL
    )
---- EVENT SOURCE CONNECTION SETTINGS -----

-- Synchronization Threshold

INSERT INTO
    #SettingsList
    (
        ObjectTypeName,
        ObjectName,
        ParentObjectTypeName,
        ParentObjectName,
        SettingTypeName,
        SettingName,
        ConfiguredValue,
        OrderBy,
        ParentObjectID,
        ObjectID
    )
SELECT
    COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
    Objects.ObjectName,
    ParentObjectType.Name AS ParentObjectType,
    ParentObjects.ObjectName AS ParentObjectName,
    dbo.SettingType.Name AS SettingTypeName,
    'Synchronization Threshold' AS SettingName,
    CAST ( dbo.fnConvertTicksToTime_ms ( IEventSourceConnectionSettings.SynchronizationThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
    ParentObjects.ObjectID as ParentObjectID,
    Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritSynchronization = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Synchronization Type

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Synchronization Type' AS SettingName,
 CASE
SynchronizationType WHEN 0 THEN 'Show a Message Box prompt.' WHEN 1 THEN 'Don''t synchronize automatically.' WHEN 2 THEN 'Automatically synchronize.'
END AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritSynchronization = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maintenance Window Enabled

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT DISTINCT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maintenance Window Enabled' AS SettingName,
CASE ISNULL(COALESCE(ScheduleGroup.ID, ObjectSchedule.ID), -1) WHEN -1 THEN 'No' ELSE 'Yes' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.ScheduleGroup
        ON dbo.IEventSourceConnectionSettings.MaintenanceWindowScheduleKey = dbo.ScheduleGroup.ID
    LEFT OUTER JOIN
dbo.ObjectSchedule
        ON dbo.IEventSourceConnectionSettings.MaintenanceWindowScheduleKey = dbo.ObjectSchedule.ID
WHERE
InheritMaintenanceWindow = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maintenance Window Name
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT DISTINCT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maintenance Window Name' AS SettingName,
ISNULL(COALESCE(ScheduleGroup.Name, ObjectSchedule.Name), 'None') AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.ScheduleGroup
        ON dbo.IEventSourceConnectionSettings.MaintenanceWindowScheduleKey = dbo.ScheduleGroup.ID
    LEFT OUTER JOIN
dbo.ObjectSchedule
        ON dbo.IEventSourceConnectionSettings.MaintenanceWindowScheduleKey = dbo.ObjectSchedule.ID
WHERE
InheritMaintenanceWindow = CAST ( 0 AS BIT )


-- Max databases to synchronize

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Max Databases to Synchronize (largest first)' AS SettingName,
dbo.ISqlServerConnectionSettings.MaxDatabasesToSynchronize AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
INNER JOIN dbo.ISqlServerConnectionSettings
    ON dbo.ISqlServerConnectionSettings.ID = dbo.IEventSourceConnectionSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID AND dbo.SettingType.ID = '0A780902-0899-4877-8E7B-47738E2EF7E8'
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritMiscellaneous = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Auto-enable SQL Server Agent Tokens

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Auto-enable SQL Server Agent Tokens' AS SettingName,
CASE AutoEnableSqlAgentTokens WHEN 1 THEN 'Yes' ELSE 'No'END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
INNER JOIN dbo.ISqlServerConnectionSettings
    ON dbo.ISqlServerConnectionSettings.ID = dbo.IEventSourceConnectionSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID AND dbo.SettingType.ID = '0A780902-0899-4877-8E7B-47738E2EF7E8'
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritMiscellaneous = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maximum Queue Length

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maximum Queue Length' AS SettingName,
CAST ( ISNULL ( ISqlServerConnectionSettings.MaxQueueLength , 0 ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerConnectionSettings
    ON dbo.ISqlServerConnectionSettings.ID = dbo.IEventSourceConnectionSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID AND dbo.SettingType.ID = '0A780902-0899-4877-8E7B-47738E2EF7E8'
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritMiscellaneous = CAST ( 0 AS BIT )
-- Only get non-inherited settings

    And ISqlServerConnectionSettings.MaxQueueLength IS Not NULL

-- Auto-recycle Large SQL Server Agent Logs

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Auto-recycle Large SQL Server Agent Logs' AS SettingName,
CASE AutoRecycleAgentLog WHEN 1 THEN 'Yes' ELSE 'No'END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerConnectionSettings
    ON dbo.ISqlServerConnectionSettings.ID = dbo.IEventSourceConnectionSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID AND dbo.SettingType.ID = '0A780902-0899-4877-8E7B-47738E2EF7E8'
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritMiscellaneous = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Collect Memory by Category Data

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Collect Memory by Category Data' AS SettingName,
CASE CollectSsasMemoryUsage WHEN 1 THEN 'Yes' ELSE 'No'END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceConnectionSettings
        ON dbo.IEventSourceConnectionSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceConnectionSettings.SettingTypeID = dbo.SettingType.ID AND dbo.SettingType.ID = 'FE75F332-7D70-4868-8248-71446C27990E'
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritMiscellaneous = CAST ( 0 AS BIT )
-- Only get non-inherited settings

---- EVENT SOURCE SETTINGS -----

-- Auto-Watch New Objects

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Auto-Watch New Objects' AS SettingName,
 CASE
AutoWatchNewObjects WHEN 1 THEN 'Yes'
ELSE 'No'
END AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritAutoAssignment = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maximum Rows To Synchronize

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maximum Rows To Synchronize' AS SettingName,
CAST ( IEventSourceSettings.MaximumRowsToSynchronize AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritSynchronization = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Missed Run Wait Time

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Missed Run Wait Time' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( IEventSourceSettings.MissedRunWaitTime ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritSynchronization = CAST ( 0 AS BIT )
-- Only get non-inherited settings

    And IEventSourceSettings.MissedRunWaitTime IS Not NULL
---- EVENT SOURCE OBJECT SETTINGS -----

-- Minimum Runtime Threshold

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Runtime Threshold' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( IEventSourceObjectSettings.MinRuntimeThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritRuntimeThresholds = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maximum Runtime Threshold

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maximum Runtime Threshold' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( IEventSourceObjectSettings.MaxRuntimeThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritRuntimeThresholds = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Runtime Threshold Percent

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Runtime Threshold Percent' AS SettingName,
CAST ( dbo.IEventSourceObjectSettings.MinRuntimeThresholdPercent AS VARCHAR ( 50 )
) + '%',
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritRuntimeThresholds = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maximum Runtime Threshold Percent

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maximum Runtime Threshold Percent' AS SettingName,
CAST ( dbo.IEventSourceObjectSettings.MaxRuntimeThresholdPercent AS VARCHAR ( 50 )
) + '%',
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritRuntimeThresholds = CAST ( 0 AS BIT )
-- Only get non-inherited settings

---- JOB SETTINGS -----

-- Behavior When Queuing

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Behavior When Queuing' AS SettingName,
 CASE
BehaviorWhenQueuing WHEN 1 THEN 'Never Queue.' WHEN 2 THEN 'Queue for specified count.' WHEN 3 THEN 'Queue for specified time.' WHEN 4 THEN 'Queue indefinitely.'
END AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueuing = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Queue others for

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Queue Others For' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( IJobSettings.QueueOthersFor ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueuing = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Behavior When Queued

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Behavior When Queued' AS SettingName,
 CASE
BehaviorWhenQueuing WHEN 1 THEN 'Never Queue.' WHEN 2 THEN 'Queue for specified count.' WHEN 3 THEN 'Queue for specified time.' WHEN 4 THEN 'Queue indefinitely.'
END AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueued = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Queue for

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Queue For' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( IJobSettings.QueueFor ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueued = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Auto-start threshold

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Auto-start Threshold' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( IJobSettings.AutoStartThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueued = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Auto-start threshold enabled

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Auto-start Threshold Enabled' AS SettingName,
 CASE
AutoStartThresholdEnabled WHEN 1 THEN 'Yes'
ELSE 'No'
END AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueued = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Queued Auto-start Type

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Auto-start Type' AS SettingName,
 CASE
QueuedAutoStartType WHEN 1 THEN 'Don''t auto-start, resume schedule.' WHEN 2 THEN 'Auto-start immediately.' WHEN 3 THEN 'Use default setting.'
END AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.IJobSettings
        ON dbo.IJobSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritBehaviorWhenQueued = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Block Duration

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Block Duration' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( ISqlServerBlockLogSourceSettings.MinimumDurationThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerBlockLogSourceSettings
        ON dbo.ISqlServerBlockLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritCollectionSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Collect Deadlock Events

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Collect Deadlock Events' AS SettingName,
CASE IsDeadlockCollectionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerDeadlockSourceSettings
        ON dbo.ISqlServerDeadlockSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritDeadlockSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Top SQL Duration

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Duration' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( ISqlServerTraceLogSourceSettings.MinimumDurationThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerTraceLogSourceSettings
        ON dbo.ISqlServerTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritTopSqlSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Top SQL CPU

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum CPU' AS SettingName,
CAST ( ISqlServerTraceLogSourceSettings.MinimumCpuThreshold AS varchar ( 128 ) ) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerTraceLogSourceSettings
        ON dbo.ISqlServerTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritTopSqlSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Top SQL Writes

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Writes' AS SettingName,
CAST ( ISqlServerTraceLogSourceSettings.MinimumWritesThreshold AS varchar ( 128 ) ) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerTraceLogSourceSettings
        ON dbo.ISqlServerTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritTopSqlSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Top SQL Reads

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Reads' AS SettingName,
CAST ( ISqlServerTraceLogSourceSettings.MinimumReadsThreshold AS varchar ( 128 ) ) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerTraceLogSourceSettings
        ON dbo.ISqlServerTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritTopSqlSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Collect Batch and RPC Events

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Collect Batch and RPC Events' AS SettingName,
CASE IsBatchCollectionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerTraceLogSourceSettings
        ON dbo.ISqlServerTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritTopSqlSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Collect Statement Events

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Collect Statement Events' AS SettingName,
CASE IsStatementCollectionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerTraceLogSourceSettings
        ON dbo.ISqlServerTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritTopSqlSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Minimum Top Commands Duration

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Minimum Duration' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( ISsasTraceLogSourceSettings.MinimumDurationThreshold ) AS varchar ( 128 )
) AS ConfiguredValue,
 CASE
WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISsasTraceLogSourceSettings
        ON dbo.ISsasTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritSsasSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Collect Statement Events

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Collect MDX/DMX/XMLA Events' AS SettingName,
CASE IsCommandCollectionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISsasTraceLogSourceSettings
        ON dbo.ISsasTraceLogSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritSsasSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maintenance Window Enabled

INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maintenance Window Enabled' AS SettingName,
CASE ISNULL(COALESCE(ScheduleGroup.ID, ObjectSchedule.ID), -1) WHEN -1 THEN 'No' ELSE 'Yes' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IDeviceSettings
        ON dbo.IDeviceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IDeviceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.ScheduleGroup
        ON dbo.IDeviceSettings.MaintenanceWindowScheduleKey = dbo.ScheduleGroup.ID
    LEFT OUTER JOIN
dbo.ObjectSchedule
        ON dbo.IDeviceSettings.MaintenanceWindowScheduleKey = dbo.ObjectSchedule.ID
WHERE
InheritMaintenanceWindow = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maintenance Window Name
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT DISTINCT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maintenance Window Name' AS SettingName,
ISNULL(COALESCE(ScheduleGroup.Name, ObjectSchedule.Name), 'None') AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IDeviceSettings
        ON dbo.IDeviceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.IDeviceSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.ScheduleGroup
        ON dbo.IDeviceSettings.MaintenanceWindowScheduleKey = dbo.ScheduleGroup.ID
    LEFT OUTER JOIN
dbo.ObjectSchedule
        ON dbo.IDeviceSettings.MaintenanceWindowScheduleKey = dbo.ObjectSchedule.ID
WHERE
InheritMaintenanceWindow = CAST ( 0 AS BIT )

--INDEX DEFRAGMENTATION

-- Is Defragmentation Enabled
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Enable Index Defragmentation' AS SettingName,
CASE IndexDefragmentationOperationType WHEN 0 THEN 'False' ELSE 'True' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Fragmentation Scan Mode
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Fragmentation Scan Mode' AS SettingName,
CASE FragmentationScanModeType WHEN 0 THEN 'Limited'
    WHEN 1 THEN 'Sample'
    WHEN 2 THEN 'Detailed'
END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Max Concurrent Defrag Operations
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maximum Concurrent Defrag Operations' AS SettingName,
MaxConcurrentDefragOperations AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Run Post-defrag Analysis
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Run Post-defrag Analysis' AS SettingName,
CASE RunPostDefragAnalysis WHEN 1 THEN 'True' ELSE 'False' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Defrag Schedule
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Schedule' AS SettingName,
CASE WHEN ObjectSchedule.Name IS NULL THEN '<not specified>'
    ELSE ObjectSchedule.Name
END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.ObjectSchedule
        on dbo.ISqlServerIndexDefragmentationSettings.Schedule = ObjectSchedule.ID
WHERE
ISqlServerIndexDefragmentationSettings.InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Maximum Defrag Duration
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Maximum Duration' AS SettingName,
CAST ( dbo.fnConvertTicksToTime_ms ( ISqlServerIndexDefragmentationSettings.MaximumDuration) AS varchar ( 128 )
) AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Partitions
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Partitions' AS SettingName,
CASE WHEN PartitionDefragmentationType = 0 THEN 'All'
    WHEN PartitionDefragmentationType = 1 THEN 'Max Only'
    WHEN PartitionDefragmentationType = 2 THEN 'Exclude Max'
END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritGeneralDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Max Fragmentation Percentage For Reorg
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Reorg Threshold %' AS SettingName,
MaxFragmentationPercentageForReorg AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritIndexReorgDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Rebuild Threshold %
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Rebuild Threshold %' AS SettingName,
MaxFragmentationPercentageForRebuild AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritIndexRebuildDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Sort in tempdb
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Sort in tempdb' AS SettingName,
CASE WHEN SortInTempDB = 0 THEN 'False'
ELSE 'True'
END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritIndexRebuildDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Use Online Rebuild
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Use Online Rebuild' AS SettingName,
CASE WHEN UseOnlineRebuild = 0 THEN 'False'
ELSE 'True'
END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritIndexRebuildDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- MAXDOP
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'MAXDOP' AS SettingName,
MaxDoP AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceObjectSettings
        ON dbo.IEventSourceObjectSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerIndexDefragmentationSettings
        ON dbo.ISqlServerIndexDefragmentationSettings.ID = dbo.IEventSourceObjectSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceObjectSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerIndexDefragmentationSettings.InheritIndexRebuildDatabaseSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

--DATABASES SOURCE

-- Max Partitions To Collect
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Max Partitions to Collect per Database' AS SettingName,
MaxPartitionsToCollect AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerDatabaseSourceSettings
        ON dbo.ISqlServerDatabaseSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerDatabaseSourceSettings.InheritGeneralDatabaseSourceSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Min Index Size (MB) to Collect Fragmentation Data
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Min Index Size (MB) to Collect Fragmentation Data' AS SettingName,
MinIndexSizeInMBToCollect AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerDatabaseSourceSettings
        ON dbo.ISqlServerDatabaseSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerDatabaseSourceSettings.InheritGeneralDatabaseSourceSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Max Index Size (MB) to Collect Fragmentation Data
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Max Index Size (MB) to Collect Fragmentation Data' AS SettingName,
MaxIndexSizeInMBToCollect AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerDatabaseSourceSettings
        ON dbo.ISqlServerDatabaseSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerDatabaseSourceSettings.InheritGeneralDatabaseSourceSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- Collect Buffer Data when Buffer > 8GB
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'Collect Buffer Data when Buffer > 8GB' AS SettingName,
CASE WHEN CollectBufferData = 0 THEN 'False'
ELSE 'True'
END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0
    WHEN ParentObjects.ObjectName = 'Global Object' THEN 1
    ELSE 2
END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID
FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.IEventSourceSettings
        ON dbo.IEventSourceSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.ISqlServerDatabaseSourceSettings
        ON dbo.ISqlServerDatabaseSourceSettings.ID = dbo.IEventSourceSettings.ID
    INNER JOIN
dbo.SettingType
        ON dbo.IEventSourceSettings.SettingTypeID = SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
WHERE
ISqlServerDatabaseSourceSettings.InheritGeneralDatabaseSourceSettings = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- SMTP Server Enabled
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT DISTINCT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'SMTP Server Enabled' AS SettingName,
CASE ISNULL(COALESCE(SmtpServerGroup.ID, SmtpServer.ID), -1) WHEN -1 THEN 'No' ELSE 'Yes' END AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.ISiteSettings
        ON dbo.ISiteSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.ISiteSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.SmtpServer
        ON dbo.ISiteSettings.SmtpServerKey = dbo.SmtpServer.ID
    LEFT OUTER JOIN
dbo.SmtpServerGroup
        ON dbo.ISiteSettings.SmtpServerKey = dbo.SmtpServerGroup.ID
WHERE
ISiteSettings.InheritSmtpSetting = CAST ( 0 AS BIT )
-- Only get non-inherited settings

-- SMTP Server Name
INSERT INTO
#SettingsList
(
    ObjectTypeName,
    ObjectName,
    ParentObjectTypeName,
    ParentObjectName,
    SettingTypeName,
    SettingName,
    ConfiguredValue,
    OrderBy,
    ParentObjectID,
    ObjectID
)
SELECT DISTINCT
COALESCE(SettingTypeObjectType.Name, dbo.ObjectType.Name) AS ObjectTypeName, --X
Objects.ObjectName,
ParentObjectType.Name AS ParentObjectType,
ParentObjects.ObjectName AS ParentObjectName,
dbo.SettingType.Name AS SettingTypeName,
'SMTP Server Name' AS SettingName,
ISNULL(COALESCE(SmtpServerGroup.Name, SmtpServer.Host), 'None') AS ConfiguredValue,
CASE WHEN ParentObjects.ObjectName IS NULL THEN 0 WHEN ParentObjects.ObjectName = 'Global Object' THEN 1 ELSE 2 END AS OrderBy,
ParentObjects.ObjectID as ParentObjectID,
Objects.ObjectID

FROM
vwObjects as Objects
    LEFT OUTER JOIN
vwObjects AS ParentObjects
        ON Objects.ParentObjectID = ParentObjects.ObjectID
    INNER JOIN
dbo.ObjectType
        ON Objects.ObjectTypeID = dbo.ObjectType.ID
    LEFT OUTER JOIN
dbo.ObjectType AS ParentObjectType
        ON ParentObjects.ObjectTypeID = ParentObjectType.ID
    INNER JOIN
dbo.ISiteSettings
        ON dbo.ISiteSettings.ObjectID = Objects.ObjectID
    INNER JOIN
dbo.SettingType
        ON dbo.ISiteSettings.SettingTypeID = dbo.SettingType.ID
LEFT JOIN dbo.ObjectType SettingTypeObjectType --X
        ON dbo.SettingType.AppliesToObjectTypeID = SettingTypeObjectType.ID --X
    LEFT OUTER JOIN
dbo.SmtpServer
        ON dbo.ISiteSettings.SmtpServerKey = dbo.SmtpServer.ID
    LEFT OUTER JOIN
dbo.SmtpServerGroup
        ON dbo.ISiteSettings.SmtpServerKey = dbo.SmtpServerGroup.ID
WHERE
ISiteSettings.InheritSmtpSetting = CAST ( 0 AS BIT )
-- Only get non-inherited settings

IF @IsWatched = 1
BEGIN
    DELETE FROM #SettingsList where ParentObjectID in (SELECT ObjectID FROM EventSourceConnection WHERE IsWatched = 0)
    DELETE FROM #SettingsList where ObjectID in (SELECT ObjectID FROM EventSourceConnection WHERE IsWatched = 0)
END

DECLARE @RestrictedConnectionsCount INT
SET @RestrictedConnectionsCount = (SELECT COUNT(*) FROM Security.GetRestrictedConnectionIDsForActiveUser())

IF @RestrictedConnectionsCount > 0
    BEGIN
        SELECT DISTINCT
        ISNULL
        (
            ParentObjectName,
            'Root'
        ) AS ParentObjectName,
            ObjectName,
        ObjectTypeName AS AppliesToObjectType, --X
            SettingTypeName,
            SettingName,
            ConfiguredValue,
            OrderBy
        FROM #SettingsList Settings
            INNER JOIN [Security].[GetVisibleConnectionIDsForActiveUser] () VisibleConnections
            ON Settings.ObjectID = VisibleConnections.ObjectID
            OR Settings.ParentObjectID = VisibleConnections.ObjectID
            OR Settings.ParentObjectID IS NULL
            OR Settings.ParentObjectID
                IN (SELECT EventSource.ObjectID FROM EventSource
                    INNER JOIN [Security].[GetVisibleConnectionIDsForActiveUser] () VisibleConnections
                    ON EventSource.EventSourceConnectionID = VisibleConnections.ObjectID)
            OR Settings.ObjectID
                IN (SELECT EventSourceObject.ObjectID FROM EventSourceObject
                    INNER JOIN EventSource ON EventSourceObject.EventSourceID = EventSource.ObjectID
                    INNER JOIN [Security].[GetVisibleConnectionIDsForActiveUser] () VisibleConnections
                    ON EventSource.EventSourceConnectionID = VisibleConnections.ObjectID)
        ORDER BY OrderBy,
            ParentObjectName,
            ObjectName,
        ObjectTypeName, --X
            SettingTypeName,
            SettingName
    END
ELSE
    SELECT DISTINCT
    ISNULL
    (
        ParentObjectName,
        'Root'
    ) AS ParentObjectName,
        ObjectName,
        ObjectTypeName AS AppliesToObjectType, --X
        SettingTypeName,
        SettingName,
        ConfiguredValue,
        OrderBy
    FROM #SettingsList Settings
    ORDER BY OrderBy,
        ParentObjectName,
        ObjectName,
        ObjectTypeName, --X
        SettingTypeName,
        SettingName


DROP TABLE #SettingsList
GO