Notes

πŸ—’ Code snippets, thoughts and ideas that never quite made it to a full blog posts.

Aug 19, 2020

SQL Server script to create a database, login, user and permission

The simplest way to bring up a new database and user for testing and development.

-- Create a new database called 'test-database' with user 'test-database-user' with password 'test-database-password-!@#$@!1'
if db_id('test-database') is null
BEGIN EXEC ('create database [test-database]') END
GO
IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'test-database-user')
BEGIN EXEC sp_addlogin 'test-database-user', 'test-database-password-!@#$@!1', 'master'; END
GO
use [test-database] IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'S' AND name = N'test-database-user') BEGIN CREATE USER [test-database-user] FOR LOGIN [test-database-user] WITH DEFAULT_SCHEMA=[dbo] END
GO
EXEC ('use [test-database] EXEC sp_addrolemember ''db_owner'', ''test-database-user''')

During development I am often firing up new database, especially for testing migrations and seed scripts. This script is the quickest way I found to bring up a new database and user.

It works well with Azure Data Studio as a user snippet (grab it from my Gist here).

Azure Datastudio Create DB

Aug 18, 2020

SQL Server Script to magically cascade delete rows from table and foreign key relationships

This script deletes from the source table and rows in other tables linked by foreign keys.


EXEC uspCascadeDelete
@ParentTableId = 'dbo.User',
@WhereClause = 'User.UserID = 1'

EXEC uspCascadeDelete
@ParentTableId = 'dbo.Product',
@WhereClause = 'Product.Price > 100'
@ExecuteDelete = 'Y' -- execute the delete (default is to print sql statements)

-- run the CREATE PROCEDURE dbo.uspCascadeDelete script first to install

This stored procedure by Aasim Abdullah is awesome for databases where cascade delete has not been set on the foreign keys. You give it the table and conditions and it will find and remove all rows from other tables linked by foreign keys.

The full procedure create script is below, usage examples at the top.

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
    DROP FUNCTION dbo.udfGetFullQualName;

GO
CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
    DECLARE @schema_id AS BIGINT;
    SELECT @schema_id = schema_id
    FROM   sys.tables
    WHERE  object_id = @ObjectId;
    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END

GO
--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
    DROP FUNCTION dbo.udfGetOnJoinClause;

GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
    DECLARE @OnClauseTemplate AS VARCHAR (1000);
    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
    DECLARE @str AS VARCHAR (1000);
    SET @str = '';
    SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM   dbo.sysforeignkeys AS fk
    WHERE  fk.constid = @fkNameId; --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END

GO
--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
    DROP PROCEDURE dbo.uspCascadeDelete;

GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='N', --'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR (8000)='', @Level INT=0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP,  IF LEVEL 0, THEN KEEP DEFAULT
AS -- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys
SET NOCOUNT ON;
/* Set up debug */
DECLARE @DebugMsg AS VARCHAR (4000), 
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
    BEGIN -- assume owner is dbo and calculate id
        IF CHARINDEX('.', @ParentTableId) = 0
            SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
        ELSE
            SET @ParentTableId = OBJECT_ID(@ParentTableId);
    END
IF @Level = 0
    BEGIN
        PRINT @DebugIndent + ' **************************************************************************';
        PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
        IF @ExecuteDelete = 'Y'
            PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
        ELSE
            PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
    END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
    SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
    SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
    EXECUTE (@strSQL);
ELSE
    PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT constid AS fkNameId, -- constraint name
                        fkeyid AS cTableId
        FROM   dbo.sysforeignkeys AS fk
        WHERE  fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
               AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT, 
@cTableId AS INT, 
@cColId AS INT, 
@pTableId AS INT, 
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
    BEGIN
        SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
    END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strFromClause = @FromClause + @CRLF + '      INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON ' + dbo.udfGetOnJoinClause(@fkNameId);
        SET @nLevel = @Level + 1;
        EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
        SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
        FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
    --, @cColId, @pTableId, @pColId
    END
IF @Level = 0
    BEGIN
        SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
        SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
    END
CLOSE curs_children;
DEALLOCATE curs_children;

Aug 17, 2020

SQL Server script to automatically create indexes for foreign keys

This script will generate the SQL to create an index for every foreign key that is missing one.

DECLARE @IndexName TABLE ([SQL] NVARCHAR(500));

