To automate one of the most redundant tasks associated with table creation, SqlDBM has added templates. Templates are a way to create and reuse common columns across the entire project.
A typical use case would be adding metadata columns to all existing and new tables in a database. You can create a template with standard columns like “loaded_at” or “etl_filename” and associate it with a table.
How do column templates differ from table templates?
SqlDBM offers users the possibility to create a column as well as table templates. There are several differences in their behavior that should be considered. Please see the link at the end of this article for more on table templates.
Column templates are added to an existing table, while table templates create a new table.
Column templates are binding, and future changes to the template will affect the tables that use them. Table templates are non-binding, and changes to the template do not affect existing tables that have been created as a result.
Coming soon: to give users full flexibility in adding binding or non-binding columns to a table template, it will soon be possible to assign column templates through table templates.
Column templates in action:
Create/edit a template
From the Database Explorer pane, scroll down to “Templates” and click “Create New.” To edit an existing template, click on it to open its properties.
Use the “Template Properties” menu on the left to edit the properties.
Template (properties)
Name: descriptive name for the template
Apply to new tables - tick this option to apply the template to all new tables created in the project.
Columns
In the Columns section of the template properties, use the buttons to add, copy, or delete columns.
+ Icon (Add column) - add a column and assign a data type
Copy Icon (copy column) - click on an existing column to highlight the copy icon to create a copy.
Trash Icon (delete column) - click on an existing column to highlight the trash icon to delete it.
Description
Provide a description of the template and its intended use.
Related Tables
Review all the tables that are associated with this template. Bear in mind that changes to the template will be reflected in the related tables.
Associate/disassociate a template with a table
Create a template using the steps described above.
Click on an existing table to bring up its properties in the left menu.
Navigate to the options section. Type in the name of the template you wish to associate. Notice that the columns from the template have been added to the table.
While the template remains associated with the table, changes to the template will automatically be reflected.
Associate a template with multiple tables
Templates can be applied in bulk to all or selected tables.
Apply to all tables
Apply an existing template to all tables in a project by selecting it and clicking on the "Apply to current tables" icon (double checkmark). This will affect all tables in a project.
Apply to selected tables
Select multiple tables from the diagram (do not include other objects like views and functions) and access the bulk options from the right-hand menu.
Add the desired template by typing in all or part of the template name.
Break template association
To break the association, click the X next to the template name in the options and choose from the options in the popup.
Remove template with columns
Remove template only
Future changes to the template will no longer be reflected in the table.
Bear in mind that a table may have multiple templates applied.
Overriding existing fields
If template columns already exist in a given table, the template may still be applied without causing duplicates.
If existing columns match in name, but not datatype or properties, a confirmation dialog will appear, asking the user to override or skip the template association for the conflicting tables.