Tag Archive for SQL Script

T-SQL Tuesday #62 – Primary Key Constraints Are Good For Your Database

Robert Pearl (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

This month’s invitation is about making sure your database is healthy. For me, that is not just making sure that patches are updated and backups restore properly. It also means the database schema is healthy.

In the beginning

One of the simplest things that we can do when creating a new table, is to make sure there is a primary key constraint. A primary key constraint is simply a specialized index. It can be a clustered or non-clustered index.  The important part is, it is created as a primary key constraint.

Why?

Two strong reasons come to mind on why you want to have primary key constraints on your tables. The first, is the accuracy of your data. The purpose of a primary key is to uniquely identify a record (just like your fingerprints). By using primary key constraints, SQL Server will guarantee that the primary keys are unique.

I’ve had application developers argue with me that the application will guarantee that the primary key is unique, so a primary key constraint is not needed. I’ve also had other developers tell me that using GUIDS or identities will guarantee uniqueness. Even if you use these data types or have checks in your application to verify the value is unique, it is still possible to go around the application to insert data and it is possible for multiple applications to simultaneously provide what they think is a unique value. A primary key constraint can will prevent duplication.

This does bring me to the second reason. Say, you do a have a spectacular way to keep all of your primary keys unique. Without primary key constraints, you can’t implement Transactional Replication. That’s right. Transactional Replication requires primary key constraints.

How can I find my straying tables?

Now that I’ve (hopefully) peeked your curiosity, let’s look at a way to find a list of tables that are missing primary key constraints.

SELECT
     t.name AS TableName
FROM
     sys.tables AS t
     LEFT JOIN sys.indexes AS i ON i.object_id = t.object_id
          AND is_primary_key = 1
WHERE
     is_primary_key IS NULL
ORDER BY
     t.name

Remember it is not a good idea to “just add” new constraints without determining the impact on the current system. Once you have the list of tables that are missing primary key constraints, you can go through the process of determining if you can add them into the environment without side affects. Some side affects can include having to deal with duplicate data, increase database size by the addition of new indexes, and better performance.

Thanks for all the fish

Thanks go out to Robert Pearl for hosting this month’s T-SQL Tuesday blog party.

SQL Advent Calendar – Day 13 – Snippet for New Transactional Procedure

AdventCalendar13First 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.

Begin Transaction

Two days ago, I shared my snippet to create a shell for new procedures. A week ago, I found I was writing quite a few update and insert procedures, so I created this new template called new_iu.

The procedures I’m creating are called by an application, so the template I created provides the following:

  • Begin and End Tran
  • Rollback in the error handler
  • Return statement. (0 is successful, anything else is a failure)
  • An output parameter that returns the number of rows changed. We needed this, because more that one kind of row was updated or inserted, but we only want to count the “main” units.

 

USE <DatabaseName, string,$DBNAME$>
GO

--Use Shift + Ctrl + M to set Template Parameters

IF EXISTS ( SELECT * FROM sys.objects WHERE	object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type IN (N'P', N'PC') ) 
	DROP PROCEDURE <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    <purpose,string,[brief description]="">
Notes:      
Sample:    
            DECLARE @RV as int, @RecordsAffected as int
			EXEC @RV = <schemaowner,string,dbo>.<sprocname,string,> [Parameters],@RecordsAffected OUTPUT
			SELECT @RV as ReturnValue, @RecordsAffected as RecordsAffected
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE PROCEDURE <schemaowner,string,dbo>.<sprocname,string,> 
(
	--Insert Parameters
	,@RecordsAffected AS int OUTPUT
)
AS 
BEGIN TRY
	SET NOCOUNT ON
----------------------------------------------------
-------           Test Parameters           --------
----------------------------------------------------
--DECLARE
--    @StartDate AS date = '12/1/2010'
--   ,@EndDate AS date = '12/5/2010'
--   ,@RecordsAffected AS int --OUTPUT
----------------------------------------------------
----------------------------------------------------
	BEGIN TRAN

		$CURSOR$--[TSQL Statements]

	COMMIT TRAN

END TRY
BEGIN CATCH
	DECLARE
		@ErrorMessage AS nvarchar(3000)
	   ,@ErrorSeverity AS int

	IF @@TRANCOUNT > 0
	BEGIN
        ROLLBACK TRAN
		SET @RecordsAffected = 0
	END 

	SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.' + OBJECT_NAME(@@PROCID, DB_ID()),
						N'SQL Object Name Not Available') + N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' + CONVERT(nvarchar(5), ERROR_LINE()) + N' - '
						+ ERROR_MESSAGE()    
	SET @ErrorSeverity = ERROR_SEVERITY()
	RAISERROR(@ErrorMessage, @ErrorSeverity, 1)

	RETURN 1

