T-SQL Scripts

A collection of reusable T-SQL scripts that may come in handy in different SQL Server projects.

Numbers function

Numbers Function (GetNums)
A couple of functions that return as many integers as specified in the function call. Two variants for SQL Server 2012 and later, and one variant for SQL Server 2008 R2 and earlier. Two of them based on the article Virtual Auxiliary Table of Numbers by Itzik Ben-Gan. If the numbers are to be used often, persist them in a table (with a clustered index) for better performance.

Calculate Easter

T-SQL Calculate-Easter
Creating a calendar table? The calculation of easter is somewhat complex, but here is a t-sql function to do just that. From TSQL Function to Determine Holidays in SQL Server by Tim Cullen.

Swedish Holidays (Svenska Helgdagar)

Helgdagar
Uses the “Numbers function” and “Calculate Easter” functions above. This script creates an calendar table containing 32000 days (change it if you like) starting from 2000-01-01 (change that as well if you like). It has a column that shows if it is a holiday or not, and if it is a holiday, what type. The script handles Swedish holidays, but it could easily be modified to handle other countries.

Get number of rows per table

Get-number-of-rows-per-table
The correct way to get the count of number of rows in tabels is to issue a SELECT COUNT(*) statement per table. But there is a faster way, altough not as accurate. Check the attached script. So what does it mean, that the method is not accurate? See Kalen Delaney’s article Geek City: How Many Rows?.

Technical Analysis Database (TAdb)

TAdb
This script creates a new database named TAdb. The database serves as a platform for trying out different technical analysis methods. It only containts one table (Quotes) that will hold rows with daily quotes for fictive stocks. The columns are:

The script will load the table Quotes with closing quotes for a couple of years for two different stocks.

Setup and configure SQL Server Alerts

SetupAndConfigureAlerts
A script to configure SQL Server Alerts over multiple servers. Change the variables @DELAY_BETWEEN_RESPONSES and @OPERATOR_NAME. The default is alerts for severity 17-25 and no individual errors, but that can be changed.

List all tables and sizes on a server

ListAllTablesOnAServer
This script uses the undocumented stored procedure sp_MSforeachdb to iterate through all databases on a server and list all tables and their size.

Get the max size of tempdb

GetTempdbMaxSize
There is always a limit on how large tempdb can get. Even if there are no size limitations on the data files in tempdb, the limit is still the free space on disk drives. This is a stored procedure that calculates the maximum size of tempdb (data only, not log).

ScriptLogins

ScriptLogins
Use this procedure to transfer logins between server instances in a log shipping or mirroring scenario. The procedure will return a script string to create all logins on the primary server instance. The script checks if the login exists, and if it does drops it first. It also script all roles and permissions. Create the procedure on the primary server instance. Make sure you have a linked server set up from the secondary to the primary. Run the following T-SQL on the secondary to create all logins.

DECLARE @StatementToRun VARCHAR(MAX)
SELECT @StatementToRun = StatementToRun FROM OPENQUERY("linkedservername", 'EXEC master.dbo.ScriptLogins')
EXEC (@StatementToRun)

Note that the existing logins on the secondary with a matching name with those transferred will be overwritten!

See my blog post Copy logins between SQL Server instances that make use of this script.

A syslogins replacement

Syslogins Replacement
The system table sys.syslogins was marked as deprecated in SQL Server 2005. This is a SQL script that mimics the sys.syslogins table, using SQL Server system views instead. See my blog post A replacement for syslogins.

List SQL server roles for users

SQL Server Roles For Users
A script that lists all server roles and the users with respective permission. Also see my blog post.

Map orphaned users to logins

MapUsersToLogins
The stored procedure dbo.MapUsersToLogins can be used after a database restore to map orphaned users to logins. The names of the user and login must match. Run the procedure on the restored database. If you don’t want to create a procedure, you can just run the script without the “CREATE PROCEDURE” statement.

Stored Procedure with Table Valued Parameter

Stored Procedure With Table Valued Parameter Demo
A simple demonstration of a procedure that accepts a table as parameter.

Demo database with In memory table and Natively Compiled Stored Procedure

InMemoryDemo
A simple database that demostrates In Memory tables and Natively Compiled Stored Procedures introduced in SQL Server 2014.

Get SQL Server Version

Use this to get the current SQL Server Version:

SELECT CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)), 1, CHARINDEX('.', CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)))-1) AS INT)

Get the max or min value from a set of columns

Max or min value from a set of columns
The min and max aggregates in SQL Server are normally used to work over rows, getting the max or min value from a set of rows. But what if you want to get the max or min value from a set of columns instead?

SQL Agent Summary

SQLAgentJobsSummary
A T-SQL script that shows similar information, and more, as the Job Activity Monitor.

The use of WHILE loops and the BREAK and CONTINUE statements

A demonstration of a WHILE loop in T-SQL, with the control statements BREAK and CONTINUE.
WHILE_BREAK_CONTINUE

Get the filename of the latest full database backup with T-SQL

Use this T-SQL script to get the name of the latest full database backup. Replace the database name with your own.
Get Latest Full Backup File Name

Capturing Deadlocks

Capturing Deadlock information
Captures deadlock scenarios and stores information about them in a tempdb table.

Long Running And Blocked Queries

Capturing Long Running And Blocked Queries
Monitors SQL Server for long running (+20 seconds) queries and stores them in a tempdb table. Also sends an email when a query has been blocked by another for more than 20 seconds.

