Toad World Blog

Adding Extended Properties to All Objects (SQL Server Models)

Jul 15, 2015 7:24:00 AM by Vaclav Frolik

In SQL Server models you can define Extended Properties to various objects manually. In case you wish to add extended properties to multiple objects at once, download a small macro from here.

Extract the content from the zip archive and import the package to Toad Data Modeler.

Then open your SQL Server model and click Macros | Add Extended Properties.

New form opens. Let's say we want to add extended property Version with value 1.0 to all entities in the model.

Select target object type, property name and value and click Execute.

Information about the progress will be added to Message Explorer.

Every entity in the model now has the Version extended property and its value is set to 1.0:

Note: In SQL Server, the extended properties will only be generated if owner is assigned to your entities. If your SQL Preview or generated SQL Script doesn't contain the extended property definitions, run Model Verification to find possible issues:

To fix all the problems at once, click Tools | Schema/Owner Assignment... and assign owner name to all entities quickly:

SQL Preview will show the code for extended properties.

Good Luck!

Tags: Toad Data Modeler Data Modeling

Vaclav Frolik

Written by Vaclav Frolik

Interests:

  • UX design and graphics
  • Databases
  • Product / Project management
  • Web technologies
  • Online marketing

Favorites:

  • Music bands: Pearl Jam, Audioslave, RHCP, Midnight Oil, Alter Bridge...
  • Actors: Kevin, Edward, Jack, Jeff and my kids.
  • Actresses: Kim, Jennifer
  • Sports: MTB, Squash, Football (the one played with feet, not hands)
  • Series: IT Crowd, The Big Bang Theory