END CATCH

SET NOCOUNT OFF
RETURN 0
GO
Commit Transaction

If you have other successful ways to write insert, update, or delete stored procedures, please share in the comments.

Note: You can download this snippet from my Script Library under Resources. It’s called New_IU.

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

SQL Advent Calendar – Day 12 – And Neo Yelled

AdventCaledar12First 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.

A Needed Siesta

We are half way to Christmas. Can you believe it? Writing this blog series has been great for me, but tonight I’m tired. So with that, I’m going to share some of the Snippets that come with SQL Prompt.

The first two are fun ones. Type Neo + TAB and Yell + TAB and see what you get. They always make me feel better.

If you really want to get into snippets, you can really save on the keep strokes with the following.

ssf SELECT * FROM
st100 SELECT TOP 100 *
FROM
cj CROSS JOIN
ij INNER JOIN
ob ORDER BY
gb GROUP BY

What other quick strokes do you think should be added?

Previous Post In Series: SQL Advent Calendar 2013 – Day 11 – Snippet For a New Stored Procedure
Next Post In Series: SQL Advent Calendar – Day 13 – Snippet for New Transactional Procedure

SQL Advent Calendar 2013 – Day 11 – Snippet For a New Stored Procedure

AdventCalendar11First 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.

Save the Key Strokes!

The snippet below has saved me from a ton of typing! Just three little letters and TAB.

  • I always have a comment block.
  • I never have to add my name or date to the comment block.
  • My error handler always looks the same.
  • I even have a place for my test parameters.

This snippet, called affectionately New, provides the outer shell for a new stored procedure. It is ideal for non-transactional stored procedures. (I will later share my latest snippet created for transactional stored procedures.) It’s set up as a DROP / CREATE, and the DROP is wrapped in an IF statement so that it doesn’t fail the first time. Your cursor is placed right where your query needs to go. CTRL+SHIFT+M will populate the other unique parts of the script like database name, stored procedure name, etc.

USE <DatabaseName,string,$DBNAME$>
GO

--Use Shift + Ctrl + M to set Template Parameters

IF EXISTS ( SELECT * FROM sys.objects WHERE	object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type IN (N'P', N'PC') ) 
	DROP PROCEDURE <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    <purpose,string,[brief description]="">
Notes:      
Sample:    
            EXEC <schemaowner,string,dbo>.<sprocname,string,> [Parameters]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE PROCEDURE <schemaowner,string,dbo>.<sprocname,string,> 
(
	--Insert Parameters
)
AS 
BEGIN TRY
	SET NOCOUNT ON
----------------------------------------------------
-------           Test Parameters           --------
----------------------------------------------------
--DECLARE 
--		@StartDate datetime = '12/1/2010'
--		,@EndDate datetime = '12/5/2010'
----------------------------------------------------
----------------------------------------------------

		$CURSOR$--[TSQL Statements]

END TRY
BEGIN CATCH

	DECLARE
	    @ErrorMessage AS nvarchar(3000)
	   ,@ErrorSeverity AS int

	SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.' + OBJECT_NAME(@@PROCID, DB_ID()),
						N'SQL Object Name Not Available') + N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' 
						+ CONVERT(nvarchar(5), ERROR_LINE()) + N' - ' + ERROR_MESSAGE()   

	SET @ErrorSeverity = ERROR_SEVERITY()
	RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH

SET NOCOUNT OFF
GO
If You’re Brave…

