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.
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
|Note: You can download this macro from my Script Library under Resources. It’s called Name Default Constraints – Selected.|
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