Get Table Specific Metadata for creating Merge statement

CREATE PROCEDURE [dbo].[usp_utl_GetTableSpecificMetadata]
(
    @SourceTableName VARCHAR(200)
  , @SourceSchemaName VARCHAR(200)
  , @DestinationSchemaName VARCHAR(200)
)
AS

/*=======================================================================================================================================
-- Author       :Mihir Shah
-- Created      :04/07/2018
-- Purpose/Description   
--  This query will help to create Metadata to be used for Merge Creation

=========================================================================================================================================
Usage:
Execute [dbo].[usp_utl_GetTableSpecificMetadata]
@SourceTableName = 'C_Roster' , @SourceSchemaName = 'stg' , @DestinationSchemaName = 'ex'
==========================================================================================================================================*/

/* Inserting records for Surrogate Keys */


SELECT  @DestinationSchemaName AS DstSchema
      , SUBSTRING(TABLE_NAME, 3, 255) AS DstTableName
      , COLUMN_NAME AS DstColumn
      , ORDINAL_POSITION AS SortID
      , @SourceSchemaName AS SrcSchema
      , TABLE_NAME AS SrcTable
      , COLUMN_NAME AS SrcColumn
      , 'NK' AS ColumnType
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @SourceTableName
    AND TABLE_SCHEMA = @SourceSchemaName
    AND COLUMN_NAME LIKE '%ID'

--UNION ALL
--/* Inserting records for Natural Keys */
--select 'dbo' AS DstSchema, Table_name AS DstTable, Column_name as DstColumn,
--ORDINAL_POSITION AS SortID, 'tmp' AS SrcSchema, Table_name AS SrcTable, Column_name as SrcColumn,
--'NK' AS ColumnType
--from INFORMATION_SCHEMA.columns
--where table_name = 'RatesActivityLogs'
--AND Table_Schema = 'dbo'
--AND column_name  like '%BKey'
--AND column_name = TABLE_NAME + 'BKey'

UNION ALL
/* Inserting records for Type 1 dimensions */
SELECT  @DestinationSchemaName AS DstSchema
      , SUBSTRING(TABLE_NAME, 3, 255) AS DstTableName
      , COLUMN_NAME AS DstColumn
      , ORDINAL_POSITION AS SortID
      , @SourceSchemaName AS SrcSchema
      , TABLE_NAME AS SrcTable
      , COLUMN_NAME AS SrcColumn
      , 'Type1' AS ColumnType
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @SourceTableName
    AND TABLE_SCHEMA = @SourceSchemaName
    --AND column_name <> SUBSTRING(Table_name,3,255) + 'BKey'
    AND COLUMN_NAME NOT LIKE '%ID';
GO