Auto Create External Table

gistfile1.sql

/****** Object:  StoredProcedure [dbo].[sp_Get_Product_Temps]    Script Date: 6/12/2018 1:13:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Samara Duncan
-- Create date: June 12, 2018
-- Description:	Sets up an external data source based on DB name
-- =============================================
CREATE PROCEDURE [dbo].[sp_Setup_External_DataSource] 
	@ExternalDB nvarchar(50), 
	@ExternalServer varchar(100),
	@UserName varchar(50),
	@Password varchar(50)
AS
BEGIN
	SET NOCOUNT ON;

    --create external credential
	IF EXISTS (Select 1 FROM sys.database_scoped_credentials where name = @ExternalDB + '_Credential')
	BEGIN
		Print 'Credentials Exist'
	END
	ELSE BEGIN
		Print 'Creating Credentials'
		IF @UserName IS NULL OR @UserName = ''
			BEGIN
				Print 'Cannot Create Credential Without Password'
				RETURN
			END
		ELSE IF @Password IS NULL OR @Password = ''
			BEGIN
				Print 'Cannot Create Credential Without Password'
				RETURN
			END
		ELSE
			BEGIN
				EXEC('
					CREATE DATABASE SCOPED CREDENTIAL ['+ @ExternalDB + '_Credential] 
					WITH IDENTITY = ''' + @UserName + ''',
					SECRET = ''' + @Password + ''';
				'); 
			END
	END

	--create external data source
	IF EXISTS (Select 1 FROM sys.external_data_sources where name = @ExternalDB)
	BEGIN
		Print 'Data Source Exists'
	END
	ELSE BEGIN
		Print 'Creating Data Source'
		EXEC('
			CREATE EXTERNAL DATA SOURCE [' + @ExternalDB +']
			WITH (   
				TYPE = RDBMS,  
				LOCATION = ''' + @ExternalServer + ''',  
				DATABASE_NAME = ''' + @ExternalDB + ''', 
				CREDENTIAL = ['+@ExternalDB + '_Credential]  
			)
		'); 
	END
END
GO

CREATE PROCEDURE [dbo].[sp_Setup_External_Schema]
	@LocalSchema varchar(20),
	@ExternalSchema varchar(20),
	@ExternalDB nvarchar(50),
	@ExternalSystemTable varchar(50) = NULL --Only set if you don't want to use the default name
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sql nvarchar(MAX)

	IF @ExternalSystemTable IS NULL OR @ExternalSystemTable = ''
	SET @ExternalSystemTable = @ExternalSchema + '_SystemView'

	--creating schema if necessary
	IF  EXISTS (SELECT name FROM sys.schemas WHERE name = @LocalSchema)
		BEGIN
			Print 'Schema exists';
		END
	ELSE
		BEGIN
			EXEC('CREATE SCHEMA ' + @LocalSchema)
			Print 'Creating migration schema'
		END
END

GO

CREATE PROCEDURE [dbo].[sp_Setup_External_System_Table]
	@LocalSchema varchar(20),
	@ExternalSchema varchar(20),
	@ExternalDB nvarchar(50),
	@ExternalSystemTable varchar(50) = NULL --Only set if you don't want to use the default name
AS
BEGIN

	DECLARE @sql nvarchar(MAX)
		--Create External Table to generate schema
	IF EXISTS (Select Top 1 1 FROM sys.external_tables WHERE name = @ExternalSystemTable)
		BEGIN
			PRINT 'System Table Exists'
		END
	ELSE
		BEGIN
			PRINT 'Creating system external view'

			SET @sql = N'
				DROP VIEW IF EXISTS ['+@ExternalSchema+'].[SystemView]'

			EXEC sp_execute_remote @data_source_name = @ExternalDB, @stmt = @sql

			SET @sql = N'
				CREATE VIEW ['+@ExternalSchema+'].[SystemView]
						AS SELECT     c.NAME  AS ColumnName, 
				   tp.NAME AS ColumnType, 
				   CASE WHEN tp.NAME IN (''varchar'', ''char'', ''varbinary'', ''binary'') THEN ''('' + 
							CASE WHEN c.max_length = -1 
								THEN ''MAX''
								ELSE cast(c.max_length AS varchar(5)) 
							END  + '')'' 
					WHEN tp.NAME IN (''nvarchar'', ''nchar'') 
						THEN ''('' + 
							CASE WHEN c.max_length = -1 
								THEN ''MAX''
								ELSE cast(c.max_length / 2 AS varchar(5)) 
							END + '')'' 
					WHEN tp.NAME IN (''datetime2'', ''time2'', ''datetimeoffset'') 
						THEN ''('' + cast(c.scale AS varchar(5)) + '')''
					WHEN tp.NAME = ''decimal'' 
						THEN ''('' + cast(c.[precision] AS varchar(5)) + '','' + cast(c.scale AS varchar(5)) + '')'' 
						ELSE '''' 
					END    AS ColumnTypeSuffix, 
					c.is_nullable AS IsNullable,
					t.NAME AS TableName, 
					s.NAME AS SchemaName 
					FROM       sys.columns c 
					INNER JOIN sys.tables t 
					ON         c.object_id = t.object_id 
					INNER JOIN sys.schemas s 
					ON         t.schema_id = s.schema_id 
					INNER JOIN sys.types tp 
					ON         c.user_type_id = tp.user_type_id 
					WHERE      s.NAME = ''' + @ExternalSchema + ''''

			--PRINT @sql

			EXEC sp_execute_remote @data_source_name = @ExternalDB, @stmt = @sql

			PRINT 'Creating system external view'

			SET @sql = 'CREATE EXTERNAL TABLE ['+@LocalSchema+'].['+ @ExternalSystemTable +']
				(
					ColumnName nvarchar(128),
					ColumnType nvarchar(128),
					ColumnTypeSuffix varchar(13),
					IsNullable bit,
					TableName nvarchar(128),
					SchemaName nvarchar(128)
				)
				WITH (DATA_SOURCE = [' + @ExternalDB +'], SCHEMA_NAME = N''' + @ExternalSchema + ''',OBJECT_NAME = N''SystemView'')'
		

			--PRINT @sql
			EXEC(@sql)
		END					

END

GO

CREATE PROCEDURE [dbo].[sp_Setup_External_Table]
	@ExternalSchema varchar(20),
	@ExternalTable varchar(20),
	@ExternalDB nvarchar(50),
	@LocalSchema varchar(20) = NULL, --Defaults to external table schema
	@LocalTable varchar(20) = NULL, -- Defaults to external table name
	@ExternalSystemTable varchar(50) = NULL --Only set if you don't want to use the default name
AS
BEGIN
	DECLARE @sql nvarchar(MAX)

	--Fill in null variables
	IF @LocalTable IS NULL OR @LocalTable = ''
		SET @LocalTable = @ExternalTable

	IF @LocalSchema IS NULL OR @LocalSchema = ''
		SET @LocalSchema = @ExternalSchema

	IF @ExternalSystemTable IS NULL OR @ExternalSystemTable = ''
	BEGIN
		SET @ExternalSystemTable = @ExternalSchema + '_SystemView'
	END


	EXEC('
		IF EXISTS (SELECT TOP 1 1 FROM sys.external_tables t
			INNER JOIN sys.schemas s
			ON t.schema_id = s.schema_id
			WHERE t.name = '''  + @LocalTable + '''
				AND s.name = ''' + @LocalSchema + ''')
		BEGIN
			DROP EXTERNAL TABLE [' + @LocalSchema +'].[' + @LocalTable +']
		END')


	PRINT 'Creating Table'

	SET @sql = 
		'SELECT @tempOUT = (SELECT 
			STUFF((    SELECT '', ['' + [ColumnName] + ''] '' + ColumnType + ColumnTypeSuffix + '' ''
				FROM [' + @LocalSchema +'].['+ @ExternalSystemTable +']
				WHERE [TableName] = ''' + @ExternalTable + '''
							FOR XML PATH('''') -- Select it as XML
							), 1, 1, '''' ))'

	
	DECLARE @temp nvarchar(max)
	exec sp_executesql @sql, N'@tempOUT nvarchar(MAX) OUTPUT', @tempOUT = @temp OUTPUT


	SET @sql = '
		CREATE EXTERNAL TABLE ['+@LocalSchema+'].['+ @LocalTable +'] ( 
		' + @temp + '
		)WITH (DATA_SOURCE = [' + @ExternalDB +'], SCHEMA_NAME = N''' + @ExternalSchema + ''',OBJECT_NAME = N''' + @ExternalTable + ''')'

	PRINT @sql
	EXEC(@sql)

	PRINT 'Creation Complete'
END


GO

CREATE PROCEDURE [dbo].[sp_Setup_External_Table_Full_Schema]
	@ExternalSchema varchar(20) = 'dbo', --Required, schema of the external table you want to create
	@ExternalDB nvarchar(50) = N'', --optional, Required for external data source setup
	@ExternalServer varchar(100) = '', --optional, required for external data source setup
	@UserName varchar(50) = '', --optional, required for credential setup
	@Password varchar(50) = '', --optional, required for credential setup
	@LocalSchema varchar(20) = 'migration', --optional, if null external schema will be used
	@ExternalSystemTable varchar(50) = '' --optional, if null, external schema will be used to name table

AS
BEGIN
	DECLARE @sql nvarchar(MAX)

	IF @ExternalSystemTable IS NULL OR @ExternalSystemTable = ''
	BEGIN
		SET @ExternalSystemTable = @ExternalSchema + '_SystemView'
	END

	IF EXISTS (Select 1 FROM sys.external_data_sources where name = @ExternalDB)
	BEGIN
		Print 'Data Source Exists'
	END
	ELSE BEGIN
		EXEC [dbo].[sp_Setup_External_DataSource]  @ExternalDB, @ExternalServer, @UserName, @Password
	END

	IF  EXISTS (SELECT name FROM sys.schemas WHERE name = @LocalSchema)
		BEGIN
			Print 'Schema exists';
		END
	ELSE
		BEGIN
			EXEC [dbo].[sp_Setup_External_Schema] @LocalSchema, @ExternalSchema, @ExternalDB, @ExternalSystemTable
		END

	

	IF EXISTS (Select Top 1 1 FROM sys.external_tables WHERE name = @ExternalSystemTable)
	BEGIN
		PRINT 'System Table Exists'
	END
	ELSE
	BEGIN
		EXEC [dbo].[sp_Setup_External_System_Table]  @LocalSchema, @ExternalSchema, @ExternalDB, @ExternalSystemTable
	END

	DECLARE @TableNames TABLE (TableName nvarchar(50))

	SET @sql = '
		SELECT DISTINCT [TableName] 
		FROM [' + @LocalSchema + '].[' + @ExternalSystemTable + ']'

	INSERT  @TableNames EXEC(@sql)

	DECLARE @TableName SYSNAME

	DECLARE TableCursor CURSOR FOR
	SELECT TableName FROM @TableNames

	OPEN TableCursor

	FETCH NEXT FROM TableCursor
	INTO @TableName

	WHILE @@FETCH_STATUS = 0
    BEGIN
		EXEC [dbo].[sp_Setup_External_Table] @ExternalSchema, @TableName, @ExternalDB, @LocalSchema, @TableName, @ExternalSystemTable

		FETCH NEXT FROM TableCursor
			INTO @TableName
    END

	CLOSE TableCursor
	DEALLOCATE TableCursor

END