WITH
    v_NonIndexedFKColumns
    AS
    (
                    SELECT
                [Table_Name] = OBJECT_NAME(a.[parent_object_id]),
                [Column_Name] = b.[NAME]
            FROM
                sys.foreign_key_columns a
                        , sys.all_columns b
                        , sys.objects c
            WHERE
                        a.[parent_column_id] = b.[column_id]
                AND a.[parent_object_id] = b.[object_id]
                AND b.[object_id] = c.[object_id]
                AND c.[is_ms_shipped] = 0
        EXCEPT
            SELECT
                [Table_Name] = OBJECT_NAME(a.[object_id]),
                [Column_Name] = b.[NAME]
            FROM
                sys.index_columns a
                        , sys.all_columns b
                        , sys.objects c
            WHERE
                        a.[object_id] = b.[object_id]
                AND a.[key_ordinal] = 1
                AND a.[column_id] = b.[column_id]
                AND a.[object_id] = c.[object_id]
                AND c.[is_ms_shipped] = 0
    )
INSERT INTO @IndexName
    ([SQL])
SELECT
    'CREATE INDEX [IX_' + v.[Table_Name] + '_' + v.[Column_Name] + ']'
    + 'ON [' + SCHEMA_NAME(fk.[schema_id]) + '].[' + v.[Table_Name] + '] ([' + v.[Column_Name] + ']);'
FROM
    v_NonIndexedFKColumns v
                    , sys.all_columns c
                    , sys.all_columns c2
                    , sys.foreign_key_columns fkc
                    , sys.foreign_keys fk
WHERE
                    v.[Table_Name] = OBJECT_NAME(fkc.[parent_object_id])
    AND v.[Column_Name] = c.[NAME]
    AND fkc.[parent_column_id] = c.[column_id]
    AND fkc.[parent_object_id] = c.[object_id]
    AND fkc.[referenced_column_id] = c2.[column_id]
    AND fkc.[referenced_object_id] = c2.[object_id]
    AND fk.[object_id] = fkc.[constraint_object_id]

DECLARE @CurrentSQLCommand NVARCHAR(MAX);
DECLARE sqlCommandsCursor CURSOR FOR
                SELECT [SQL]
FROM @IndexName;
OPEN sqlCommandsCursor;
FETCH NEXT FROM sqlCommandsCursor INTO @CurrentSQLCommand;
WHILE @@FETCH_STATUS = 0
                BEGIN
    -- Un-comment the line below to create the indexes
    -- EXEC sp_executesql @CurrentSQLCommand;
    PRINT @CurrentSQLCommand
    FETCH NEXT FROM sqlCommandsCursor INTO @CurrentSQLCommand;
END
CLOSE sqlCommandsCursor;
DEALLOCATE sqlCommandsCursor;

I have recently run into a couple of SQL server databases with performance issues. Investigation showed the likely issue was they were missing indexes on several foreign keys. A teammate introduced me to the magic script above (originally from MSSQLTips.com) to automatically generate index creation scripts.


Aug 15, 2020

Change embedded tweet dark theme to match site

My custom Ghost theme uses a typical CSS/JS dark mode toggle. One thing I found jarring was embedded tweets not updating to the new theme.

I put together this hack javascript (because I could find no better approach) that waits until (hopefully) the tweet has loaded then updates the URL to the proper theme.

var storedTheme = localStorage.getItem('theme') || (window.matchMedia("(prefers-color-scheme: dark)").matches ? "dark" : "light");

setTimeout(function() {
    var targetTheme = storedTheme == "dark" ? "light" : "dark";
    switchTweetTheme(targetTheme, storedTheme);
}, 1000);

function switchTweetTheme(currentTheme, targetTheme) {
    var tweets = document.querySelectorAll('[data-tweet-id]');

    tweets.forEach(function(tweet) {
        var src = tweet.getAttribute("src");
        tweet.setAttribute("src", src.replace("theme=" + currentTheme, "theme=" + targetTheme));
    });
}

Try it out by switching theme on the top right ☝


Aug 15, 2020

Notes scratchpad

I want a home for content that is not quite a tweet and not quite a blog post. Anything tagged "Note" on my site will not appear on the main list but will display inline at notes.

Ghost lets me do this by altering my routes.yml to exclude note tagged items from the index and include them here using a template called tag-inline-content

collections:
  /:
    permalink: /{slug}/
    template: index
    filter: 'tag:-note'    
  /notes/:
    permalink: /{slug}/
    template: tag-inline-content
    filter: 'tag:note'  
    data: tag.note