-- Database Parameter and Query Declaration
USE AdventureWorks2022;
DECLARE @query nvarchar(max) = 'SELECT * FROM Production.vProductAndDescription';
DECLARE @DataVersePrefix varchar(10) = 'crpdp';

-- Create Temp Table for SP_Describe_First_Result_Set
IF OBJECT_ID('tempdb..#ResultSet', 'U') IS NOT NULL DROP TABLE #ResultSet;
CREATE TABLE #ResultSet (
    [is_hidden] bit NOT NULL,
    [column_ordinal] int NOT NULL,
    [name] sysname NULL,
    [is_nullable] bit NOT NULL,
    [system_type_id] int NOT NULL,
    [system_type_name] nvarchar(256) NULL,
    [max_length] smallint NOT NULL,
    [precision] tinyint NOT NULL,
    [scale] tinyint NOT NULL,
    [collation_name] sysname NULL,
    [user_type_id] int NULL,
    [user_type_database] sysname NULL,
    [user_type_schema] sysname NULL,
    [user_type_name] sysname NULL,
    [assembly_qualified_type_name] nvarchar(4000) NULL,
    [xml_collection_id] int NULL,
    [xml_collection_database] sysname NULL,
    [xml_collection_schema] sysname NULL,
    [xml_collection_name] sysname NULL,
    [is_xml_document] bit NOT NULL,
    [is_case_sensitive] bit NOT NULL,
    [is_fixed_length_clr_type] bit NOT NULL,
    [source_server] sysname NULL,
    [source_database] sysname NULL,
    [source_schema] sysname NULL,
    [source_table] sysname NULL,
    [source_column] sysname NULL,
    [is_identity_column] bit NULL,
    [is_part_of_unique_key] bit NULL,
    [is_updateable] bit NULL,
    [is_computed_column] bit NULL,
    [is_sparse_column_set] bit NULL,
    [ordinal_in_order_by_list] smallint NULL,
    [order_by_list_length] smallint NULL,
    [order_by_is_descending] smallint NULL,
    [tds_type_id] int NOT NULL,
    [tds_length] int NOT NULL,
    [tds_collation_id] int NULL,
    [tds_collation_sort_id] tinyint NULL
);
 
-- Run the query and insert the result into the temp table
INSERT INTO #ResultSet
EXEC sp_describe_first_result_set @query, null, 0;
 
-- Return the results in a format that can be used for Extract Transform Load development
SELECT  CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('[',R.[name],'],') ELSE CONCAT('[',R.[name],']') END as Column_MSSQL,
        CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('"',R.[name],'",') ELSE CONCAT('"',R.[name],'"') END as Column_ORA,
        CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('`',R.[name],'`,') ELSE CONCAT('`',R.[name],'`') END as Column_MYSQL_Databricks,
        CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('[',@DataVersePrefix,'_',REPLACE(LOWER(R.[name]),' ',''),'],') ELSE CONCAT('[',@DataVersePrefix,'_',REPLACE(LOWER(R.[name]),' ',''),']') END as Column_DataVerse,
 
		-- Microsoft SQL Compatible Create Table
        CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('[',R.[name],'] ',R.[system_type_name],CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END,',') ELSE CONCAT('[',R.[name],'] ',R.[system_type_name],CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END) END as CreateTable_MSSQL,
		
		-- Oracle SQL Compatible Create Table
		CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('"',R.[name],'" ',R.[system_type_name],CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END,',') ELSE CONCAT('"',R.[name],'" ',R.[system_type_name],CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END) END as CreateTable_ORA,
		
		-- MySQL Compatible Create Table
		CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('`',R.[name],'` ',R.[system_type_name],CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END,',') ELSE CONCAT('`',R.[name],'` ',R.[system_type_name],CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END) END as CreateTable_MYSQL,
		
		-- Databricks Compatible Create Table
		CASE WHEN R.[column_ordinal]<>MC.MaxCol THEN CONCAT('`',R.[name],'` ',
			CASE	WHEN LEFT(R.[system_type_name],8) = 'nvarchar' THEN REPLACE(R.[system_type_name],'nvarchar','varchar')
					WHEN LEFT(R.[system_type_name],5) = 'nchar' THEN REPLACE(R.[system_type_name],'nchar','varchar')
					WHEN LEFT(R.[system_type_name],8) = 'datetime' THEN 'timestamp'
					WHEN LEFT(R.[system_type_name],4) = 'char' AND LEFT(R.[system_type_name],7) NOT IN ('nvarcha','varchar') THEN REPLACE(R.[system_type_name],'char','varchar')
					ELSE R.[system_type_name]
			END,
			CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END,',') ELSE CONCAT('`',R.[name],'` ',
				CASE	WHEN LEFT(R.[system_type_name],8) = 'nvarchar' THEN REPLACE(R.[system_type_name],'nvarchar','varchar')
						WHEN LEFT(R.[system_type_name],5) = 'nchar' THEN REPLACE(R.[system_type_name],'nchar','varchar')
						WHEN LEFT(R.[system_type_name],8) = 'datetime' THEN 'timestamp'
						WHEN LEFT(R.[system_type_name],4) = 'char' AND LEFT(R.[system_type_name],7) NOT IN ('nvarcha','varchar') THEN REPLACE(R.[system_type_name],'char','varchar')
						ELSE R.[system_type_name]
				END
			,CASE WHEN R.is_nullable=0 THEN '' ELSE ' NOT NULL' END) END as CreateTable_Databricks

FROM #ResultSet R
CROSS JOIN (SELECT MAX([column_ordinal]) MaxCol FROM #ResultSet) MC
ORDER BY [column_ordinal] ASC;

SELECT	STRING_AGG(CONCAT('[',[name],']'),',') as [MSSQL_SingleLine_Select],
		STRING_AGG(CONCAT('"',[name],'"'),',') as [ORA_SingleLine_Select],
		STRING_AGG(CONCAT('`',[name],'`'),',') as [MYSQL_Databricks_SingleLine_Select]
FROM #ResultSet;