A collection of reusable T-SQL scripts that may come in handy in different SQL Server projects.
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.
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)
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
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)
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
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
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
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).
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
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
Map orphaned users to logins
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
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
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.
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 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
What was the duration of the latest recovery (rollback and roll forward)?
What databases are log shipped?
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/C','INT') AS InvoiceNo ,X.value('/T/C','DATE') AS InvoiceDate ,X.value('/T/C','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