Short Code Examples (T-SQL Tuesday #143)

Page content

Foreword πŸ”—︎

I’ve mentioned some of my favourite snippets in my other blog posts. I have plenty more to share, but most of them do not fit the description of being short. Also, some of them (like the Tally table) are so common that I’m going to skip it.

To keep things interesting I’ve also added a few RegEx scripts, just because I’m a fan of the syntax.

Remove the square brackets πŸ”—︎

// Find
\[([^]]+)\]

// Replace
$1

I’m using a formatting tool but they usually ignore the square brackets. And I’ve seen code where everything was wrapped in them.

You can try it here along with the explanation.

Replace table variable declaration with a temp table πŸ”—︎

// Find
declare\[email protected]([^\s]+)\s+(?:as\s+)?table

// Replace
CREATE TABLE #$1

Don’t forget to add a case-insensitivity flag. Again, you can try it here.

Time loop πŸ”—︎

Sometimes I need to take snapshots of DMVs for a period of time. Or just timebox some operation.

DECLARE @monitorLoop int = 1 /* init the monitor loop */
DECLARE @startTime datetime2(0) = SYSDATETIME()

WHILE DATEADD(minute, 60, @startTime) > SYSDATETIME() /* change the datepart and/or value to your needs */
BEGIN
	RAISERROR ('Loop number: %i', 1,1, @monitorLoop) WITH NOWAIT
	/* Do your thing here */
	SET @monitorLoop += 1
	WAITFOR DELAY '00:01:00' 
END 

Impersonation πŸ”—︎

I use this most of the time to test the permissions.

⚠️ Careful, you cannot use impersonation to test the module signing permissions.
I’ve learned this the hard way.

EXECUTE AS LOGIN = 'MaintenanceNET' /* pick login*/
-- EXECUTE AS USER = 'MaintenanceNET' /* or pick a user */
SELECT SUSER_NAME(), USER_NAME(), ORIGINAL_LOGIN() /* check if you are impersonating
REVERT /* After you are done, revert to the original login/user */

Recreate an empty database πŸ”—︎

Sometimes when I’m experimenting in a Local environment, I just want to delete the database and start from scratch. There might be open connections that prevent the deletions. Instead of hunting them down, I use this snippet. Replace the DBName with the name of your DB

USE [master];
GO
ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DBName];
GO
CREATE DATABASE [DBName];

Find referencing objects πŸ”—︎

When I’m analysing a database object, often I need to find dependencies. Nothing ever find 100% of the dependencies, but this code gets me close.

/* my references */
;WITH baseReferences
AS
(
	SELECT 
		CONCAT(dsp.referencing_schema_name + '.', dsp.referencing_entity_name) AS referencingObject
		, CONCAT(dsc.referenced_schema_name + '.', dsc.referenced_entity_name) AS referencedObject
		, dsc.referenced_minor_name AS referencedColumn
		, dsc.is_caller_dependent
		, dsc.is_ambiguous
		, dsc.is_selected
		, dsc.is_updated
		, dsc.is_select_all
		, dsc.is_all_columns_found
		, dsc.is_insert_all
		, dsc.is_incomplete
	FROM 
	sys.objects AS o
	CROSS APPLY sys.dm_sql_referencing_entities(CONCAT(SCHEMA_NAME(o.schema_id),'.', o.name), 'object') dsp
	CROSS APPLY sys.dm_sql_referenced_entities(CONCAT(dsp.referencing_schema_name, '.', dsp.referencing_entity_name), 'object') dsc
	WHERE 
		o.object_id = OBJECT_ID('PutYourObjectNameHere')
		AND dsc.referenced_entity_name = o.name
)
SELECT
	DISTINCT PARSENAME(br.referencingObject, 1)
	--*
FROM baseReferences br
--WHERE br.is_updated = 1 OR br.is_insert_all = 1 /* Check if the referencing code changes the underlying table */