Inspect Object Locking with T-SQL

I recently came across a scenario whereby we wanted to programmatically, via T-SQL, inspect locking behaviour on objects in a particular database when operations were being performed on given data (actually just a SELECT (WITH (NOLOCK) and DELETE statement, on a table). The requirement was to get a direct view on whether the table was having an exclusive lock thrust upon it, or whether shared access was still in play (and get an insight into whether we were in the realms of row-level locking or not).

This turns out to be relatively easy through the use of the inbuilt Dynamic Management Views; specifically the sys.dm_tran_locks view, coupled with a cheeky join to sys.objects for information to identify the object being referenced (this is my own interpretation, from scratch, but you can easily find many similar examples so pick what floats your boat!):

/*
	Interrogate the lock types at database level (can see the lock type on a table when another statement is running)
	-------------------------------------------------------------------------------------------------------------------

	1) For information on the different lock modes in SQL Server visit the following resources:
		a) https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
		b) http://lockergnome.com/2010/01/26/what-are-the-different-lock-modes-in-sql-server/
		c) https://logicalread.com/sql-server-lock-modes-mc03/ - Haven't actually read this one yet but looked intriguing enough to include as a 'bonus' :o)
	2) An interesting aside into the WITH (NOLOCK) hint: http://sqlsoldier.net/wp/sqlserver/thetruthaboutnolockhints
	3) The difference by IX and X locks: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/difference-between-x-and-ix-lock-151903
*/
SELECT 
tl.resource_type AS [ResourceType]
, CASE 
	WHEN tl.request_mode = 'Sch-S'
	THEN 'Schema Stability (ensure entity cannot be dropped)'
	WHEN tl.request_mode = 'IS'
	THEN 'Intent Shared'
	WHEN tl.request_mode = 'IX'
	THEN 'Intent Exclusive (exclusive lock to subordinate resource in the lock hierarchy - table can still be accessed in part)'
	WHEN tl.request_mode = 'X'
	THEN 'Exclusive (full, exclusive access to the resource)'
	ELSE tl.request_mode
END AS [LockType]
, so.[object_id] AS [ObjectId] 
, so.name AS [ObjectName]
, so.type_desc AS [TypeDesc]
FROM sys.dm_tran_locks tl
	-- JOIN to sys.objects to get identifiable information on the resource marked with a lock
	INNER JOIN sys.objects so ON tl.resource_associated_entity_id = so.object_id
-- Show lock information for the database in scope (this could be parameterised - this could be placed in an stored procedure, for example)
WHERE tl.resource_database_id = DB_ID();

In the comments above the code snippet, I’ve included a couple of links to detail the various lock types (information that can be shown in the request_mode view column) that you may encounter. Just run the query whilst a transaction that you want to inspect (for locking activity) is in progress, and voila, you’ll get an idea as to how the resources are being accessed and what impact this is having locking wise.

As a quick illustration let’s knock up a test database and table, using the following script which incorporates a very trivial setup:

-- Test script for checking out the dm_tran_locks test script

-- 1) Create the test database
CREATE DATABASE [LockTestDatabase];
GO

USE [LockTestDatabase];

-- 2) Create the LockTest table within the LockTestDatabase database (super noddy example for illustration only)
CREATE TABLE [dbo].[LockTest]
(
	Id				INT				PRIMARY		KEY	IDENTITY(1, 1)	NOT NULL
	, SomeInfo		NVARCHAR(20)									NOT NULL
);

-- 3) Create some simple test data
DECLARE
@Counter		INT = 1;

WHILE (@Counter <= 250000)		-- Want a non-trivial amount of data so queries take a couple of seconds to complete
BEGIN

	PRINT (@Counter);

	INSERT INTO [dbo].[LockTest]
	(
		SomeInfo
	)
	VALUES
	(
		'Information' + CONVERT(NVARCHAR(6), @Counter)
	);

	SET 
	@Counter += 1;

END;

The insertion of test records takes a short while so it’s possible to run the dm_tran_locks test script and obtain the following results:

LockTest Table Lock State during Insert.

LockTest Table Lock State during Insert.

After the test insertions are complete, attempt to run a SELECT statement using the WITH (NOLOCK) hint:

SELECT 
lt.Id
, lt.SomeInfo
FROM dbo.LockTest lt WITH (NOLOCK);