Currently the $CURSOR$ placeholder is being used on line 37. This means you can immediately start typing your query. If you’re brave, you can change the placeholder to $PASTE$. This will put whatever is in your copy buffer directly in your new stored procedure. (I’m not that brave. Sometimes I have a lot of Excel Data hanging out, drinking spiked eggnog, and singing Karaoke in my copy buffer.)

 

AdventCalendar2013Day11_Img2

 

One of the cool features of this script, is the way the error handler is created. You can comment out lines 3 to 25 (the AS line)  and the error handler won’t fail. Instead of showing the stored procedure name (that you aren’t using) it will simply tell you, you are not in a stored procedure at the moment.

AdventCalendar2013Day11_Img1

 

I hope you enjoy this gem as much as I do.

Note: You can download this snippet from my Script Library under Resources. It’s called New.

Previous Post In Series: SQL Advent Calendar 2013 – Day 10 – Creating Change Tracking Domains (3 of 3)
Next Post In Series: SQL Advent Calendar–Day 12– And Neo Yelled

SQL Advent Calendar 2013 – Day 10 – Creating Change Tracking Domains (3 of 3)

AdventCalendar10First 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.

The Final Piece

The last couple of days I’ve written here and here about how to add simple auditing to your tables using ER Studio Data Architect. Today is the finishing piece. Today I will share my SQL Prompt script that creates the triggers for the tables with the ModifiedBy and ModifiedDTS fields.

The trigger is an update trigger. Whenever the record is change, the trigger will be executed. If the user provided a value for the ModifieidBy field, then that value will be used. If a new value is not provided, then the SUSER_NAME() function will be used. I put that check in there for applications that use a single credential to log in to SQL Server, but the application can send in the actually user. If you don’t need that feature, then it can be taken out.

Before running the code, do the following.

  1. Use CTRL+SHIFT+M to update the script with the values you need. This will include a comma delimited list of table names, and a single schema name. (Eventually, I’ll add functionality for multiple schemas.)
  2. The script is setup to print the triggers to the Messages pane. You can copy the scripts into another window to execute, or you can set the @ReadyToExecute bit to 1. This will cause the scripts to be executed as the triggers are built.
WARNING: Make sure you verify that a trigger with the same name as the one being generated does not exists. IT WILL BE DROPPED with this code. ALSO, make sure adding this trigger, will not cause issues with other existing update triggers.

 

