Free SQL Help for Devs, DBAs, and the Curious

In today's database-driven world, database metadata is of utmost importance because it provides a detailed schema of how, where, and when data is stored and accessed. It is essential to have a snapshot of your database metadata, as it allows you to view and track the changes in your database structure over time. In this blog post, we will walk through a PowerShell script that helps in capturing the snapshot of database metadata for SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL.

Before diving into the script, let's understand what database metadata is. Database metadata is data about data – it describes various attributes of a database, such as the name, size, ownership, and security settings of database objects like tables, views, indexes, and procedures. Having a snapshot of the database metadata is useful in various scenarios such as auditing, troubleshooting, performance tuning, and database documentation.

Let's start with SQL Server. The following T-SQL script will retrieve metadata such as table name, column name, data type, and maximum length from a SQL Server database.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS;

In MySQL 5.7, you can obtain similar metadata by querying the INFORMATION_SCHEMA.COLUMNS table. This table contains information about all columns in all table schemas of the MySQL instance.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS;

For DB2 11.1, you can retrieve metadata by querying the SYSCAT.COLUMNS catalog view. This view contains detailed information about all columns in all tables in the database.

SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME, LENGTH
FROM SYSCAT.COLUMNS;

In Azure SQL, similar to SQL Server, you can fetch the metadata by querying the INFORMATION_SCHEMA.COLUMNS view.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS;

Now, let's combine these SQL scripts into a PowerShell script. PowerShell, a task automation and configuration management framework from Microsoft, can be used to automate this task of capturing database metadata snapshot. The PowerShell script will connect to the respective database, execute the SQL script to retrieve the metadata, and finally store the output in a CSV file.

The process of capturing database metadata snapshot can be scheduled to run at regular intervals, providing you with a historical record of your database schema. This can be a valuable tool in your arsenal for database management, auditing, and troubleshooting.