You should hopefully observe a shared resource lock, for schema stability only, that essentially just prevents the resource from being dropped whilst information is being requested from the table:

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

I’m not going into any kind of detailed explanation with regards to the lock types here, I’m just proving that observing the lock types being applied is easily possible. Lastly, attempt a deletion (wrapped in a rollback, so it can easily be re-run):

BEGIN TRANSACTION

	DELETE
	FROM dbo.LockTest;

ROLLBACK TRANSACTION

A lock is applied that indicates exclusive access to the resource (due to the nature of the delete here, as all rows are being removed), aka table:

LockTest Table Lock State during Deletion.

LockTest Table Lock State during Deletion.

This gives you an insight into how straightforward it is to get an impression on how your SQL objects are being influenced, locking wise, by your code. If anyone has any suggestions on further reading, more useful snippets, etc. then please add a comment below. Thanks, all.

T-SQL Grouping Set Showcase

Greetings all,

In the interim, whilst I juggle my time to get more in-depth posts rolled out (like the one surrounding Alexa skills), I back-tracked once again to look at overlooked features from the distant past of SQL 2008. I’ve chosen the topic of GROUPING SETS as the basis of this post; which basically boils down to a really nice way to return multiple levels of aggregation in a single result set.

A quick run through then – the following example illustrates a sample database with four tables; Staff, Perk, HolidayRequest and SalaryPayment. The HolidayRequest table has a computed column (based on ‘DateFrom’ and ‘DateTo’) that specifies how many days holiday have been requested (per request) and the SalaryPayment table has an ‘Amount’ column (salary per month). These represent our key aggregation fields. I haven’t actually referenced all columns in the samples provided; I just wanted to give the examples a little bit of extra padding (so they feel more complete).

The following T-SQL shows aggregations targeting total holiday days requested grouped by staff name, department and department and job title (see the GetHolidayRequestDaysSumInfo stored procedure in the full listing for further details):

-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Holiday request days (summed), grouped by department
SELECT 
s.Department
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY Department;

-- Holiday request days (summed), grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.Department, s.JobTitle;

The results look like this:

Holiday Request Group Queries.

Holiday Request Group Queries.

With traditional GROUP BY statements you need to split aggregations, so you can only perform one per result set. This is where GROUPING SETS come in handy; here is a single statement that returns the results from the statements above, but in a single result set (again, see the GetHolidayRequestDaysSumInfoUsingGroupingSets for more information, within the full listing):

-- Holiday request days (summed), group sets included for staff name, department and department and job title
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.Department)
	, (s.Department, s.JobTitle)
);

Again, here are the super fabulous results from this particular query:

Holiday Request Grouping Set Queries.

Holiday Request Grouping Set Queries.

Pretty neat! Just to nail the point home here are a few extra examples focusing on the summing of salary payments, grouped by various facets (individual statements, for starters, as shown in the GetSalarySumInfo procedure):

-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Salary sum info grouped by job title
SELECT 
s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.JobTitle;

-- Salary sum info grouped by department
SELECT 
s.Department
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department

-- Salary sum info grouped by perk name
SELECT 
p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY p.PerkName;

-- Salary sum info grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department, s.JobTitle;

Results from our individual statements look a little like this:

Salary Group Queries.

Salary Group Queries.

Lastly, here’s the equivalent, single, query using GROUPING SETS (see GetSalarySumInfoUsingGroupingSets in the full listing):

-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.JobTitle)
	, (s.Department)
	, (p.PerkName)
	, (s.Department, s.JobTitle)
);
Salary Grouping Set Queries.

Salary Grouping Set Queries.

A great little language feature that I have largely overlooked in the past. I do have one or two reservations over performance, but a first inspection of the query plan for the ‘salary’ based example suggests that some marginal gains are on the table, using the stock setup I have (note the table spool, also):

Grouping Sets Performance.

Grouping Sets Performance.

Feels to me like something that deserves further investigation.

Here is the complete listing, if you wish to create the tables with the sample data referenced in this post (procedures, and all, bundled in). Thanks for tuning in!