USE 
GO
/* ***********************************************************************************
Purpose:    Creates new update triggers for tables that use ModifiedDTS and ModifiedBy.
Notes:      1. Use Shift + Ctrl + M to set Template Parameters
			2. Change the @ReadyToexecute bit to 1, when you are ready to execute the
				code, or simply run the sqcript that is generated in the Messages pane.
WARNING: 	Make sure you verify that a trigger with the same name as the one being 
		generated  does not exists. IT WILL BE DROPPED with this code. ALSO, make 
		sure adding this trigger, will not cause issues with other existing 
		update trigers.
Author:     Mickey Stuewe, www.mickeystuewe.com
Date:       12/10/2013

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
DECLARE 
	@ReadyToExecute AS bit = 1

DECLARE
	 @Tables AS nvarchar(max) = ''
	,@SchemaName AS nvarchar(50) = '<schema,string,>'
	,@TriggerName AS nvarchar(200)
	,@TableName AS nvarchar(126)
	,@SQL AS nvarchar(MAX)
	,@PrimaryKeys_t_to_d AS nvarchar(MAX)
	,@PrimaryKeys_d_to_i AS nvarchar(MAX)

	DECLARE Trig_Cursor CURSOR	
		FOR 
			WITH cte_Pieces
			AS 
			(
				SELECT
					 1 AS ID
					,1 AS StartString
					,CONVERT(int,CHARINDEX(',', @Tables)) AS StopString
				UNION ALL
				SELECT
					ID + 1 AS ID
					,StopString + 1 AS StartString
					,CONVERT(int,CHARINDEX(',', @Tables, StopString + 1)) AS StopString
				FROM
					cte_Pieces
				WHERE
					StopString > 0
			)
			,cte_Tables
			AS
			(
				SELECT
					SUBSTRING(@Tables, StartString, CASE WHEN StopString > 0 THEN StopString - StartString
																ELSE LEN(@Tables)
														   END) AS TableName
				FROM
					cte_Pieces
			)
			SELECT 
				TableName
				,'trg_' + TableName + '_u' AS TriggerName
			FROM 
				cte_Tables

	OPEN Trig_Cursor
	FETCH NEXT FROM Trig_Cursor
	INTO 
		 @TableName
		,@TriggerName
	WHILE @@FETCH_STATUS = 0
	BEGIN

		--Get all the primary keys
		SET @PrimaryKeys_t_to_d = NULL
		SET @PrimaryKeys_d_to_i = NULL

		SELECT
			@PrimaryKeys_t_to_d = ISNULL(@PrimaryKeys_t_to_d  + SPACE(36) + 'AND ','') +  't.' + KCU.COLUMN_NAME + ' = d.' + KCU.COLUMN_NAME + CHAR(13) + CHAR(10) 
			,@PrimaryKeys_d_to_i = ISNULL(@PrimaryKeys_d_to_i + SPACE(36) + 'AND ','') +  'd.' + KCU.COLUMN_NAME + ' = i.' + KCU.COLUMN_NAME + CHAR(13) + CHAR(10) 

		FROM
			INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
			JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
																				   AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
																				   AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
		WHERE
			tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
			AND KCU.TABLE_NAME =  @TableName
			AND KCU.CONSTRAINT_SCHEMA = @SchemaName
		ORDER BY 
			KCU.ORDINAL_POSITION ASC

		--Drop existing Trigger
		SET @SQL = ''
		SET @SQL = @SQL +  N'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @SchemaName + '].[' +  @TriggerName +  ']''))' + CHAR(13) + CHAR(10)
		SET @SQL = @SQL +  N'DROP TRIGGER ' + @SchemaName + '.' +  @TriggerName	+ CHAR(13) + CHAR(10)

		--PRINT @SQL
		EXECUTE sp_executesql @SQL

		--CREATE New Trigger
		SET @SQL =		  N''																						
		SET @SQL = @SQL + N'/* ********************************************************************************'					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Template:	Created with template by Mickey Stuewe, www.mickeytsuewe.com'								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Purpose:	Set ModifiedDTS and ModifiedBy columns.'					 								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Notes:		ModifiedBy and ModifiedDTS will only changed if they were not changed'						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'			by the user.'																				+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N''																										+ CHAR(13) + CHAR(10) 
		SET @SQL = @SQL + N'Author:		$USER$'  																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Date:		$DATE$' 																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N''																										+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'Revision History'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'(Change Date)	(Author)		(Description of Change)'												+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'-----------------------------------------------------------------'										+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N' *********************************************************************************/'					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'CREATE TRIGGER ' + @SchemaName + '.' +  @TriggerName +													+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	ON ' + @SchemaName + '.' +  @TableName																+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	AFTER UPDATE'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'AS '																									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'BEGIN'																									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET NOCOUNT ON'																						+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	-- Update the ModifiedDTS/By fields if they are not passed in. '									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	UPDATE '																							+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		' + @SchemaName + '.' +  @TableName + 															+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET '																								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		 ModifiedBy = CASE '																			+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN i.ModifiedBy IS NULL THEN SUSER_NAME() '									+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN i.ModifiedBy <> d.ModifiedBy THEN i.ModifiedBy '							+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						WHEN ISNULL(i.ModifiedDTS,d.ModifiedDTS) = d.ModifiedDTS THEN SUSER_NAME() '	+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						ELSE i.ModifiedBy '																+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'						END '																			+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		,ModifiedDTS = GETDATE() '																		+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	FROM '																								+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		' + @SchemaName + '.' +  @TableName + ' AS t '													+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'		INNER JOIN deleted AS d ON ' + @PrimaryKeys_t_to_d												
		SET @SQL = @SQL + N'		INNER JOIN inserted AS i ON ' + @PrimaryKeys_d_to_i												+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'	SET NOCOUNT OFF'																					+ CHAR(13) + CHAR(10)
		SET @SQL = @SQL + N'END '																									+ CHAR(13) + CHAR(10)

		IF @ReadyToExecute = 0
		BEGIN
			SET @SQL = @SQL + N'GO '																									+ CHAR(13) + CHAR(10)

			PRINT @SQL
		END
		ELSE
		BEGIN
			EXECUTE sp_executesql @SQL
		END

		IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[' + @SchemaName + '].[' +  @TriggerName +  ']')) 
			PRINT '--CREATED TRIGGER ' + @SchemaName + '.' +  @TriggerName
		ELSE
			PRINT '--*** TRIGGER ' + @SchemaName + '.' +  @TriggerName + ' was not created.***'

		FETCH NEXT FROM Trig_Cursor
		INTO 
			@TableName
			,@TriggerName
	END
	CLOSE Trig_Cursor
	DEALLOCATE Trig_Cursor

