snippet | Mickey's T-SQL Ponderings

Tag Archive for snippet

SQLMickey | July 15, 2014

Comments Off

Becoming a SQL Prompt Power User

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. This month we are cooperatively blogging about SQL Prompt by Red Gate.

The Next Mission

What makes up a SQL Prompt power user? Well, they use the SQL prompt commands like they were extensions of SSMS. They know the shortcuts, and they’ve memorized the key words to call up their favorite scripts. Let’s take a look what these short cuts are and how to integrate them seamlessly into your every day SQL Life.

Playing The Ebony Keys

A power user of any software is one who uses the hot keys in lieu of the menus. SQL prompt has a plethora of hot key combination.

Before I use <ctrl> + e to execute my code, I always use <ctrl> + k, <ctrl> + y. This hot key combo will structure your code based on the styles you have set in SQL prompt. This is my most heavily used hot key combo right after <ctrl> + e and <ctrl> + r (hiding the results window).

There is a new hot key combination that is coming out soon in version 6.4, and I’m very excited about it. It adds a semicolon at the end of each statement! I’ve been trying to add the habit of using semicolons for 2 years now, and it never sticks. Now I can use <ctrl> + b, <ctrl> + c and away I go.

Here is a table of the hot key combinations I use the most, plus the shortcut way I remember them.

	Hot Key Combination	Shortcut Hot Key Combo	What It Does
	<ctrl> + b, <ctrl> + w	<ctrl> + bw	Expands ALL of the wild cards.
	<ctrl> + b, <ctrl> + q	+ bq	Qualifies all the SQL Objects and fixes the capitalization as well.
in beta	<ctrl> + b, <ctrl> + c	<ctrl> + bc	Adds semicolons to the end of each statement. (Available soon in 6.4)
	<ctrl> + k, <ctrl> + y	<ctrl> + ky	Standardizes the code based on the formatting styles that were set in SQL Prompt.

As a power user, my key strokes will look like this when I use them all at once:

<ctrl> + bw, bq, bc, ky and finally <ctrl> + e to execute my code. Here is an example of how an ugly duckling turns into a beautiful swan by applying all of these hot keys at one time.

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

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
,s.BusinessEntityID
,s.Name
,s.SalesPersonID
,s.Demographics
,s.rowguid
,s.ModifiedDate
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

Formatting your code is only half of the features in SQL Prompt. There are three other features that every power user should use. (Ok, the truth be told, there are more than 3, but my article shouldn’t be the length of a book.)

Feature 1:

SQL Prompt offers the ability to save snippets of code. This is really useful for repetitive tasks. Here is a list of articles that I’ve written talking about specific snippets that I find handy.

Placeholders are one of the most helpful features that SQL Prompt provides for you to work with snippets. The placeholders are reserved words that are variables for specific pieces of information or represent actions that can be applied to your snippet. They are always completely capitalized and are surrounded by dollar signs ($). You can find the current list for SQL Prompt 6 here.

One of the new placeholders is prefect for the power user. It allows you to insert a snippet and have part of it highlighted. Here is an example of how I like to use it. This particular snippen, which I named “st1”, will add SELECT TOP 100 * FROM in front of the table name that I”ve highlighted and copied to the copy buffer.

$SELECTIONSTART$SELECT TOP 100 *
FROM $PASTE$$SELECTIONEND$

The $PASTE$ placeholder will be replaced with whatever is in my copy buffer. The $SELECTIONSTART$ and $SELECTIONEND$ placeholders will cause the code within the placeholders to be highlighted.

To use it, I follow these steps.

1. I highlight a table I want to use and hit <ctrl> + c to get the table into my copy buffer.

2. I type st1 + to get the snippet. I now have a highlighted SELECT statement ready to execute, so I hit + e to execute it.

3. Finally, I type <ctrl> + z twice to undo the changes I’ve made.

This allows me to quickly see some sample data from the table without ever leaving my current query window.

Feature 2:

This is a hidden gem. This feature finds all the invalid objects in your database. This is a great feature for those of you who need to perform code reviews and want to make sure that any DDL changes have not broken any existing SQL Objects.

In this example I have a table with a trigger that uses the field ModifiedDTS. I deleted the field and ran the command. It listed all the SQL Objects that are now broken due to the deleted field.

This feature does not have a Hot Key, but it is definitely worthy of a power user.

Feature 3:

This is a great “clean up” feature, especially when you are working with stored procedures with 200 + lines that you have inherited. You know the ones, they are filled with old code and have never been cleaned up. Well, this feature is perfect for it, because it finds all the unused variables and parameters. Simply use the hot key combination + b, + f. SQL Prompt, will then underline in green all the unused variables and parameters for you to evaluate for usefulness.

I encourage you to spend 10 minutes going through the SQL Prompt menu to find out what other goodies exist for you to leverage. There are also new features coming soon in version 6.4. Here is a great reference to all the release notes for each version, plus the release notes for the current beta version.

Don’t Stop Yet

If you want to read more about how to work with SQL Prompt, then check out these blog posts:

Chris Yates: SQL Prompt – The Power Within
Julie Koesmarno: Clean And Tidy SQL With SQL Prompt
Jeffrey Verheul: Become a T-SQL Hero with SQL Prompt

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next month!!

Original Post: On a SQL Collaboration Quest

Category: Red Gate, Script, SQL CoOp | Tags: #SQLCoOp, Better Habbits, Chris Yates, Jeffrey Verheul, Julie Koesmarno, macro, Red Gate, snippet, SQL Prompt, Templates

SQLMickey | December 17, 2013

Comments Off

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

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.

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.

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

Category: Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, Templates

SQLMickey | December 16, 2013

2 comments

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

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

Three French Hens, I mean User Defined Functions

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

Scalar Functions
Multi-line Table Functions
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.

Execution Plan for a User Defined Function by Grant Fritchey
Comparing Inline and Multi-Statement Table-Valued Functions by Wayne Sheffield

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?

Category: Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, SQL Prompt, Templates

SQLMickey | December 13, 2013

One comment

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

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

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

Category: Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, SQL Prompt, SQL Script, Templates

SQLMickey | December 12, 2013

Comments Off

SQL Advent Calendar – Day 12 – And Neo Yelled

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

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

Category: Red Gate, Script, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, SQL Script, Templates

SQLMickey | December 11, 2013

2 comments

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

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

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

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.

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

Category: Red Gate, SQL Advent Calendar, T-SQL | Tags: Better Habbits, Red Gate, snippet, SQL Advent Calendar, SQL Script

SQLMickey | December 6, 2013

One comment

SQL Advent Calendar – Day 6 – What’s In All These Indexes?

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

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.

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

Category: Indexes, Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, SQL Prompt, Templates

SQLMickey | December 5, 2013

One comment

SQL Advent Calendar – Day 5 – Snippet For New Indexes

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

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.

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.

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?

Category: Indexes, Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, SQL Script, Templates

SQLMickey | December 4, 2013

2 comments

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

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

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

Category: Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: Red Gate, snippet, SQL Advent Calendar, SQL Prompt, SQL Script

Mickey's T-SQL Ponderings

Tag Archive for snippet

The Next Mission

Playing The Ebony Keys

But Wait, There’s More

Share this:

Like this:

Oh where, oh where did my trigger go?

Share this:

Like this:

Three French Hens, I mean User Defined Functions

Share this:

Like this:

Begin Transaction

Commit Transaction

Share this:

Like this:

A Needed Siesta

Share this:

Like this:

Save the Key Strokes!

If You’re Brave…

Share this:

Like this:

Through the Looking Glass I see Many Indexes

Share this:

Like this:

New Index

Share this:

Like this:

It’s a Dark and Snowy Day

But wait, there’s more…

After

Future Love Notes

Share this:

Like this:

Searching…

Subscribe to Blog via Email. Resistance is futile.

Top Posts & Pages

The Tombs

Tags

These are not the links you are looking for. Move along. Move along.