/* 

	Lewis Grint - 18/03/2017

	GROUPING SETS WORKING SAMPLE 
	-------------------------------------------------
	1) This example uses four mock database tables (created internally by the script, no work required :op). Run this on a test database of your choice.
		- Staff.
		- Perk.
		- HolidayRequest.
		- SalaryPayment.
	2) Utility, mock data is also inserted by this script (table drop statements, if required, can be found just below this header).
	3) The sample data is then aggregated using GROUP BY via two traditionally structured stored procedures (GetHolidayRequestDaysSumInfo and GetSalarySumInfo). Data is returned (for each grouping) using a separate result set.
	4) Stored procedure variants are then presented that using GROUPING SETS - All GROUP BY variations are returned in a single result set (for illustration purposes only, not saying my grouping selections are actually that helpful in the real world!).

*/

/*
-- Drop table statements, as and when required
DROP TABLE dbo.HolidayRequest;
DROP TABLE dbo.SalaryPayment;
DROP TABLE dbo.Staff;
DROP TABLE dbo.Perk;

*/

-- Habitual setting of preferred QUOTED_IDENTIFIER/ANSI_NULL configuration (even if it's not important for this example)
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

-- Utility variables for gathering up the correct StaffId, when required (and payment dates)
DECLARE
@StaffId							INT
, @FebPayDate						DATE = '2017-02-28'
, @JanPayDate						DATE = '2017-01-31'
, @DecPayDate						DATE = '2016-12-31';

-- 1) Create sample tables to illustrate how GROUPING SETS work

-- Perk table added for nothing more than extra kicks. A staff member can select a company perk (referenced from the Staff table)
IF (OBJECT_ID('dbo.Perk', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Perk
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, PerkName					NVARCHAR(30)									NOT NULL
		, INDEX IX_Perk_PerkName	NONCLUSTERED (PerkName)					
	);

END;

-- NOTE: I won't be including more indexes from this point forward, to try and keep the example light(er)

-- Staff table, which for the sake of our example contains first name, last name, job title, department and a fk to 'perks'
IF (OBJECT_ID('dbo.Staff', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Staff
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, FirstName					NVARCHAR(50)									NOT NULL
		, LastName					NVARCHAR(50)									NOT NULL
		, JobTitle					NVARCHAR(50)									NOT NULL
		, Department				NVARCHAR(50)									NOT NULL
		, Perk						INT												NOT NULL
		, CONSTRAINT FK_Staff_Perk	FOREIGN KEY (Perk) REFERENCES dbo.Perk (Id)
	);

END;

-- SalaryPayment table, which contains multiple rows for each staff member (one for each salary payment)
IF (OBJECT_ID('dbo.SalaryPayment', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.SalaryPayment
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, PaymentDate				DATE											NOT NULL
		, Amount					MONEY
		, CONSTRAINT FK_SalaryPayment_Staff			FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

-- HolidayRequest table, which contains multiple rows for each staff member (one for each holiday request)
IF (OBJECT_ID('dbo.HolidayRequest', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.HolidayRequest
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, DateFrom					DATE											NOT NULL
		, DateTo					DATE											NOT NULL
		-- Final column is computed based on the difference between the start and end date (requested), in days. Simple, but fits for the purposes of the example (obviously, doesn't take into account weekends, bank holiday, etc.)
		, NumberOfDaysRequested		AS (DATEDIFF(DAY, DateFrom, DateTo) + 1)
		, CONSTRAINT FK_HolidayRequest_Staff		FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

--2) Insert test data into each table

-- Perk table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Perk p
)
BEGIN

	INSERT INTO dbo.Perk
	(
		PerkName
	)
	VALUES 
	(
		'Free ice cream'
	)
	,
	(
		'Free parking'
	)
	,
	(
		'Beer on arrival'
	)
	,
	(
		'Pat on the back'
	);

END;

-- Staff table test data (including perk info)
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Staff s
)
BEGIN

	INSERT INTO dbo.Staff
	(
		FirstName
		, LastName
		, JobTitle
		, Department
		, Perk	
	)
	VALUES
	(
		'Steve'
		, 'Stevenson'
		, 'Head Honcho'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Marie'
		, 'Pritchard'
		, 'Team Manager'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free parking'
		)
	)
	,
	(
		'Judy'
		, 'Dench'
		, 'Team Manager'
		, 'Island Retreat'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Dave'
		, 'Dodger'
		, 'Chief Work Dodger'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Beer on arrival'
		)
	)
	-- There's another Dave Dodger at the company, but he is actually a pretty useful chap
	,
	(
		'Dave'
		, 'Dodger'
		, 'Hard Worker'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Bob'
		, 'Boots'
		, 'Handle Cranker'
		, 'Main Office'
		,
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Janet'
		, 'Timms'
		, 'Handle Cranker'
		, 'Main Office'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	);

END;

-- SalaryPayment table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.SalaryPayment sp
)
BEGIN
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 5580.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 5240.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 5580.50
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2500.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2425.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2425.15
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2495.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2400.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2400.15
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2122.90
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2105.20
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2105.20
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2115.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2100.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2100.50
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);
	
