USE TM_RODELER
GO

IF OBJECT_ID('GET_VIEWS_CREATE_PROGRAMS_FROM_LINKED_SERVER_sp') IS NOT NULL
	DROP PROCEDURE GET_VIEWS_CREATE_PROGRAMS_FROM_LINKED_SERVER_sp
GO

/*

EXEC GET_VIEWS_CREATE_PROGRAMS_FROM_LINKED_SERVER_sp

*/
CREATE PROCEDURE GET_VIEWS_CREATE_PROGRAMS_FROM_LINKED_SERVER_sp
AS
BEGIN
	DELETE
	FROM program
	WHERE code = 'L9W'

	INSERT INTO program (
		code
		,NAME
		,type
		)
	SELECT 'L9W'
		,'Linked server'
		,1

	DECLARE @program_code_s VARCHAR(100) = 'L9W'
	DECLARE @program_code VARCHAR(100) = ''
	DECLARE @table_type VARCHAR(100) = 'SIMPLE TABLE'
	DECLARE @layout_type VARCHAR(100) = 'GRID'
	DECLARE @linked_server VARCHAR(1000) = '[BI-DWH.PROD.LOCAL\BIPROD].[BetaMedia_DWH].[aml].'
	DECLARE @query_s VARCHAR(8000) = 'SELECT top 10 * FROM ' + @linked_server
	DECLARE @query VARCHAR(8000)
	DECLARE @view_t AS TABLE (NAME VARCHAR(1000))

	INSERT INTO @view_t (NAME)
	SELECT 'CBOAccount'

	UNION

	SELECT 'CBOCustomerFile'

	UNION

	SELECT 'CBOFnsAnswer'

	UNION

	SELECT 'CBOFnsQuestion'

	UNION

	SELECT 'DimAccount'

	UNION

	SELECT 'DimAsset'

	UNION

	SELECT 'DimBrand'

	UNION

	SELECT 'DimCurrency'

	UNION

	SELECT 'DimPositionProperties'

	UNION

	SELECT 'DimTransactionProperties'

	UNION

	SELECT 'DimUserAgent'

	UNION

	SELECT 'FactPositions'

	UNION

	SELECT 'FactTransactions'

	UNION

	SELECT 'DimCustomer'

	/**/
	DECLARE @curr_letter CHAR(1) = 'A'
	DECLARE @view_name VARCHAR(1000)
	DECLARE @c AS CURSOR

	SET @c = CURSOR
	FOR

	SELECT NAME
	FROM @view_t

	OPEN @c

	FETCH @c
	INTO @view_name

	WHILE @@fetch_status <> - 1
	BEGIN
		SET @program_code = @program_code_s + @curr_letter
		SET @query = @query_s + @view_name

		select @program_code
			,@view_name
			,2
			,@table_type
			,@view_name
			,@query
			,@layout_type
			,@view_name

		EXEC insert_sp 'program'
			,@program_code
			,@view_name
			,2
			,@table_type
			,@view_name
			,@query
			,@layout_type
			,@view_name

		SET @curr_letter = CHAR(ASCII(@curr_letter) + 1)

		/**/
		FETCH @c
		INTO @view_name
	END

	CLOSE @c

	DEALLOCATE @c
END
GO