search

Heinzad / data-mache

Data Machination
MIT License
0 stars 0 forks source link

MSSQL Meta Programming Column Generator #14

Open Heinzad opened 1 year ago

Heinzad commented 1 year ago

As a developer, I want to obtain a string of column definitions, so that I can use them in dynamic sql for generating tables.

Given a set of column definitions, when I use the string of column definitions in table-creation ddl, then the dynamic sql should be valid for generating the columns in a table.

Heinzad commented 1 year ago

feat #14 script: meta.column_generator.Function.sql

Heinzad commented 1 year ago

/* Column Generator -- Returns a string of column ddl for given column definitions -- Used in table generation ddl -- Assumes one table is in the param

-- script_name: meta.column_generator.Function.sql -- script_author: Adam Heinz -- script_date: 6 Feb 2023 -- script_license: MIT

-- example_usage: ------DECLARE @col_defn [meta].[column_schemata] ; ------DECLARE @col_string varchar(8000) ; ------DECLARE ------ @catalog_name varchar(128) = 'testdb' ------,@schema_name varchar(128) = 'testing' ------,@table_name varchar(128) = 'testable' ------; ------INSERT INTO @col_defn ( ------ [table_catalog] ------,[table_schema] ------,[table_name] ------,[column_name] ------,[ordinal_position] ------,[column_default] ------,[is_nullable] ------,[data_type] ------,[character_maximum_length] ------,[numeric_precision] ------,[numeric_scale] ------,[datetime_precision] ------) ------SELECT ------[table_catalog] ------,[table_schema] ------,[table_name] ------,[column_name] ------,[ordinal_position] ------,[column_default] ------,[is_nullable] ------,[data_type] ------,[character_maximum_length] ------,[numeric_precision] ------,[numeric_scale] ------,[datetime_precision] ------FROM ( ------VALUES ------ ( ------ @catalog_name
------ ,@schema_name
------ ,@table_name ------ ,'CHARACTER_DEMO' ------ ,1 ------ ,NULL ------ ,'YES' ------ ,'varchar' ------ ,50 ------ ,NULL ------ ,NULL ------ ,NULL ------ ) ------ ,( ------ @catalog_name
------ ,@schema_name
------ ,@table_name ------ ,'NUMERIC_DEMO' ------ ,2 ------ ,NULL ------ ,'YES' ------ ,'decimal' ------ ,NULL ------ ,8 ------ ,0 ------ ,NULL ------ ) ------ ,( ------ @catalog_name
------ ,@schema_name
------ ,@table_name
------ ,'DATE_DEMO' ------ ,3 ------ ,NULL ------ ,'YES' ------ ,'datetime2' ------ ,NULL ------ ,NULL
------ ,NULL
------ ,7 ------ ) ------ ,( ------ @catalog_name
------ ,@schema_name
------ ,@table_name
------ ,'SEQUENCE_DEMO' ------ ,4 ------ ,'SEQ' ------ ,'NO' ------ ,'bigint' ------ ,NULL ------ ,NULL
------ ,NULL
------ ,7 ------ )
------) as v ( ------[table_catalog] ------,[table_schema] ------,[table_name] ------,[column_name] ------,[ordinal_position] ------,[column_default] ------,[is_nullable] ------,[data_type] ------,[character_maximum_length] ------,[numeric_precision] ------,[numeric_scale] ------,[datetime_precision] ------) ; ------SELECT * FROM @col_defn ------SET @col_string = [meta].[column_generator] ( ------ @catalog_name
------ ,@schema_name
------ ,@table_name ------ ,@col_defn ------) ; ------PRINT(@col_string) ;

*/