GO

Here is an example of what the trigger will look like. This example has a three part primary key.

AdventCalendar2013Day10_Img1

Previous Post In Series: SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

AdventCalendar09First 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.

Second Step Is Creating The Change Tracking Fields

Today I’ll share with you my macro called Add Change Tracking Attributes. This script will add the attributes InsertedBy, InsertedDTS, ModifiedBy, and ModifiedDTS to the selected entities. These attributes are based on the domains that were created by the macro in yesterday’s post found here. If you add these fields, and then decide you don’t want them, you can use my macro called Remove Attribute From Selected Tables, which I blogged about here to remove them.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds Change Tracking Attributes to Selected Entities.
' Author:   	Mickey Stuewe
' Date:     	12/9/2013
'----------------------------------------------------------------------------

Sub Main

	Dim objModel As Model
	Dim objSelObject As SelectedObject
	Dim objEntity As Entity
	Dim objAttribute As AttributeObj
	Dim iCounter As Integer
	Dim dict As Dictionary
	Dim iInsertedDateID As Integer
	Dim iInsertedByID As Integer
	Dim iModifiedByID As Integer
	Dim iModifiedDateID As Integer

	' Get the Local Data Dictionary.
	Set dict = DiagramManager.ActiveDiagram.Dictionary

    ' Determine Domains.
    Set MyDomain = dict.Domains.Item("InsertedDTS")
    iInsertedDateID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("InsertedBy")
    iInsertedByID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("ModifiedDTS")
    iModifiedDateID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("ModifiedBy")
    iModifiedByID = MyDomain.ID

	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

			' Get the actual entity object with this ID.
			' The model contains the collection of all the
			' entities.
			Set objEntity =  objModel.Entities.Item(objSelObject.ID)

			'Create Tracking fields and set DomainIDs
			Set objAttribute = objEntity.Attributes.Add("InsertedBy", False)
			 SetDefaultDomain objAttribute, iInsertedByID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("InsertedDTS", False)
			SetDefaultDomain objAttribute, iInsertedDateID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("ModifiedBy", False)
			SetDefaultDomain objAttribute, iModifiedByID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("ModifiedDTS", False)
			SetDefaultDomain objAttribute, iModifiedDateID,objEntity.EntityName

			iCounter = iCounter + 1
		End If
	Next objSelObject

	MsgBox "Four new Attributes were added to " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Added To Tables"

End Sub

Sub SetDefaultDomain(objAttribute As AttributeObj, iDomainID As Integer, strEntityName As String)

	objAttribute.DomainId = iDomainID

	If Len(objAttribute.DeclaredDefault) > 0 Then

		'if the attribute default is bound, then unbind it so that it can be named.
		If objAttribute.DomainId > 0 Then
			'You have to override the bound default value and the default text (name).
			objAttribute.EnableOverride (1,True)
			objAttribute.EnableOverride (2,True)

		End If

		If Len(objAttribute.DefaultName) = 0 Then
			objAttribute.DefaultName = "DF_" & strEntityName & "_" & objAttribute.AttributeName

		ElseIf objAttribute.DefaultName <> "DF_" & strEntityName & "_" & objAttribute.AttributeName 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: " & objAttribute.AttributeName & " - Default Name: " & objAttribute.DefaultName & " - Value: " & _
				objAttribute.DeclaredDefault & vbCrLf & " Standardized Name: " & "DF_" & strEntityName & "_" & objAttribute.AttributeName, _
				vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
					objAttribute.DefaultName = "DF_" & strEntityName & "_" & objAttribute.AttributeName

			End If
		End If
	End If
End Sub

Take a look at lines 81 and 82. These two lines are needed to be able to give the default a name and here is why: This macro creates attributes based off of domains. The Inserted attributes have defaults. If we don’t name the defaults, then SQL Server will provide names for them…and they are not pretty. In order for us to be able to override the domain, we have to set the enableoverride property for two properties. Then we’ll be able to provide a name to the default. This step can’t be done in the domain, because defaults are a type of constraint and all constraints need to have unique names.

Tomorrow I’ll show you a Red Gate Snippet used to create the triggers for the tables with these fields.

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

SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

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.

Note: Right now it only provides definitions for Entities and Attributes. I am happy to expand it if you need the other 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

 

AdventCalendar2013Day7_Img1

 

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)

SQL Advent Calendar – Day 5 – Snippet For New Indexes

AdventCalendar05First 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.

New Index

I have four snippets that are templates for new SQL Objects. The one I’m sharing today is for new indexes. The great thing about  snippets like this, is I add comments to help me or my teammates make decisions on how the SQL object is created.

Notice that I included the hint of “Use Shift + Ctrl + M to set Template Placeholders”. I wrote about them here. I start all of my “new” templates with the word new, but you can really call them whatever you want. This one is NewIndex. Notice that some of the Template Placeholders are repeated. That keeps me from having to do repetitive work. (WIN!)

USE <db,string,$dbname$>
GO

--Use Shift + Ctrl + M to set Template Placeholder
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<tablename,string,>') AND name = N'IX_<tablename,string,>_<fields,string,>')
DROP INDEX IX_<tablename,string,>_<fields,string,> ON <schemaowner,string,dbo>.<tablename,string,> WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX IX_<tablename,string,>_<fields,string,> ON <schemaowner,string,dbo>.<tablename,string,>
(
	$CURSOR$<fields,string,> 
)
<optional_include,string,include()>
<optional_where,string,where()>
WITH 
	(FILLFACTOR = <fillfactor,string,95>)ON [PRIMARY] --GUID Datatype use FillFactor = 80, otherwise use FillFactor = 95 
GO

Here is what the dialog box will look like as I fill it out.

AdventCalendar2013Day5_Img1

Here is what the code will look like after I click OK. Notice that the INCLUDE clause is filled out and the WHERE clause has been omitted. One “gotcha” for this, is the list of fields. You have to either replace the underscores with commas in the list or replace the commas with underscores in the name. It still makes my life easy.

AdventCalendar2013Day5_Img2

 

Previous Post In Series: SQL Advent Calendar 2013 – Day 4–Comments Are Like Love Notes to Your Future Self

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

SQL Advent Calendar 2013 – Day 4 – Comments Are Like Love Notes to Your Future Self

AdventCalendar04First 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.

It’s a Dark and Snowy Day

It’s a dark and snowy day and you get an urgent email. There’s a stored procedure that has been running for 90 minutes. It needs to be fixed STAT. You open up the monstrosity (which hasn’t been cracked open in four years) and it looks like Data’s cat threw up in it. The first thing you want to know, is who wrote the thing? The second thing is how can you possibly make the 400 lines legible quickly? It takes 3 emails and 4 phone calls to track down the authors (plural) of the stored procedure and you are finally able to understand the purpose of the code.

Wouldn’t it have been nice if there had been comments? The author’s (plural) names would have helped as well. Below are three SQL Prompt snippets to help with minimal effort, just a couple of keystrokes and the aid of SQL Prompt.

As for making the code legible, that can be done with 3 little keys. CTRL+K+Y. This short cut will format your code based on the settings you dictate. Everything from indentation of certain parts of the code to the placement of commas.
Before

