Recently, I went through a course at the Information Lab for an introduction into Data Engineering for my future placements. During this time, we learned about different elements when working with a database. This comes in the form of different commands that are a part of different kinds of SQL statements:
- DDL (Data Definition Language) with CREATE, ALTER, DROP, and TRUNCATE.
- DML (Data Manipulation Language) with INSERT, UPDATE, MERGE, and DELETE.
- DQL (Data Query Language) with the classic SELECT.
- DCL (Data Control Language) with GRANT and REVOKE.
STORED PROCEDURES
One interesting and important part of working with databases are Stored Procedures. Stored procedures are reusable blocks of SQL code that can be called anywhere connected to the database. The reason for this is in the name itself: it is a procedure that is stored, being stored in the database itself. You can then call a stored procedure by using the EXEC command followed by the name of the stored procedure.
Within the stored procedure, you can do multiple other statements that would be run one after another. This can let you, for example, drop/truncate a table then recreate it and return the results of a select statement with it. This could be done a part of a pipeline, with a different stored procedure for each layer in your pipeline. That's what we ended up doing as part of the final project for the course I did.
In addition to those statements, you can also define parameter values that can be inputted when you call the stored procedure. You do this by defining the parameters with an '@' right after you've named your stored procedure. This can allow the stored procedure to be a lot more flexible in what it does.
INFORMATION SCHEMA
Another useful part of the database is its information schema. This is where all information about the metadata of the database is kept. This includes details such as the information about each column in each table in each schema. From its data type, the amount it can store, its default value, and more, there's a lot of useful information there.
The Information Schema is read-only as it only represents the details of the different database objects that have been created. What's interesting about it, is that the Information Schema has been defined as part of a standard for all databases, so there's a good chance that it exists in whatever modern database stack that you are using.
I found it most useful when checking the data types of a CTE (Common Table Expression) that I created throughout my pipeline. If I had an issue with a VARCHAR, it would let me see what the size of that VARCHAR. You can also see details about the different constraints that have been put onto the different columns and tables.
If you want to get into the nitty-gritty details of the constraints, then you would need to navigate through the backend of the Information Schema: the sys.objects table and its relatives. This is where details about the exact columns that foreign keys are referencing or the logical condition for a check condition are stored. It leads to some extra work needing to be put in, but it can provide a much greater level of detail.
TABLE DEFINITIONS
During my final project, I ended up needing to use both of these elements of a database system. At the start, I needed to use the stored procedures to define each of the different layers of my pipeline, and I needed to use the Information Schema to check the data types of the tables I was creating.
In order to make the tables in my pipeline, I started by using a SELECT INTO statement, which was nice and easy for my purposes as it didn't take much to create the tables. But when I wanted to introduce constraints into my table, I ran into issues of how I had defined my tables. Adding a primary key to a table required it to also be defined as having a NOT NULL constraint. Attempting to alter the already made table to have NOT NULL columns led to some issues, so another strategy was needed.
I decided to move to defining each of my tables with a CREATE TABLE statement and using an INSERT statement afterwards. But with all the tables I already made and the specific conditions I needed, I didn't want to have to write out each of my statements manually. That's where the Information Schema and a stored procedure come handy!
Because the Information Schema is a read-only table, that means I can read in the values that I want from the Information Schema based on the schema and table that I want to create. Then from there, I can manipulate the values that I've read to be in the same format as a CREATE TABLE statement. Delving deeper into the sys tables, I can even figure out what the different constrains are and define them in the creation step rather than introducing additional ALTER statements later.
Using a stored procedure, I can parameterize the values for the schema and table name, so that the use case of the stored procedure is flexible for any table. It even means that people outside of my schema could use the stored procedure to find out the way that their tables are defined. The way I approached making this stored procedure was broken down into the different levels of details that I would need:
- What are the constraints for each column of the table?
- Combine all constraints into one table, and aggregate to be on one row per column.
- What are the name and data type of the columns in the table? (Making sure to deal with data types with variable sizes)
- Combine all details of the column into a single string.
- Aggregate together everything and add statement around it.
Getting all the constraints together took the longest time as I wanted to be able to deal with as many as I could. To start thinking about what constraints I could deal with, I started by looking at Microsoft's T-SQL documentation for CREATE TABLE. It can take some time to get used to how it represents how it works, but it shows all the possible constraints that can be introduced in the CREATE TABLE statement.
For the constraints, I looked at seven of them:
- PRIMARY KEY - What column(s) define a table.
- FOREIGN KEY - What column(s) are linked to other tables.
- UNIQUE - Every value in the column must be unique.
- NOT NULL - No value in the column can be null.
- CHECK - Every value must hold true for some logical statement.
- DEFAULT - What the default value for a column is if it isn't defined.
- IDENTITY - What value should a column be, starting from a value and incrementing.
With those in mind, I needed to use both the Information Schema and the sys tables to get all the details I wanted for the constraints. What I needed for each constraint was its name, what type it is, and what its definition is if it has one, for each column in a defined schema and table name. Putting these constraints together, I could then follow the steps I previously defined and use the Information Schema to get the higher level details about the table and its columns.
CREATE PROCEDURE my_schema.make_table_definition
@schema VARCHAR(255),
@name VARCHAR(255)
AS
BEGIN
WITH keys AS (
--Gets all constraints from the table constraints table
--Being Primary Keys, Foreign Keys, Checks, and Unique
SELECT
column_constraint.TABLE_SCHEMA AS TABLE_SCHEMA,
column_constraint.TABLE_NAME AS TABLE_NAME,
column_constraint.COLUMN_NAME AS COLUMN_NAME,
column_constraint.CONSTRAINT_NAME AS CONSTRAINT_NAME,
table_constraint.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
--Collects the column definition for foreign keys and checks in a single field
CAST(COALESCE(
checks.definition,
schemas.name + '.' + tables.name + '(' + columns.name + ')'
) AS VARCHAR(1000)) AS CONSTRAINT_DEFINITION
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS column_constraint
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS table_constraint
ON column_constraint.CONSTRAINT_NAME = table_constraint.CONSTRAINT_NAME
LEFT JOIN sys.check_constraints AS checks
ON table_constraint.CONSTRAINT_TYPE = 'CHECK' AND table_constraint.CONSTRAINT_NAME = checks.name
LEFT JOIN sys.foreign_keys
ON table_constraint.CONSTRAINT_TYPE = 'FOREIGN KEY' AND table_constraint.CONSTRAINT_NAME = foreign_keys.name
LEFT JOIN sys.foreign_key_columns
ON foreign_key_columns.constraint_object_id = foreign_keys.object_id
LEFT JOIN sys.tables
ON tables.object_id = foreign_keys.referenced_object_id
LEFT JOIN sys.columns
ON columns.object_id = tables.object_id
AND foreign_key_columns.referenced_column_id = columns.column_id
LEFT JOIN sys.schemas
ON tables.schema_id = schemas.schema_id
WHERE column_constraint.TABLE_SCHEMA = @schema AND column_constraint.TABLE_NAME = @name
), identities AS (
--Gets all identities, which cannot have a constraint name
SELECT
schemas.name AS TABLE_SCHEMA,
tables.name AS TABLE_NAME,
columns.name AS COLUMN_NAME,
NULL AS CONSTRAINT_NAME,
'IDENTITY' AS CONSTRAINT_TYPE,
'(' + CAST(identity_columns.seed_value AS VARCHAR(256)) + ',' + CAST(identity_columns.increment_value AS VARCHAR(256)) + ')' AS CONSTRAINT_DEFINITION
FROM sys.identity_columns
LEFT JOIN sys.tables
ON identity_columns.object_id = tables.object_id
LEFT JOIN sys.columns
ON identity_columns.column_id = columns.column_id
AND columns.object_id = tables.object_id
LEFT JOIN sys.schemas
ON tables.schema_id = schemas.schema_id
WHERE schemas.name = @schema AND tables.name = @name
), not_null AS (
--Gets all not null constraints
--Not nulls can hae constraint names defined but there's no way to find it
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
NULL AS CONSTRAINT_NAME,
'NOT NULL' AS CONSTRAINT_TYPE,
'NOT NULL' AS CONSTRAINT_DEFINITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @name AND IS_NULLABLE = 'NO'
), defaults AS (
--Gets all default constraints
SELECT
schemas.name AS TABLE_SCHEMA,
tables.name AS TABLE_NAME,
columns.name AS COLUMN_NAME,
default_constraints.name AS CONSTRAINT_NAME,
'DEFAULT' AS CONSTRAINT_TYPE,
default_constraints.definition AS CONSTRAINT_DEFINITION
FROM sys.default_constraints
LEFT JOIN sys.tables
ON default_constraints.parent_object_id = tables.object_id
LEFT JOIN sys.columns
ON default_constraints.parent_column_id = columns.column_id
AND columns.object_id = tables.object_id
LEFT JOIN sys.schemas
ON tables.schema_id = schemas.schema_id
WHERE schemas.name = @schema AND tables.name = @name
), constraints AS (
--One row per column in the table
--Converts the contraints
SELECT
COLUMN_NAME,
STRING_AGG(
TRIM(
IIF(CONSTRAINT_NAME IS NOT NULL, 'CONSTRAINT ' + CONSTRAINT_NAME + ' ', '')
--Adding constraint name if exists
+ CASE CONSTRAINT_TYPE
WHEN 'NOT NULL' THEN ''
WHEN 'FOREIGN KEY' THEN 'REFERENCES '
ELSE CONSTRAINT_TYPE + ' '
END --Getting constraint type
+ IIF(CONSTRAINT_DEFINITION IS NOT NULL, CONSTRAINT_DEFINITION, '') --Adding constraint definition if exists
),
' ') AS constraint_name
FROM (
SELECT *
FROM keys
UNION ALL
SELECT *
FROM identities
UNION ALL
SELECT *
FROM not_null
UNION ALL
SELECT *
FROM defaults
) AS con
GROUP BY COLUMN_NAME
), col AS (
SELECT
CAST(@schema + '.' + @name AS VARCHAR(256)) AS TABLE_NAME,
CAST(COLUMNS.COLUMN_NAME AS VARCHAR(256)) AS COLUMN_NAME,
--Deal with Data Type precision
--Numeric and decimal have two values
--Not null will define maximum length in field, otherwise none
CAST(UPPER(DATA_TYPE) +
CASE
WHEN DATA_TYPE IN ('numeric', 'decimal')
THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR(256))
+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(256)) + ')'
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(255))
+ ')'
ELSE ''
END AS VARCHAR(1000)) AS DATA_TYPE,
constraints.constraint_name AS CONSTRAINTS,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
LEFT JOIN constraints
ON COLUMNS.COLUMN_NAME = constraints.COLUMN_NAME
WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @name
)
SELECT
'CREATE TABLE ' + TABLE_NAME + ' (' + CHAR(13) +
(STRING_AGG(
CHAR(9) + COLUMN_NAME + ' ' + DATA_TYPE
+ IIF(CONSTRAINTS IS NOT NULL, ' ' + CONSTRAINTS, ''),
--Combine column information together
',' + CHAR(13)) WITHIN GROUP (ORDER BY ORDINAL_POSITION ASC))
--Aggregate with comma and new line, then order by column position
+ CHAR(13) + ');' AS table_definition
FROM col
GROUP BY TABLE_NAME;
END;
You can then call the stored procedure with:
EXEC my_schema.make_table_definition 'schema_name', 'table_name';
All together is my solution at getting the table definition. It was helpful as I could just copy and paste what was outputted for each table, making that part less intensive. In addition, I also learned a lot about how databases store their metadata and how schemas, tables, columns, and constraints are all linked together.