Short Code Examples (T-SQL Tuesday #143)

Tom

2021-10-12

3 minutes

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\s+@([^\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 */

TSQL
RegEx