Tag Archive for Red Gate

Tribal Awards – Nominated For Best New Community Voice

In December Red Gate introduced the Tribal Awards. Some of the awards are fun like “SQL Karaoke Performer of the Year” and others are more serious like “Blog of the Year”. They collected nominations and I found out that I was selected for “Best New Community Voice”. I’m very honored to be included with the other four gentlemen… all from Europe.

  • Koen Verbeek from Belgium
  • William Durkin from Germany
  • Rob Sewell from United Kingdom
  • Mark S. Rasmussen from Denmark

Now that the nominees have been selected, the community can now vote for the winners of the Tribal Awards, but be quick. The voting ends on January 3rd with the winners being announced on January 7th.

SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

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

Oh where, oh where did my trigger go?

On Day 8, Day 9, and Day 10 I wrote about using 4 Change tracking fields in tables. Two of the fields, ModifiedBy and ModifiedDTS are updated in a trigger. If you are slowly making changes to many tables, you can loose track of which tables have the triggers you need.

This little snippet for SQL Prompt, called FindTrig, will get two result sets for you. The first result set is a comma delimited list of tables that have the ModifiedDTS field, but do not have a trigger with the pattern trg__u. The second result set will show you a the same list of tables and the triggers they already have. This will be helpful when the code to update ModifiedBy and ModifiedDTS was added to a different trigger than expected. You definitely don’t want two triggers updating the same two fields. That will cause an infinite loop of updates.

USE <DBName,string, $DBNAME$>
GO
/* ***********************************************************************************
Purpose:    Creates a comma delimited list of tables that may need a trigger to populate
			the ModifiedBy and ModifiedDTS fields
Author:     mstuewe
Date:       12/17/2013
************************************************************************************ */
DECLARE @Tables AS nvarchar(max) 

	SELECT
		@Tables = ISNULL(@Tables + ',', '') + t.name
    FROM
        sys.objects AS t
        JOIN sys.all_columns AS c ON c.object_id = t.object_id
        LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
                                                 OR tr.name = 'trg_' + t.name + '_u'
    WHERE
        t.type = 'U'
        AND c.NAME = 'ModifiedDTS'
		AND tr.name IS NULL
	ORDER BY 
		t.name

	IF @Tables IS NULL
		PRINT 'All tables have a trigger.'
	ELSE
	BEGIN
		SELECT @Tables AS TableNames

		SELECT
			t.NAME AS TableName
			,tr.name AS ExistingTrigger
		FROM
			(
			SELECT
				t.object_id
				,t.name
			FROM
				sys.objects AS t
				JOIN sys.all_columns AS c ON c.object_id = t.object_id
				LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
														 OR tr.name = 'trg_' + t.name + '_u'
			WHERE
				t.type = 'U'
				AND c.NAME = 'ModifiedDTS'
				AND tr.name IS NULL
			) AS t
			LEFT JOIN sys.Triggers AS Tr ON Tr.parent_id = t.object_id
		ORDER BY 
			t.name
	END
GO

As an example, I created three tables with the four changing tracking fields, Employee, Phone, and PhoneType. I added the trigger to Employee and Phone, but the trigger for Phone had it’s name change. After looking at the results returned, I would know that I only need to create a trigger script for the PhoneType table.

 

AdventCalendar2013Day17_Img1

 

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

 

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

SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

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

Three French Hens, I mean User Defined Functions

There are three kinds of User Defined Functions that you can create in SQL Server.

  1. Scalar Functions
  2. Multi-line Table Functions
  3. In-Line Table Functions

They all have a purpose but unfortunately the first two listed above are misunderstood and misused the most. Below are a couple of articles on the differences and the performance problems that can be caused by Scalar and Multi-line Table Functions.

I created this snippet called NewFun for SQL Prompt when I was first learning the differences between these function types, and I still use to today. Why? It saves key strokes.

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'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            EXEC <schemaowner,string,dbo>.<sprocname,string,> [Parameters]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
(
	--Insert Parameters
)
RETURNS Table
AS 

	RETURN 
	(
		$CURSOR$
	)

GO

 

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

Previous Post In Series: SQL Advent Calendar Day -15 – Naming Default Constraints

Next Post In Series: SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

 

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 – Day 6 – What’s In All These Indexes?

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

Through the Looking Glass I see Many Indexes

Last year, I was doing quite a bit of query tuning. I was constantly looking at what indexes existed before I made new ones.  I created this snippet to Red Gates’ SQL Prompt to be able to quickly look at all the indexes for a given table. Sometimes I even found duplicate indexes when uses this snippet. It’s one of my favorites. I call it Indexes4Table.

--Use Shift + Ctrl + M to set Template Placeholders
USE <database,sysname,$dbname$>
GO
/* ***************************************************
Purpose:    Lists all indexes for a table
Author:     mickey stuewe www.mickeystuewe.com
Date:       12/6/2013
*************************************************** */
SELECT
	i.name AS IndexName
	,i.type_desc
	,STUFF(Indexed.ColumnList, 1, 1, '') AS IndexColumnList
	,STUFF(Included.ColumnList, 1, 1, '') AS IncludeColumnList
	,i.filter_definition
	,i.fill_factor
FROM
	sys.indexes AS i
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 0
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Indexed(ColumnList)
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 1
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Included(ColumnList)
WHERE
	i.object_id =  OBJECT_ID(N'<schema_name,sysname,dbo>.<table_name,sysname,>')
ORDER BY
	i.type_desc
	,Indexed.ColumnList

There are a couple of things I want you to notice. I’m using a string concatenation pattern based on Peter Larson’s (b) SQL, which is written about on Adam Machanic’s (b|t) blog here. I also wanted you to notice that you can use placeholders within a string. See line 57.

I ran my script against the Persons.Person table in AdventureWorks2008R2 (I also added an index so that you can see all the columns filled out. Here is the outcome.

AdventCalendar2013Day6_Img1

Now I can quickly look at all the values that I find important for my indexes on a particular table.

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

Next Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

 

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

%d bloggers like this: