First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets
As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.
Behind Door Number Two
ER Studio Data Architect by Embarcadero (ER Studio DA) has the ability to create macros, which makes me absolutely happy. Why? Because I don’t like repetitive work. If I can create a macro, then I can have the computer do the repetitive work while I go get a peppermint mocha.
One day I was working on a macro that created fields for multiple tables. I realized during the development process that I also needed a macro to delete fields for multiple tables. So I created the macro below. It has come in handy many times.
'---------------------------------------------------------------------------- ' PURPOSE: This macro adds base attributes to entities that are selected. ' Author: Mickey Stuewe ' Date: 9/3/2013 ' version: 1.0 '---------------------------------------------------------------------------- Sub Main Dim objModel As Model Dim objSelObject As SelectedObject Dim objEntity As Entity Dim iCounter As Integer Begin Dialog UserDialog 440,98,"Continue?" ' %GRID:10,7,1,1 Text 20,21,250,28,"Are you sure you want to remove a field from the selected tables?",.lblOwner OKButton 300,21,120,28 CancelButton 300,56,120,28 Text 20,56,80,14,"Field Name:",.Text1 TextBox 100,56,170,14,.txtFieldName End Dialog Dim dlg As UserDialog If Dialog(dlg) = -1 Then Set objModel = DiagramManager.ActiveDiagram.ActiveModel ' Iterate through all the selected objects in the current ' model. For Each objSelObject In objModel.ActiveSubModel.SelectedObjects 'Get the object type - we are only concerned 'with entities. If objSelObject.Type = 1 Then Set objEntity = objModel.Entities.Item(objSelObject.ID) objEntity.Attributes.Remove(dlg.txtFieldName) iCounter = iCounter + 1 End If Next objSelObject MsgBox dlg.txtFieldName & " was removed from " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Removed From Tables" End If End Sub
To use this macro, follow the steps below.
- Add the macro to the library. (Tomorrow I’ll be expanding on this step.)
- Add the macro to the shortcut menu for Tables.
- Select several tables that have a common field you would like to remove.
- Implement your new macro
- Watch the unwanted fields go away. (When I do this to 30 tables at one time, I actually feel giddy. True story.)
Note: You can download this macro from my Script Library under Resources. It’s called Remove Attribute From Selected Tables. |
Next Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In
[…] want them, you can use my macro called Remove Attribute From Selected Tables, which I blogged about here to remove […]
[…] Previous Post In Series: SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect […]
[…] SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect […]
Very cool Mickey. I use macros in ER Studio all the time. I’ve got several that like you say “then I can have the computer do the repetitive work” and it is wonderful. With your permission I’ll have to add this one to my arsenal.