Short Code Examples (T-SQL Tuesday #143)

I've mentioned some of my favourite snippets in my other blog posts. I have plenty more to share, but most of them don't qualify as short. Also, some of them (like the Tally table) are so common that I'm going to skip them.

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

SQL formatters usually leave square brackets untouched. 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 an operation.

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

/* change the datepart and/or value to your needs */
WHILE DATEADD(minute, 60, @startTime) > SYSDATETIME()
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 permissions.

Warning 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 blocking the DROP. 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 analysing a database object, I often need to find dependencies. Nothing ever finds 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
/* Uncomment to check if the referencing code changes the underlying table */
--WHERE br.is_updated = 1 OR br.is_insert_all = 1

Thank you for reading