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.

Standardization

I truly believe in having coding standards. I think the best standards that a team can have, are the ones that they are willing to adhere to. If no one agrees that commas belong on the left rather than the right, then why does your standards say they have to be on the left?

One standard that I really like to have, and is supper easy to do using macros,  is having decent names for indexes and constraints. DF_Mytable_1837489274 does absolutely nothing for me. But DF_Mytable_isActive, is helpful when looking at all the constraints on my table.

ER Studio DA has three macros to help with these naming standards. Name foreign Key Constraints, Name Primary Key Constraints, and Index Naming. When creating default constraints in ER Studio Data Architect ( ER Studio DA), I found that I was getting random numbers on my default names in SQL Server. This was especially true if I created my defaults from Domains. (Domains are templates for fields. I wrote about Domains here.)So I created an additional macro called Name Default Constraints-Selected to round out this collection of macros.

My particular standard for naming defaults is as follows: DF__. If you have different standards that can be derived from information in the field, then you can change the pattern on line 42. Take a look at lines 36 and 37 too. These two lines are necessary when working with Domains. They make it possible to edit the default name for a field that is tied to a domain.

This macro will perform two different actions. It will create a name for defaults that don’t have one, and it will ask the user if they want to change a name that does not adhere to the standards. After the macro has completed, a dialog box is displayed showing how many defaults were named and how many had their name updated.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro will create and update Default Constraint names on
'				Attributes For All selected Entities. If the attribute is
'				bound, it will overwrite the bound default and name the default.
' Author:   	Mickey Stuewe
' Date:     	12/15/2013
'----------------------------------------------------------------------------
Option Explicit

Sub Main

	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objAttrib As AttributeObj
	Dim objEntity As Entity
	Dim iCounter As Integer
	Dim iNCcounter As Integer
	Dim iunBoundCounter As Integer
	Dim strDefaultName As String

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

	If Not objModel.Logical Then

		' Iterate through all the selected objects in the current
		' model.
		For Each objSelObject In objSubModel.SelectedObjects
			If objSelObject.Type = 1 Then  'Entities
				Set objEntity = objModel.Entities.Item(objSelObject.ID)
				For Each objAttrib In objEntity.Attributes
					If Len(objAttrib.DeclaredDefault) > 0 Then
						'if the attribute default is bound, then unbind it so that it can be named.
						If objAttrib.DomainId > 0 Then
							'You have to override the bound default value and the default text (name).
							objAttrib.EnableOverride (1,True)
							objAttrib.EnableOverride (2,True)
							iunBoundCounter = iunBoundCounter + 1
						End If

						strDefaultName = "DF_" & objEntity.EntityName & "_" & objAttrib.AttributeName
						If Len(objAttrib.DefaultName) = 0 Then
							objAttrib.DefaultName = strDefaultName
							iCounter = iCounter + 1
						ElseIf objAttrib.DefaultName <> strDefaultName Then
							If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
								vbCrLf & vbCrLf & "Attribute: " & objAttrib.AttributeName & " - Default Name: " & objAttrib.DefaultName & " - Value: " & _
								objAttrib.DeclaredDefault & vbCrLf & " Standardized Name: " & strDefaultName, _
								vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
									objAttrib.DefaultName = strDefaultName
									iCounter = iCounter + 1
							End If
						End If
					Else
						iNCcounter = iNCcounter + 1
					End If
				Next objAttrib
			End If
		Next objSelObject

		MsgBox iunBoundCounter & " had their defaults unbound so that they could be named." & vbCrLf & vbCrLf & _
				iCounter & " Default Constraints had their name created or updated." & vbCrLf & vbCrLf & _
				iNCcounter & " Attributes were not modified.", vbOkOnly + vbInformation, "Default Constraint Name(s) Created or Updated"

	Else
		MsgBox "You are in a Logical model. Move to a Physical model to name of Default Constraints.", vbOkOnly + vbCritical, "Need Physical Model"
	End If
End Sub

Previous Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

Next Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions