Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /****** 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
Add Comment
Please, Sign In to add comment