END;

-- HolidayRequest table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.HolidayRequest hr
)
BEGIN

	-- For ease, everyone has three holiday requests
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-01-30'
		, '2017-02-03'
	)
	,
	(
		@StaffId
		, '2017-05-22'
		, '2017-05-26'
	)
	,
	(
		@StaffId
		, '2017-07-24'
		, '2017-07-28'
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-23'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-16'
	)
	,
	(
		@StaffId
		, '2017-08-14'
		, '2017-08-14'
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-24'
		, '2017-03-25'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-15'
	)
	,
	(
		@StaffId
		, '2017-08-15'
		, '2017-08-16'
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-20'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-03-27'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-04-03'
		, '2017-04-07'
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-31'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-07-31'
		, '2017-07-31'
	)
	,
	(
		@StaffId
		, '2017-09-25'
		, '2017-09-25'
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-05-15'
		, '2017-05-19'
	)
	,
	(
		@StaffId
		, '2017-07-10'
		, '2017-07-12'
	)
	,
	(
		@StaffId
		, '2017-10-11'
		, '2017-10-13'
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-04-10'
		, '2017-04-11'
	)
	,
	(
		@StaffId
		, '2017-06-21'
		, '2017-06-23'
	)
	,
	(
		@StaffId
		, '2017-11-13'
		, '2017-11-17'
	);

END;
GO

-- Create utility stored procedures, just because :op

-- Basic example using the HolidayRequest table
IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfo;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfo
AS
BEGIN

	-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Holiday request days (summed), grouped by department
	SELECT 
	s.Department
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY Department;

	-- Holiday request days (summed), grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets
