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.

Oops, I missed one.

I try to remember to add definitions as I create my tables so that I don’t forget later, but sometimes I miss one…especially definitions for attributes. Here is a great macro for ER/Studio Data Architect to find all the Entities and Attributes that are missing definitions. It even has a feature to colorize the tables that are missing definitions. I call it Verify Definitions For Selected Objects.

Option Explicit
'----------------------------------------------------------------------------
' PURPOSE:  	Creates a list of Entities and Attributes that are missing
'				definitions.
' Author:   	Mickey Stuewe
' Date:     	9/7/2013
'----------------------------------------------------------------------------
Sub Main
	'Defines the dialog box
	Begin Dialog UserDialog 680,322,"Verify Definitions for All Objects", .MyDialogAction
		Text 20,7,360,14,"Entities and Attributes that need Definitions",.Text1
		TextBox 20,28,500,287,.txtList,1
		PushButton 540,28,130,21,"Check",.CheckForDefinitions
		OKButton 540,49,130,21
		CheckBox 540,84,130,14,"Colorize Entities",.chkColor
	End Dialog

	Dim dlg As UserDialog

	'Calls the dialogbox
	Dialog dlg, -2
End Sub

Sub CheckForDefinitions()
    Dim objDiagram As Diagram
	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objEntity As Entity
	Dim objAttr As AttributeObj

	Set objDiagram = DiagramManager.ActiveDiagram
	Set objModel = objDiagram.ActiveModel
	Set objSubModel = objModel.ActiveSubModel

	'Clears list
	DlgText("txtList","")

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objSubModel.SelectedObjects
		Select Case objSelObject.Type
			Case 1 'Entity
				If objModel.Entities(objSelObject.ID).Definition = "" Then
					Call AddToList("Entity", objModel.Entities(objSelObject.ID).Owner & "." & objModel.Entities(objSelObject.ID).EntityName)
					If DlgValue("chkColor") = 1 Then
						objSubModel.EntityDisplays.Item(objModel.Entities(objSelObject.ID).TableName).BackgroundColor = RGB(243,222,248)
					End If

				End If
				For Each objAttr In objModel.Entities(objSelObject.ID).Attributes
					If objAttr.Definition = "" Then
						Call AddToList("Attrib", objModel.Entities(objSelObject.ID).Owner & "." & objModel.Entities(objSelObject.ID).EntityName & "." & objAttr.AttributeName)
						If DlgValue("chkColor") = 1 Then
						objSubModel.EntityDisplays.Item(objModel.Entities(objSelObject.ID).TableName).BackgroundColor = RGB(243,222,248)
						End If
					End If
				Next

			Case 16 'View
				MsgBox "It's time to write code for looking at Definitions for Views."
			Case 54 'Function
				MsgBox "It's time to write code for looking at Definitions for Functions."
		End Select
	Next objSelObject

End Sub

Sub AddToList(strObject As String, strObjectName As String)
	'Adds Entity or Attribute to list.
	DlgText("txtList",DlgText("txtList") & strObject & ": " & strObjectName & vbCrLf )

End Sub

'This is called for all actions in the dialog box. I will be traping for the button being clicked.
Private Function MyDialogAction(DlgItem As String, Action As Integer, SuppValue&) As Boolean

	If Action = 2 Then ' Value changing or button pressed
		Rem MyDialogFunc = True ' Prevent button press from closing the dialog box

		If DlgItem = "CheckForDefinitions" Then
			CheckForDefinitions
			MyDialogAction = True
		Else
			MyDialogAction = False
		End If
	End If
End Function

Previous Post In Series: SQL Advent Calendar – Day 6 – What’s In All These Indexes?

Next Post In Series: SQL Advent Calendar 2013 – Day 8 – Creating Change Tracking Domains (1 of 3)