Utility procedure that generates view in Synapse serverless SQL pool on top of Cosmos DB container with analytical storage

synapse-serverless-sql_generate_cosmosdb_view.sql

SET QUOTED_IDENTIFIER OFF
GO
CREATE OR ALTER PROCEDURE generate_cosmosdb_view (
				@connection nvarchar(max),
				@container nvarchar(1000),
				@db_credential sysname = null,
				@credential sysname = null
				)
AS BEGIN

IF (@db_credential IS NOT NULL AND @credential IS NOT NULL)
	THROW 50001, 'Cannot specify both server-level and database-level credentials', 1;

DECLARE @option VARCHAR(200) = '';

IF (@credential IS NOT NULL)
BEGIN
	IF (0 = (SELECT COUNT(*) FROM sys.credentials WHERE name = @credential))
		THROW 50001, 'The server-level credential does not exists. Use other name or some database scoped credential.', 2;
	ELSE
		SET @option = ", SERVER_CREDENTIAL = '" + @credential + "'";
END

IF (@db_credential IS NOT NULL)
BEGIN
	IF (0 = (SELECT COUNT(*) FROM sys.database_scoped_credentials WHERE name = @db_credential))
		THROW 50001, 'The database-scoped credential does not exists. Use other name or some server-level credential.', 3;
	ELSE
		SET @option = ", CREDENTIAL = '" + @db_credential + "'";
END


IF (@db_credential IS NOT NULL AND @credential IS NOT NULL)
	THROW 50001, 'Cannot specify both server-level and database-level credentials', 3;
DECLARE @openrowset NVARCHAR(MAX) 
SET @openrowset = "OPENROWSET( 
			PROVIDER = 'CosmosDB',
			CONNECTION = '"+@connection+"', 
			OBJECT = '"+@container + "'"
			+ @option + ")"


DECLARE @tsql NVARCHAR(MAX) 
SET @tsql = "SELECT TOP 0 *
FROM " + @openrowset + " as data"

create table #frs (
    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),
    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
);

insert #frs
exec sys.sp_describe_first_result_set @tsql;

declare @with_clause nvarchar(max);
set @with_clause = (select 'WITH (' + string_agg(
											QUOTENAME(name) + ' ' +
											CASE SUBSTRING(system_type_name, 1, 7)
												WHEN 'varchar' THEN system_type_name + ' COLLATE Latin1_General_100_BIN2_UTF8 '
												ELSE system_type_name
											END,
											', ') + ')' from #frs);

select 'SET QUOTED_IDENTIFIER ON'
union all
select 'GO'
'-- Note: This is an autogenerated schema for Cosmos DB container. Try to optimize it and minimize the types like VARCHAR(8000)!'
union all
select '-- create view header (optional)'
union all
select 'CREATE OR ALTER VIEW ' + @container + ' AS '
union all
select '-- query'
union all
select "SELECT * FROM " + @openrowset
union ALL
select '--WITH clause'
union all
select @with_clause + ' AS data'
END
GO