Quick access to extended properties of a table can be helpful if:
- You record metadata information, such as source-target data mapping, into the extended properties of SQL Server tables and need to refer to this information quickly.
- You are a data warehouse developer and use Microsoft Data Warehouse Toolkit dimensional modeling workbook as your data modeling tool.
Follow the next procedure to set up your SQL Server Management Studio environment and use the shortcut.
1. Create a stored procedure
Create a stored procedure that returns extended table properties:
CREATE PROCEDURE dbo.sp_ViewTableExtendedProperties @tablename nvarchar(255) AS declare @cmd NVARCHAR (255) SET @cmd = 'SELECT objtype, objname, name, value FROM fn_listextendedproperty (NULL, ''schema'', ''dbo'', ''table'', ''' + @TABLENAME + ''', ''column'', default);' EXEC sp_executesql @cmd GO |
2. Assign a shortcut key to the sp_ViewTableExtendedProperties stored procedure
In SSMS Tools/Options dialog box, assign the stored procedure to a keyboard shortcut:
3. Invoke extended table properties in a keystroke
First, drag table name from SSMS Object Explorer to Query Editor. Then, select the table and press Ctrl+3 to receive a list of extended properties of the table:
Tip: Use Microsoft Data Warehouse Toolkit dimensional modeling workbook as your data modeling tool
This approach is especially effective if you use the Microsoft Data Warehouse Toolkit dimensional modeling workbook developed by Kimball Group for your data modeling. The Microsoft Excel workbook generates SQL Server CREATE TABLE scripts. The result script includes commands that write source-target mapping information into extended properties of table columns.
| Source | |||||
| Column Name | Source System | Source Schema | Source Table | Source Field Name | Source Datatype |
| EmployeeKey | Derived | ||||
| BKEmployeeID | AW | HumanResources | Employee | EmployeeId | Int |
| NationalIDNumber | AW | HumanResources | Employee | NationalIdNumber | nvarchar(15) |
| EmployeeIDName | AW | HumanResources | Employee | several | |
| EmployeeFullName | Derived | Person | Contact | several | |
| EmployeeFirstName | AW | Person | Contact | FirstName | nvarchar(50) |
An example of Microsoft Data Warehouse Toolkit dimensional modeling workbook sheet that maps each DW table column to its source
| --Column extended properties exec sys.sp_addextendedproperty @name=N'Description', @value=N'Account Number from the transaction system', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AccountNumber'; exec sys.sp_addextendedproperty @name=N'Source Table', @value=N'Customer', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AccountNumber'; exec sys.sp_addextendedproperty @name=N'Source Field Name', @value=N'AccountNumber', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'DimCustomer', @level2type=N'COLUMN', @level2name=N'AccountNumber'; |
An extract from a script generated by Microsoft Data Warehouse Toolkit dimensional modeling workbook that sets extended properties to a table column
Benefits of Calling Extended Properties in a Keystroke
By applying the technique described earlier, you can quickly:
- Troubleshoot data problems
- Resolve data quality issues
- Refer to source metadata to write effective data transformation queries for your ETL processes
- Determine impact of source data changes on your data warehouse structures and communicate this to application development team
This is the second in a series of tips for the ETL developer. The first one is here.