ALTER PROCEDURE [dbo].[SalesTeamAssignment_rptUGLY](@Group AS varchar(1000), @StartDate AS date, @EndDate AS date)AS
SET NOCOUNT ON;
DECLARE @UselessVar AS varchar(max)
SELECT
p.BusinessEntityID ,FirstName,LastName,s.Name AS Store,[Group] AS TerritoryGroup,cr.Name AS CountryRegion,st.Name AS Territory,CustomerCount,s.*
FROM
AdventureWorks2008R2.sales.salesperson AS sp JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN AdventureWorks2008R2.sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID JOIN [AdventureWorks2008R2].[Person].[CountryRegion] AS cr ON st.CountryRegionCode = cr.CountryRegionCode
JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID JOIN dbo.fn_Split(',', @Group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
OUTER APPLY (SELECT COUNT(*) AS CustomerCount FROM AdventureWorks2008R2.sales.Customer AS c WHERE s.BusinessEntityID = c.StoreID ) AS cc;
But wait, there’s more…

If you also use another 3 key shortcut, CTRL+B+Q, you can alias all the fields, which will also make it easier to read.

Note: If you don’t have the table names aliased, then the entire table name and schema name will be used as the alias.
After
ALTER PROCEDURE [dbo].[SalesTeamAssignment_rptUGLY]
	(
	 @Group AS varchar(1000)
	,@StartDate AS date
	,@EndDate AS date
	)
AS
	SET NOCOUNT ON;
	DECLARE	@UselessVar AS varchar(max)
	SELECT
		p.BusinessEntityID
	   ,p.FirstName
	   ,p.LastName
	   ,s.Name AS Store
	   ,st.[Group] AS TerritoryGroup
	   ,cr.Name AS CountryRegion
	   ,st.Name AS Territory
	   ,cc.CustomerCount
	FROM
		AdventureWorks2008R2.sales.salesperson AS sp
		JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
		JOIN AdventureWorks2008R2.sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
		JOIN AdventureWorks2008R2.Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode
		JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID
		JOIN dbo.fn_Split(',', @Group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
		OUTER APPLY (
					 SELECT
						COUNT(*) AS CustomerCount
					 FROM
						AdventureWorks2008R2.sales.Customer AS c
					 WHERE
						s.BusinessEntityID = c.StoreID
					) AS cc;
Future Love Notes

The first snippet I like to use, I call com. It adds a comment block to the query. I even have this added to my templates for new stored procedures, but I’ll share that one later in the month. I love this header because it utilizing the relatively new placeholders, $NAME$ and $DATE$, which I wrote about on Day 1.

Note: $DATE$ also has the ability to have a custom format. An example would be $DATE(MM/dd/yyyy)$. This would create the date 12/04/2013 for today.
/* ***********************************************************************************
Purpose:    $CURSOR$[Brief Description]
Notes:      
Sample:    
            [Sample Call of procedure with sample parameter values]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */

The second snippet is used when you need to modify an existing script. I call it comhist. I personally don’t mind long names, because the intellisence will show me my available options as I type. I usually only need com+<down arrow>+<down arrow>+ tab. This snippet uses  $CURSOR$, $NAME$ and $DATE$. The one downside, is the snippets won’t work inside a comment. (I think I may have to mention that to Red Gate.)

--$DATE$		$USER$			$CURSOR$

The third snippet is used to add a TODO to your code. I use it when I can’t focus on a particular part of the code, but I need a reminder for later. You can then use SQLSearch ( a free tool that can be downloaded from Red Gate) to find all the locations of TODO.

/*TODO: $CURSOR$ - $USER$ - $DATE$ */

Here is what the look like after using them.

/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            [Sample Call of procedure with sample parameter values]
Author:     mstuewe
Date:       12/4/2013

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
12/4/2013		mstuewe			This is a change in the code
************************************************************************************ */

/*TODO: Buy Christmas Presents. - mstuewe - 12/4/2013 */
Note: You can download these snippets from my Script Library under Resources.

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

Next Post In Series: SQL Advent Calendar – Day 5 – Snippet For New Indexes

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

AdventCalendar02As 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.)
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

%d bloggers like this: