Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Custom SSMS Shortcuts for ETL Developer. Part 2: Extended Properties

By Olga Klimova,

Quick access to extended properties of a table can be helpful if:

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:

SSMS Tools/Options dialog box keyboard shortcut mapping

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:

The result set: 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.

Total article views: 4024 | Views in the last 30 days: 5
 
Related Articles
FORUM
ARTICLE

Extended Properties Introduction

Part one of a four part series intent on demystifying and making more accessible SQL Server extended...

FORUM

Data Warehouse - Dimensional Model vs Relational Model

The difference between the dimensional model and the relational model for a data warehouse

ARTICLE

Modifying and deleting extended properties

Continuing the short series on extended properties, this article explains how to turbocharge the cre...

ARTICLE

Extracting Extended Properties

In this second article of a short series we look at using the Extended Properties which you have add...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones