Heinzad
commented
1 year ago feat #15 script: meta.table_generator.Procedure.sql
Open Heinzad opened 1 year ago
Heinzad
commented
1 year ago feat #15 script: meta.table_generator.Procedure.sql
Heinzad
commented
1 year ago /* Table Generator -- Performs DDL for a given set of column definitions and a given DDL command. -- NB. Data Definition Language (DDL) defines the schema and the objects in it.
-- script_name: meta.table_generator.Procedure.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) = 'DataHub'
------------ ,@schema_name varchar(128) = 'meta'
------------ ,@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
------------DECLARE @sql nvarchar(4000) ;
------------SET @sql = 'DROP TABLE IF EXISTS ' + QUOTENAME(@schema_name) + CHAR(46) + QUOTENAME(@table_name) + CHAR(59) ;
------------EXEC (@sql) ;
------------EXEC meta.table_generator
------------ @p_columns_tbl = @col_defn
------------,@p_action = 'create'
------------;
------------SELECT ist.
------------FROM INFORMATION_SCHEMA.TABLES as ist
------------WHERE ist.TABLE_CATALOG = @catalog_name
------------AND ist.TABLE_SCHEMA = @schema_name
------------AND ist.TABLE_NAME = @table_name
------------;
*/
Heinzad
commented
1 year ago -- Parameters: @p_columns_tbl [meta].[column_schemata] READONLY / table valued parameter / ,@p_action varchar(128) = 'create' / options: ('create', 'alter', 'drop') /
As a developer, I want to obtain auto-generated ddl, so that I can build tables without manual scripting.
Given a schema name and table name, when I run the dynamic sql provided, then it should be valid ddl for creating or altering a table.