Получение элементов измерений из системных таблиц

Получение списка измерений и списка элементов из измерения из базы данных

--Измерения
SELECT OBJECT_ID DIM_ID, OBJECT_NAME DIMENSION_NAME
	FROM [hpl_app_1].[dbo].[HSP_OBJECT]
	WHERE OBJECT_ID IN (
		SELECT [DIM_ID]
		FROM [hpl_app_1].[dbo].[HSP_DIMENSION]
	)
	AND OBJECT_NAME not in ('HSP_Rates','HSP_XCRNCY','PUH');

--Элементы измерения Account
DECLARE @vDimName nvarchar(50) = 'Account_Dim';

SELECT tbl1.MEMBER_ID, tbl1.DIM_ID, tbl4.OBJECT_NAME ElementCode, tbl3.OBJECT_NAME ElementName
FROM [hpl_app_1].[dbo].[HSP_MEMBER] tbl1
LEFT JOIN (select * from [hpl_app_1].[dbo].[HSP_ALIAS] where ALIASTBL_ID=14) tbl2 ON tbl1.MEMBER_ID = tbl2.MEMBER_ID
LEFT JOIN [hpl_app_1].[dbo].[HSP_OBJECT] tbl3 ON tbl2.[ALIAS_ID] = tbl3.OBJECT_ID
LEFT JOIN [hpl_app_1].[dbo].[HSP_OBJECT] tbl4 ON tbl1.MEMBER_ID = tbl4.OBJECT_ID
WHERE tbl1.DIM_ID IN (
	SELECT OBJECT_ID
	FROM [hpl_app_1].[dbo].[HSP_OBJECT]
	WHERE OBJECT_ID IN (
		SELECT [DIM_ID]
		FROM [hpl_app_1].[dbo].[HSP_DIMENSION]
	)
	AND OBJECT_NAME = @vDimName
) and tbl4.OBJECT_TYPE<>45
ORDER BY tbl1.MEMBER_ID;

--Измерения

SELECT OBJECT_ID DIM_ID, OBJECT_NAME DIMENSION_NAME

FROM [hpl_app_1].[dbo].[HSP_OBJECT]

WHERE OBJECT_ID IN (

SELECT [DIM_ID]

FROM [hpl_app_1].[dbo].[HSP_DIMENSION]

)

AND OBJECT_NAME not in ('HSP_Rates','HSP_XCRNCY','PUH');

--Элементы измерения Account

DECLARE @vDimName nvarchar(50) = 'Account_Dim';

SELECT tbl1.MEMBER_ID, tbl1.DIM_ID, tbl4.OBJECT_NAME ElementCode, tbl3.OBJECT_NAME ElementName

FROM [hpl_app_1].[dbo].[HSP_MEMBER] tbl1

LEFT JOIN (select * from [hpl_app_1].[dbo].[HSP_ALIAS] where ALIASTBL_ID=14) tbl2 ON tbl1.MEMBER_ID = tbl2.MEMBER_ID

LEFT JOIN [hpl_app_1].[dbo].[HSP_OBJECT] tbl3 ON tbl2.[ALIAS_ID] = tbl3.OBJECT_ID

LEFT JOIN [hpl_app_1].[dbo].[HSP_OBJECT] tbl4 ON tbl1.MEMBER_ID = tbl4.OBJECT_ID

WHERE tbl1.DIM_ID IN (

SELECT OBJECT_ID

FROM [hpl_app_1].[dbo].[HSP_OBJECT]

WHERE OBJECT_ID IN (

SELECT [DIM_ID]

FROM [hpl_app_1].[dbo].[HSP_DIMENSION]

)

AND OBJECT_NAME = @vDimName

) and tbl4.OBJECT_TYPE<>45

ORDER BY tbl1.MEMBER_ID;