cdm.create_table

Summary

The [cdm].[create_table] stored procedure dynamically builds SQL tables using metadata templates from cont.table_templates. It is used to create consistent core, meta or item tables within a specified schema, complete with partitioning, indexes, and insertion triggers for data integrity and metadata logging.

This approach ensures repeatability, consistency, and automated setup of foundational table structures across domains.

---

🧭 Key Steps

1. Set Context & Defaults
   • Ensures defaults for optional parameters (@tableSchema, @tableType, etc.)
   • Establishes current session metadata such as procedure name and author.
2. Create Schema (if missing)
   • Validates and creates the target schema dynamically.
3. Define Table Name
   • Constructs a slugged, logical table name using entityName, domainSuffix, and tableType.
4. Build Table from Template
   • Reads the cont.table_templates definition to dynamically assemble:
     • Column list
     • Default values
     • Primary key structure (with EXPIRY_DATE if partitioning is enabled)
5. Create Table
   • Executes a CREATE TABLE statement using the constructed SQL.
6. Add Indexes
   • Conditionally creates:
     • Indexed columns defined in the template
     • CHECKSUM index for lookup
     • EXPIRY_DATE index for partitioning
7. Generate Trigger
   • Adds an INSTEAD OF INSERT trigger to:
     • Populate id with NEWID() if missing
     • Auto-fill default fields (CREATE_DATE, SOURCE_SYSTEM, CHECKSUM, etc.)
8. Log to Metadata
   • Inserts an entry into [log].[list_table] for governance and traceability via upsert.table_list.
9. Error Handling
   • Catches and raises detailed errors including procedure name and user context.

---

🧪 Example Usage

EXEC [cdm].[create_table] 
    @entityName   = 'employee',
    @domainSuffix = 'codes',
    @tableType    = 'meta',
    @debug        = 1;

---

🧩 Template Notes

• Templates from cont.table_templates must define:
  • column_name, data_type, default_value, is_nullable
  • Flags for is_idx and is_checksum to drive index and hash logic

---

📂 Parameters Overview

Parameter	Description
@entityName	Logical prefix for table naming
@domainSuffix	Optional suffix (e.g. ‘absence’, ‘contract’)
@debug	Set to 1 for verbose outputs
@tableSchema	Schema under which the table will be created
@tableType	Table pattern, e.g. 'meta' or 'item'
@valuesize	String to replace {valuesize} placeholder in templates
@authoredby	Creator identity for logging
@sourceid	Source system ID used in metadata
@partition_me	If 1, enables partitioning by [EXPIRY_DATE]

---

🔍 Output

• A new table in the specified schema and name
• Accompanying indexes and trigger
• Entry in the list_table log for traceability