Thank this author by sharing:
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.
Create a stored procedure that returns extended table properties:
CREATE PROCEDURE dbo.sp_ViewTableExtendedProperties
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
In SSMS Tools/Options dialog box, assign the stored procedure to a keyboard shortcut:
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:
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.
An example of Microsoft Data Warehouse Toolkit dimensional modeling workbook sheet that maps each DW table column to its source
An extract from a script generated by Microsoft Data Warehouse Toolkit dimensional modeling workbook that sets extended properties to a table column
By applying the technique described earlier, you can quickly:
This is the second in a series of tips for the ETL developer. The first one is here.
Part one of a four part series intent on demystifying and making more accessible SQL Server extended...
The difference between the dimensional model and the relational model for a data warehouse
Continuing the short series on extended properties, this article explains how to turbocharge the cre...
In this second article of a short series we look at using the Extended Properties which you have add...
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.