Mickey's T-SQL Ponderings

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.

1. Add the macro to the library. (Tomorrow I’ll be expanding on this step.)
2. Add the macro to the shortcut menu for Tables.
3. Select several tables that have a common field you would like to remove.
4. Implement your new macro
5. Watch the unwanted fields go away. (When I do this to 30 tables at one time, I actually feel giddy. True story.)

Next Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In