AS
BEGIN

	-- Holiday request days (summed), group sets included for staff name, department and department and job title
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.Department)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- A couple more stored prodecures, focusing on the SalaryPayment table this time
IF (OBJECT_ID('dbo.GetSalarySumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfo;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfo
AS
BEGIN

	-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Salary sum info grouped by job title
	SELECT 
	s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.JobTitle;

	-- Salary sum info grouped by department
	SELECT 
	s.Department
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department

	-- Salary sum info grouped by perk name
	SELECT 
	p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY p.PerkName;

	-- Salary sum info grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetSalarySumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets
AS
BEGIN

	-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.JobTitle)
		, (s.Department)
		, (p.PerkName)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- Execute stored procedures to inspect the results

-- Holiday aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetHolidayRequestDaysSumInfo;
EXEC dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

-- Salary aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetSalarySumInfo;
EXEC dbo.GetSalarySumInfoUsingGroupingSets;

Coding Snippets in SSMS (Microsoft SQL Server)

Hi all,

Continuing on with the idea of coding snippets, here’s a follow up showing the (very similar) procedure for creating snippets in SQL Server Management Studio (SSMS).

As before, you’ll need a file with the ‘.snippet’ extension to begin, which contains a snippet in an XML format. A basic example is shown below for reference, encapsulating some code that can quickly search a Stored Procedure definition for a particular search term:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
    <CodeSnippet Format="1.0.0">
		<Header>
		<Title>Sys.procedures SQL Check</Title>
			<Shortcut></Shortcut>
		<Description>Creates SQL to check sys.procedures for a literal string.</Description>
		<Author>Lewis Grint</Author>
		<SnippetTypes>
			<SnippetType>Expansion</SnippetType>
		</SnippetTypes>
		</Header>
		<Snippet>
			<Declarations>
				<Literal>
					<ID>SearchTerm</ID>
					<ToolTip>The term to search for.</ToolTip>
					<Default>SearchTerm</Default>
				</Literal>
			</Declarations>
			<Code Language="SQL">
				<![CDATA[--Check SP content for a literal string
SELECT 
name
FROM sys.procedures 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%$SearchTerm$%';
	]]>
			</Code>
		</Snippet>
    </CodeSnippet>
</CodeSnippets>

As you can see, this file takes the familiar ‘.snippet’ format, supporting Snippet, Code and Declarations elements. These include the code snippet itself, and placeholder definitions, as before. You will also find the Header element, where pertinent information about the snippet can be stored, such as author and description.

Easily recognisable territory so far, I’ll think you’ll agree.

Within SSMS, much as in Visual Studio, navigate to Tools > Code Snippets Manager (Ctrl + K, Ctrl + B) to get underway:

Image showing how to access the Code Snippets Manager.

Access Code Snippets Manager.

Again, pretty much plain sailing from here on in. You’ll have the same options as in Visual Studio to import a snippet file (just use the Import button):

Image showing the Code Snippets Manager before Import.

Code Snippets Manager before Import.

Navigate to your snippet file and select it. You’ll now have the opportunity to map the snippet to a nominated folder. In this instance, we’ll use the My Code Snippets folder. Click Finish to complete the process.

Image showing the MSSQL Server Code Snippets Manager Import Dialog.

SSMS Code Snippets Manager Import Dialog.

You should then be able to see your snippet under the nominated folder:

Image showing the Code Snippets Manager after Import.

Code Snippets Manager after Import.

That’s it, you’ve imported the snippet! You can now open a new query and use Ctrl + K, Ctrl + X to launch the Code Snippet Picker, whereby you can use the arrow keys to move between folder and snippets:

Image showing a Snippet before being expanded.

Snippet before Expansion.

Hit Enter to select your snippet; you should then see the following:

Image showing a Snippet after being expanded.

Snippet after Expansion.

Then you can start using your code, utilising tab to cycle through any placeholders. Another trick, that I’ve used, is to Add an entire folder of snippets using the Code Snippets Manager (instead of using Import). Using a ‘tactically’ named folder, you can gain faster access to your snippets:

Shows the results of adding a Custom Snippets Folder.

Adding a Custom Snippets Folder.

This type of snippet is an Expansion snippet. You can write other kinds, such as the Surrounds With snippet, an example of which can be found here on MSDN, illustrating a try/catch construct:

Surrounds With Snippet Example

I hope this has been helpful. Please comment below if you have any thoughts!

Cheerio!

T-SQL MERGE…Completely overlooked!

Good evening all,

Umm…how an earth did I miss this language feature? Where have I been since the release of SQL Server 2008? This really did just pass me by. I’m in the position whereby I don’t often run into this situation; where a T-SQL MERGE statement is directly applicable (i.e. table synchronisation), but still, I was a little embarrassed. Perhaps I have seen this at some point and it’s just been pushed out of my noodle at the expense of some other piece of information. So, red-faced (at least I am), let’s jump right in.

What is it?

In simple terms, this provides a compound way to perform an INSERT, UPDATE or DELETE on a target table, based on a source table. It’s as easy as that; we’ve negated the need for existence checks and other wrapping code/constructs, including the multiple INSERT, UPDATE and DELETE clauses of course. Performance is mentioned on the appropriate MSDN page. However, in my limited testing, I have not yet seen any major issues; just keep this thought on your mental radar anyway.

More information can be found here, so dive in and have a read (which details performance tips, so keep this in mind): T-SQL MERGE

MERGE Example

The following script rigs up some test data, in two example ‘target’ and ‘source’ tables, in an effort to demonstrate the synchronisations capabilities of the MERGE statement (apologies for the colour coding issues, I am using the correct language type but it seems to be taking exception to some of the content!).

/* 
	Very basic MERGE example
	-----------------------------

	Imagine these tables exist across databases, etc. and require synchronisation (or, as per my recent usage, I built up a derived table using CTE's to provide a source data table to compare against the target table to drive modifications)
	NOTE: Tables created for illustration only; not representative of how to structure this in practice of course
*/

-- TARGET TABLE
IF OBJECT_ID('dbo.TARGET_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.TARGET_PERSON_TABLE
	(
		TARGET_PERSON_ID				INT		PRIMARY KEY
		, TARGET_FORENAME				NVARCHAR(255)
		, TARGET_SURNAME				NVARCHAR(255)
		, TARGET_AGE					INT			
	);

END;

-- SOURCE TABLE
IF OBJECT_ID('dbo.SOURCE_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.SOURCE_PERSON_TABLE
	(
		SOURCE_PERSON_ID				INT		PRIMARY KEY
		, SOURCE_FORENAME				NVARCHAR(255)
		, SOURCE_SURNAME				NVARCHAR(255)
		, SOURCE_AGE					INT			
	);

END;

--Arrange some test data into each table (target and source, for illustration)
INSERT INTO dbo.TARGET_PERSON_TABLE
(
	TARGET_PERSON_ID
	, TARGET_FORENAME
	, TARGET_SURNAME
	, TARGET_AGE
)
VALUES
(
	1
	, 'Dave'
	, 'Jones'
	, 32
)
,
(
	2
	, 'Moira'
	, 'Stevens'
	, 27
)
,
(
	3
	, 'Larry'
	, 'Bodsworth'
	, 48
);

INSERT INTO dbo.SOURCE_PERSON_TABLE
(
	SOURCE_PERSON_ID
	, SOURCE_FORENAME
	, SOURCE_SURNAME
	, SOURCE_AGE
)
VALUES
(
	2
	, 'Mandy'
	, 'Stevens'
	, 32
)
,
(
	3
	, 'Larry'
	, 'Rodsworth'
	, 50
)
,
(
	4
	, 'Sandy'
	, 'Ennis'
	, 29
)
,
(
	5
	, 'Wendy'
	, 'Wainwright'
	, 40
);

-- Inspect the target/source table data prior to the MERGE operation
SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Synchronise the target table with the source table, performing matching and INSERT, UPDATE and DELETE operations as required
MERGE dbo.TARGET_PERSON_TABLE tpt
USING SOURCE_PERSON_TABLE spt ON tpt.TARGET_PERSON_ID = spt.SOURCE_PERSON_ID 
	-- If a row is 'matched' (based on the above 'ON' statement) then simply update the target Person
	WHEN MATCHED 
	THEN UPDATE 
		SET 
		tpt.TARGET_FORENAME = spt.SOURCE_FORENAME
		, tpt.TARGET_SURNAME = spt.SOURCE_SURNAME
		, tpt.TARGET_AGE = spt.SOURCE_AGE
	-- If a row is 'not matched' (based on the above 'ON' statement) then do an insert of a new Person into the target table, based on the source table
	WHEN NOT MATCHED
		THEN INSERT
		(
			TARGET_PERSON_ID
			, TARGET_FORENAME
			, TARGET_SURNAME
			, TARGET_AGE
		)
		VALUES
		(
			SOURCE_PERSON_ID
			, SOURCE_FORENAME
			, SOURCE_SURNAME
			, SOURCE_AGE
		)
	-- Lastly, if a the target table contains a row not matched by the source table then remove the target table row entirely
	WHEN NOT MATCHED BY SOURCE 
		THEN DELETE;

/*
	Inspect the target/source table data post MERGE operation (values should be synchronised between correctly between the two tables)

	Expected Results
	----------------------
	1) Person ID 1, Dave Jones, should be removed from the target table (DELETE)
	2) Person ID 2, Forename of 'Moira' should be updated to 'Mandy' and Age should be updated from 27 to 32 (UPDATE)
	3) Person ID 3, Surname of 'Bodsworth' should be updated to 'Rodsworth' and Age should be updated from 48 to 50 (UPDATE)
	4) Person ID 4, Sandy Ennis, should be added to the target table (INSERT)
	5) Person ID 5, Wendy Wainwright, should be added to the target table (INSERT)

*/

SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Clear down the tables post operation, for ease of re-running and re-testing
TRUNCATE TABLE dbo.TARGET_PERSON_TABLE;
TRUNCATE TABLE dbo.SOURCE_PERSON_TABLE;

Here is the state of the target and source tables prior to the MERGE statement being executed:

Illustrates the target and source tables state after the merge process

Target and Source Tables Post Merge.

After execution, you can see the effects of the synchronisation between the target and source tables:

Illustrates the target and source tables state before the merge process

Target and Source Tables Prior to Merging.

If this was useful to you then that’s superb…my guess is that a fair few of you will be shaking your heads wondering which hole in the sand I’ve had my head planted in for the last few years. Well, I call it how I see it and I’m pretty sure I missed the memo, so for you others out there that may have missed this, enjoy!

I’m still playing around with a F#/FitBit related piece at the moment, and I really want it to be a good quality post, so hang on in there as it’s coming soon.

Cheery bye!

A Little FOR XML PATH Nugget

A very small post this one, covering a little nugget that I’d almost forgot until it came up trumps again this week; TSQL FOR XML PATH can be a nice solution for concatenation of strings across rows (in a given column).

It’s fairly common to have the need to concatenate column based data, as the following example illustrates:

--Standard concatenation of column values (comma separated, produces multiple rows)
SELECT 
FORENAME 
+ ', ' 
+ SURNAME AS [FULL_NAME]
FROM dbo.tblTEST t
WHERE t.ID < 4;

However, don’t forget that working through and concatenating row based data, in a particular column, can be achieved simply using the FOR XML PATH construct, just like this:

--Concatenation of row values for a particular column (imagine we wanted comma separated forenames for example) - Provides a single column as structured
SELECT
CONVERT                                     --Conversion required to an NVARCHAR(VALUE) (MAX depending on string size) - The result will be XML when using FOR XML PATH initially
(
    NVARCHAR(100)
    ,
    (
        SELECT
        t.FORENAME + 
        CASE
            WHEN t.ID < 3                    --Don't add a comma after the last value (just for illustration purposes)
            THEN ', '
            ELSE ''
        END
        FROM dbo.tblTEST t
        WHERE t.ID < 4
        FOR XML PATH('')					 --Specify FOR XML PATH using an empty string (we don't want a wrapping element when concatenating strings)
    )
) AS [COMMA_SEPARATED_FORENAMES];

Interesting use of FOR XML PATH that’s well worth keeping in mind, it can come in dead handy. Apologies for the Short and sweet post; it’s the order of the day! I’ve managed to pick up the dreaded lurgy so I’m dosed up on medication and drinking a tonne of coffee! Here’s hoping that this post makes sense when I read it later on.

Until the next time, bye for now!

SQL – More Expansive Object Searching

Evening all,

I thought this would be worth sharing, in its current form. It’s a small piece of SQL (this would be encapsulated in a Stored Procedure) that allows you to search through a host of objects encompassing Stored Procedures, Functions, Views, Triggers and even Indexes for a specific search term (i.e. an object name).

I’ve utilised a CTE here (just for readability more than anything; a small sampling of actual Execution Plans suggest this isn’t hurting performance to structure the code in this way) to pull through the full object definition, for any matches, where types easily support it. Indexes are the tricky ones to deal with here; from what I’ve gathered the definition would need to be manually constructed; hence, I’ve omitted generating a definition for now.

This is essentially a bit of an extension of some rougher and readier code put together in my SQL Debugging Tips – Part Two post:

The code is as follows (would be appropriately wrapped in a Stored Procedure):

SET NOCOUNT ON;

--1) Validation - Ensure that a valid Search Term has been supplied (cannot be NULL or an empty string)
IF (COALESCE(@SEARCH_TERM, '') = '')
BEGIN
	 
	  RAISERROR('@SEARCH_TERM cannot be an empty string or NULL. Please specify a valid value (procedure: YOUR_PROCEDURE_NAME).', 16, 1);
	  RETURN;       --Input invalid, simply return

END;

--Enable a 'fuzzy' match
SET
@SEARCH_TERM = '%' + @SEARCH_TERM + '%';

--CTE that is used to 'bundle' up all of the various objects being search (for the given Search Term)
WITH COMBINED_OBJECTS
(
	  OBJ_ID
	  , OBJ_NAME
	  , OBJ_TYPE
	  , OBJ_DEFINITION
)
AS
(
	  --Search Procedures, Functions, Triggers and Views for the Search Term (in the actual definition of the object)
	  SELECT
	  sm.object_id AS [OBJ_ID]
	  , so.name AS [OBJ_NAME]
	  , so.type_desc AS [OBJ_TYPE]
	  , sm.definition AS [OBJ_DEFINITION]
	  FROM sys.sql_modules sm
			 INNER JOIN sys.objects so ON sm.object_id = so.object_id
	  WHERE sm.definition LIKE @SEARCH_TERM
	  UNION ALL
	  --Search for the Search Term in the name of an index
	  SELECT
	  si.object_id AS [OBJ_ID]
	  , si.name AS [OBJ_NAME]
	  , 'INDEX - ' + si.type_desc COLLATE DATABASE_DEFAULT AS [OBJ_TYPE]		                                                         --Negate collation issues with concatenation														
	  , NULL AS [OBJ_DEFINITION]
	  FROM sys.indexes si
	  WHERE si.name LIKE @SEARCH_TERM
	  UNION ALL
	  --Search for the Search Term in the physical column names that comprise an index definition
	  SELECT
	  si.object_id AS [OBJ_ID]
	  , sc.name + ' (' + si.name COLLATE DATABASE_DEFAULT + ' - ' + si.type_desc COLLATE DATABASE_DEFAULT + ')' AS [OBJ_NAME]            --Negate collation issues with concatenation
	  , 'INDEX_COLUMN' AS [OBJ_TYPE]
	  , NULL AS [OBJ_DEFINITION]
	  FROM sys.indexes si
			 INNER JOIN sys.index_columns sic ON
			 (
				   si.object_id = sic.object_id
				   AND si.index_id = sic.index_id
			 )
			 INNER JOIN sys.columns sc ON
			 (
				   sic.object_id = sc.object_id
				   AND sic.column_id = sc.column_id
			 )
	  WHERE sc.name LIKE @SEARCH_TERM
)
--Return the results to the caller (can be expanded as needed)
SELECT
co.OBJ_ID
, co.OBJ_NAME
, co.OBJ_TYPE
, co.OBJ_DEFINITION
FROM COMBINED_OBJECTS co
ORDER BY co.OBJ_TYPE, co.OBJ_NAME;      --Do a little bit of ordering to make the results easier to digest                                                                                                                                                 

With Indexes, I’ve allowed the ability for a developer to search within the name of the Index and search for hits linked to the columns which comprise the Index.

This is all based on a ‘fuzzy’ search (aka using Wild Cards) – @SEARCH_TERM would be an input parameter of the Stored Procedure of type NVARCHAR. A slight spin on a basic check on sys.procedures and a little more expansive.

Just a small post to get this off my chest.

Cheers!

SQL Debugging Tips – Part Two

A common task within SQL is to search an objects definition for a particular reference. This might be a table, column, procedure, function or view name. Below are a few techniques for achieving this. I’ve also highlighted some tables that are related to SQL Server Maintenance Plans and how to start searching interrogating the data tied to these.

/* 

Searching Object Definitions in SQL.

sys.sql_modules is a fairly complete way to achieve this but there's a myriad of options below, including a way 
to look into SQL Maintainence plans (add a where clause on the command column to filter this down)

*/

DECLARE
@SEARCH_TERM		NVARCHAR(MAX) = '%tblSYS_SCHEMA_COLUMN%';

--Full raw objects that reference tempID (or the SEARCH_TERM) in Views (for reference, select only), Triggers, SP's and Functions
SELECT
so.name
, sm.[definition]
, so.type_desc
FROM sys.sql_modules sm
	INNER JOIN sys.objects so ON sm.object_id = so.object_id
WHERE sm.[definition] LIKE @SEARCH_TERM
ORDER BY so.type_desc, so.name;

--Raw results from sys.procedures (SP's only) - Use for verification with the above sys.sql_modules results
SELECT *
FROM sys.procedures sp
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE @SEARCH_TERM
ORDER BY sp.name;

--Results from the INFORMATION_SCHEMA.ROUTINES system view (encapsulating SP's and Functions) - Use for verification with the above sys.sql_modules results
SELECT
ir.SPECIFIC_NAME
, ir.ROUTINE_TYPE
, ir.*
FROM INFORMATION_SCHEMA.ROUTINES ir
WHERE ir.ROUTINE_DEFINITION LIKE @SEARCH_TERM
ORDER BY ir.ROUTINE_TYPE DESC, ir.SPECIFIC_NAME;

--Check SQL Jobs (Maintenance Plans) for the search term being imbedded in the 
SELECT
sj.name
, sj.[enabled]
, sjs.step_name
, sjs.subsystem
, sjs.command		--Filter the command (by adding a WHERE clause) column by the search term (TSQL subsystems)
FROM msdb.dbo.sysjobs sj
	INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.command LIKE @SEARCH_TERM
ORDER BY sj.name;

/*
SQL JOB TABLES FOR REFERENCE

SELECT * FROM msdb.dbo.SysJobs
SELECT * FROM msdb.dbo.SysJobSteps
SELECT * FROM msdb.dbo.SysJobSchedules
SELECT * FROM msdb.dbo.SysJobServers
SELECT * FROM msdb.dbo.SysJobHistory
*/