The two stored procedures below will help with automating the generation and maintenance of a database data dictionary.
If you provide a description for each database field in a SQL Database, these stored procedures can be used to extract that description information together with some other useful column details.
The first stored proc simply details the column information for a single table:
CREATE PROCEDURE uxp_Table_Data_Dictionary
@Table_Name varchar(50)
AS
@Table_Name varchar(50)
AS
— Extract the table data dictionarySELECTTABLE_NAME,
ORDINAL_POSITION,
COLUMN_NAME,
CASE DATA_TYPE
WHEN ‘varchar’ THEN ‘varchar(‘+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20))+’)’
ELSE DATA_TYPE
END AS DATA_TYPE,
ISNULL(COLUMN_DEFAULT, ”) AS COLUMN_DEFAULT,
IS_NULLABLE,
value AS PROPERTY_VALUEFROM
INFORMATION_SCHEMA.COLUMNS AS info
JOIN ::fn_listextendedproperty (NULL, ‘user’, ‘dbo’, ‘table’, @Table_Name, ‘column’, default) AS extended ON info.COLUMN_NAME = extended.objnameWHERE
TABLE_NAME = @Table_Name
The second stored procedure extends this a little and reports the column information for all user tables within the current database:
CREATE PROCEDURE uxp_System_Data_Dictionary
AS
AS
— Create a cursor containing all user tablesDECLARE @tableName varchar(40)DECLARE table_cursor CURSOR FORSELECT name FROM sysobjects WHERE type = ‘U’ AND name <> ‘dtproperties’OPEN table_cursor— Perform the first fetch.FETCH NEXT FROM table_cursor INTO @tableName— Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN— Execute the table data dictionary generatorEXEC uxp_Table_Data_Dictionary @tableName— Fetch the next tableFETCH NEXT FROM table_cursor INTO @tableName
END— Tidy Up
CLOSE table_cursor
DEALLOCATE table_cursor
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb