• Home
• com.googlecode.etl-unit
• etlunit-sql-server-database
• 1.6.6
• source code

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

e.etl-unit.etlunit-sql-server-database.1.6.6.source-code.materialize_views.vm Maven / Gradle / Ivy

DECLARE @SQL_VIEW_RENAME VARCHAR(MAX)
DECLARE @SQL_TABLE_CREATE VARCHAR(MAX)

PRINT('Materializing views into tables')
-- drop constraints
DECLARE con_cursor CURSOR
    FOR
	SELECT
		'SP_RENAME ''' + S.NAME + '.' + V.NAME + ''', ''' + V.NAME + '_SHADOW''',
		'SELECT * INTO ' + S.NAME + '.' + V.NAME + ' FROM ' + S.NAME + '.' + V.NAME + '_SHADOW'
	FROM
		SYS.VIEWS V JOIN SYS.SCHEMAS S ON V.SCHEMA_ID = S.SCHEMA_ID WHERE S.NAME NOT IN ('SYS', 'INFORMATION_SCHEMA') AND V.NAME NOT LIKE '%_SHADOW'

OPEN con_cursor

FETCH con_cursor
INTO
	@SQL_VIEW_RENAME, @SQL_TABLE_CREATE

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC(@SQL_VIEW_RENAME)
	EXEC(@SQL_TABLE_CREATE)

	FETCH con_cursor
	INTO
		@SQL_VIEW_RENAME, @SQL_TABLE_CREATE
END

CLOSE con_cursor
DEALLOCATE con_cursor