SQL Server Recovery time

GetRecoveryTime
What was the duration of the latest recovery (rollback and roll forward)?

What databases are log shipped?

database_is_log_shipped
A script that lists all databases on an instance and if they are a primary in log shipping setup. Uses the log_shipping_primary_databases table.

Last index of character

Last Index Of Character
This T-SQL shows how to find the last position of a character in a string. Useful for instance when you want to get the file name from a file path, as demonstrated in the script.

Split a string into multiple columns

Check out this demo on how to split delimited strings into separate columns. Read more here.

CREATE TABLE InvoiceString (Col1 VARCHAR(1000))
GO

INSERT InvoiceString VALUES ('123|2016-05-25|8000'), ('456|2017-05-25|4000')

;WITH CTE_Split AS
	(
		SELECT CAST('<T><C>' + REPLACE(Col1,'|','</C><C>') + '</C></T>' AS XML) AS X
		FROM InvoiceString
	)
SELECT
	X.value('/T[1]/C[1]','INT') AS InvoiceNo
	,X.value('/T[1]/C[2]','DATE') AS InvoiceDate
	,X.value('/T[1]/C[3]','INT') AS Amount
FROM CTE_Split

Split a string into multiple rows

The classical problem where a delimited string needs to be split into multiple rows. Or, if you use SQL 2016, use the new STRING_SPLIT function.

DECLARE @String VARCHAR(100) = '123|456|789'

SELECT T.C.value('.', 'VARCHAR(100)') AS X  
FROM (SELECT CAST('<C>' + REPLACE(@String,'|','</C><C>') + '</C>' AS XML) AS X) AS A
CROSS APPLY X.nodes ('/C') AS T(C); 

Merge rows into delimited string

This is the opposite of the “Split a string into multiple columns” script above. Here is a method to merge or concatenate several rows into one delimited string.

DECLARE @TBL TABLE (Col1 VARCHAR(10))
INSERT @TBL VALUES ('ABC'),('DEF'),('GHJ')

;WITH CTE_ROWS AS
	(SELECT SeparatedString = (SELECT Col1 + ',' AS 'data()' FROM @TBL FOR XML PATH('')))
SELECT SUBSTRING(SeparatedString, 1, LEN(SeparatedString)-1) FROM CTE_ROWS

Copy local folder to server

Not T-SQL, but useful anyway. A PowerShell script to copy the contents of a local folder to a backup folder on a server. The “Microsoft.PowerShell.Core\FileSystem::” prefix for the server destination is necessary to aviod the “Source and destination path did not resolve to the same provider. ” error. -Recurse means subfolders will be copied. -Force means files will be overwritten in the destination.

Copy-Item -Path C:\Dev -Destination Microsoft.PowerShell.Core\FileSystem::\\YourServer\Backup -Recurse -Force

List IDENTITY columns that are about to overflow

This script will iterate through all databases on an instance and list all IDENTITY columns that are about to overflow the data type. The exact definition of “about to overflow” is that the current identity value is more than 90 % of the max for the data type. But you can easily change that in the next to last row in the script. Read more here.

IF OBJECT_ID('tempdb..#OverflowColumns') IS NOT NULL BEGIN
    DROP TABLE #OverflowColumns
END
 
CREATE TABLE #OverflowColumns
    (
        DatabaseName SYSNAME NOT NULL,
        TableName SYSNAME NOT NULL,
        ColumnName SYSNAME NOT NULL,
        DataType SYSNAME NOT NULL,
        IdentCurrent NUMERIC(38,0) NULL,
        PercentOfMax NUMERIC(10,2) NULL
    )
 
EXECUTE sp_MSforeachdb 'USE [?]
;WITH CTE_DT AS
    (
        SELECT C.object_id, S.name + ''.'' + SO.name AS TableName, C.name AS ColumnName, C.user_type_id, C.precision, T.name AS DataType, IDENT_CURRENT(S.name + ''.'' + SO.name) AS IdentCurrent
        FROM sys.columns C
        JOIN sys.objects SO
            ON C.object_id = SO.object_id
            AND SO.type = ''U''
        JOIN sys.schemas S
            ON SO.schema_id = S.schema_id
        JOIN sys.types T
            ON C.user_type_id = T.user_type_id
        WHERE C.is_identity = 1
    )
,CTE_DT2 AS
    (
        SELECT object_id, TableName, ColumnName, DataType, IdentCurrent,
            CASE
                WHEN user_type_id = 48 THEN IdentCurrent / 255
                WHEN user_type_id = 52 THEN IdentCurrent / 32767
                WHEN user_type_id = 56 THEN IdentCurrent / 2147483647
                WHEN user_type_id = 127 THEN IdentCurrent / 9223372036854775807
                WHEN user_type_id IN (106,108) THEN IdentCurrent / CAST(REPLICATE(''9'', precision) AS NUMERIC(38,0))
            END AS PercentOfMax
        FROM CTE_DT
    )
INSERT #OverflowColumns (DatabaseName, TableName, ColumnName, DataType, IdentCurrent, PercentOfMax)
SELECT DB_NAME(), TableName, ColumnName, DataType, IdentCurrent, PercentOfMax
FROM CTE_DT2
WHERE PercentOfMax > 0.9
'
SELECT * FROM #OverflowColumns