Tag Archives: SQL Server

How to clone SQL Server Logins, Users, Roles and Permissions

June 2, 2018Database Administrator, dba, How To, sql, SQL Server, T-SQL, tsqlclone, copy, guide, How To, howto, lesson, login, permissions, replicate, role, SQL Server, tutorial, userTidbytez

The focus of this post borrows heavily from the work of K. Brian Kelley on mssqltips.com. I’ve consolidated Brian’s excellent 3 part tutorial into a single executable script and made changes to the original works so that the output is a table of executable T-SQL commands instead of executing the commands directly against the instance. I’ve also applied a few fixes to stop errors being thrown when applying database permissions.

The script below creates 5 Stored Procedures to facilitate the cloning of logins, users, roles and permissions with the Stored Procedure CloneLoginAndAllDBPerms acting as the master to the other four. Each Stored Procedure can also be executed independently if all that is necessary is to clone a login or a user etc.

The five stored procedures in the order they run is as follows:

1. CloneLogin
2. CreateUserInDB
3. GrantUserRoleMembership
4. CloneDBPerms
5. CloneLoginAndAllDBPerms

To deploy the stored procedures just copy the main script below, paste it into an open query window in SSMS and change the DATABASE_PLACEHOLDER_NAME to the name of the database you want the Stored Procedures to be deployed against.

Here is a example of how to execute the master Stored Procedure.

Note: For @WindowsLogin, the value F or T is used to indicate whether it is a SQL Server or Windows Login being created. Providing the @DatabaseName Parameter with a single database name will limit the T-SQL commands returned for that database only.

EXEC [dbo].[CloneLoginAndAllDBPerms] @NewLogin = TheNewGuy
	,@NewLoginPwd = N'TheNewGuyPw'
	,@WindowsLogin = N'F'
	,@LoginToClone = TestLogin
	,@DatabaseName = NULL

/*
THERE ARE 5 SPs AS PART OF THIS PROCESS, ONE BEING THE MASTER SP TO CALL THE OTHER FOUR

[1] CloneLogin
[2] CloneDBPerms
[3] CreateUserInDB
[4] GrantUserRoleMembership
[5] CloneLoginAndAllDBPerms

To deploy find and replace DATABASE_PLACEHOLDER_NAME with a utility database name.
*/
USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CloneLogin') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CloneLogin] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[CloneLogin] @NewLogin SYSNAME
	,@NewLoginPwd NVARCHAR(MAX)
	,@WindowsLogin CHAR(1)
	,@LoginToClone SYSNAME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);

	CREATE TABLE #CloneLoginScript (SqlCommand NVARCHAR(MAX));

	SET @SQL = '/' + '*' + 'BEGIN: CLONE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	SET @SQL = '/' + '*' + 'CREATE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	IF (@WindowsLogin = 'T')
	BEGIN
		SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] FROM WINDOWS;'

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END
	ELSE
	BEGIN
		SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] WITH PASSWORD = N''' + @NewLoginPwd + ''';';

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'CLONE SERVER ROLES' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + @NewLogin + ''', @rolename = ''' + R.NAME + ''';' AS 'SQL'
	FROM sys.server_role_members AS RM
	JOIN sys.server_principals AS L ON RM.member_principal_id = L.principal_id
	JOIN sys.server_principals AS R ON RM.role_principal_id = R.principal_id
	WHERE L.NAME = @LoginToClone;

	IF @@ROWCOUNT = 0
	BEGIN
		SET @SQL = '/' + '*' + '---- No Server Roles To Clone' + '*' + '/';;

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'CLONE SERVER PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT [SQL]
	FROM (
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' TO [' + @NewLogin + '] WITH GRANT OPTION;'
				ELSE 'USE master;  ' + P.state_desc + ' ' + P.permission_name + ' TO [' + @NewLogin + '];'
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 100
			AND P.type <> 'COSQ'
		
		UNION ALL
		
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' ON LOGIN::[' + L2.NAME + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
				ELSE 'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON LOGIN::[' + L2.NAME + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		JOIN sys.server_principals AS L2 ON P.major_id = L2.principal_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 101
		
		UNION ALL
		
		SELECT CASE P.[STATE]
				WHEN 'W'
					THEN 'USE master; GRANT ' + P.permission_name + ' ON ENDPOINT::[' + E.NAME + '] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
				ELSE 'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON ENDPOINT::[' + E.NAME + '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
				END AS [SQL]
		FROM sys.server_permissions AS P
		JOIN sys.server_principals AS L ON P.grantee_principal_id = L.principal_id
		JOIN sys.endpoints AS E ON P.major_id = E.endpoint_id
		WHERE L.NAME = @LoginToClone
			AND P.class = 105
		) AS ServerPermission;

	IF @@ROWCOUNT = 0
	BEGIN
		SET @SQL = '/' + '*' + '---- No Server Permissions To Clone' + '*' + '/';;

		INSERT INTO #CloneLoginScript (SqlCommand)
		SELECT @SQL
	END

	SET @SQL = '/' + '*' + 'END: CLONE SERVER LOGIN' + '*' + '/';

	INSERT INTO #CloneLoginScript (SqlCommand)
	SELECT @SQL;

	SELECT *
	FROM #CloneLoginScript
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CreateUserInDB') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CreateUserInDB] AS SELECT 1')
GO

ALTER PROC [dbo].[CreateUserInDB] @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneDbUserScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CREATE DATABASE USER' + '*' + '/';

	INSERT INTO #CloneDbUserScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = '
INSERT INTO #CloneDbUserScript (SqlCommand)
SELECT ''USE [' + @DbName + ']; 
IF EXISTS(SELECT name FROM sys.database_principals 
WHERE name = ' + '''''' + @LoginToClone + '''''' + ')
BEGIN
CREATE USER [' + @NewLogin + '] FROM LOGIN [' + @NewLogin + '];
END;''';

		EXEC (@SQL);
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneDbUserScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database User To Create' + '*' + '/';;

		INSERT INTO #CloneDbUserScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CREATE DATABASE USER' + '*' + '/';

	INSERT INTO #CloneDbUserScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneDbUserScript;

	DROP TABLE #CloneDbUserScript;
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.GrantUserRoleMembership') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[GrantUserRoleMembership] AS SELECT 1')
GO

ALTER PROC dbo.GrantUserRoleMembership @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneRoleMembershipScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CLONE DATABASE ROLE MEMBERSHIP' + '*' + '/';

	INSERT INTO #CloneRoleMembershipScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = '
INSERT INTO #CloneRoleMembershipScript (SqlCommand) 
SELECT ''USE [' + @DBName + ']; EXEC sp_addrolemember @rolename = '''''' + r.name 
+ '''''', @membername = ''''' + @NewLogin + ''''';''
FROM [' + @DBName + '].sys.database_principals AS U
JOIN [' + @DBName + '].sys.database_role_members AS RM
ON U.principal_id = RM.member_principal_id
JOIN [' + @DBName + '].sys.database_principals AS R
ON RM.role_principal_id = R.principal_id
WHERE U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL);
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneRoleMembershipScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database Roles To Clone' + '*' + '/';;

		INSERT INTO #CloneRoleMembershipScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CLONE DATABASE ROLE MEMBERSHIP' + '*' + '/';

	INSERT INTO #CloneRoleMembershipScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneRoleMembershipScript;

	DROP TABLE #CloneRoleMembershipScript;
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CloneDBPerms') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CloneDBPerms] AS SELECT 1')
GO

ALTER PROC dbo.CloneDBPerms @NewLogin SYSNAME
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(max);
	DECLARE @DbName SYSNAME;
	DECLARE @Database TABLE (DbName SYSNAME)

	SET @DbName = ''

	CREATE TABLE #CloneDbPermissionScript (SqlCommand NVARCHAR(MAX));

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT NAME
		FROM sys.databases
		WHERE state_desc = 'ONLINE'
		ORDER BY NAME ASC;
	END
	ELSE
	BEGIN
		INSERT INTO @Database (DbName)
		SELECT @DatabaseName
	END;

	SET @SQL = '/' + '*' + 'BEGIN: CLONE DATABASE PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneDbPermissionScript (SqlCommand)
	SELECT @SQL;

	WHILE @DbName IS NOT NULL
	BEGIN
		SET @DbName = (
				SELECT MIN(DbName)
				FROM @Database
				WHERE DbName > @DbName
				)
		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON DATABASE::[' + @DbName + '] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON DATABASE::[' + @DbName + '] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	WHERE class = 0
	  AND P.[type] <> ''CO''
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON SCHEMA::['' 
		 + S.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = P.major_id
	WHERE class = 3
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.objects AS O
		ON O.object_id = P.major_id
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = O.schema_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + '''
	  AND P.major_id > 0
	  AND P.minor_id = 0';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '] WITH GRANT OPTION;'' 
		 COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON OBJECT::['' 
		 + S.name + ''].['' + O.name + ''] ('' + C.name + '') TO [' + @NewLogin + '];'' 
		 COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.objects AS O
		ON O.object_id = P.major_id
	  JOIN [' + @DbName + '].sys.schemas AS S
		ON S.schema_id = O.schema_id
	  JOIN [' + @DbName + '].sys.columns AS C
		ON C.column_id = P.minor_id AND o.object_id = C.object_id
	WHERE class = 1
	  AND U.name = ''' + @LoginToClone + 
			'''
	  AND P.major_id > 0
	  AND P.minor_id > 0;'

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON USER::['' 
		 + U2.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.database_principals AS U2
		ON U2.principal_id = P.major_id
	WHERE class = 4
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand)
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON SYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.symmetric_keys AS K
		ON P.major_id = K.symmetric_key_id
	WHERE class = 24
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON ASYMMETRIC KEY::['' 
		 + K.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.asymmetric_keys AS K
		ON P.major_id = K.asymmetric_key_id
	WHERE class = 26
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)

		SET @SQL = 'INSERT INTO #CloneDbPermissionScript(SqlCommand) 
	SELECT CASE [state]
	   WHEN ''W'' THEN ''USE [' + @DbName + ']; GRANT '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '] WITH GRANT OPTION;'' COLLATE DATABASE_DEFAULT
	   ELSE ''USE [' + @DbName + ']; '' + state_desc + '' '' + permission_name + '' ON CERTIFICATE::['' 
		 + C.name + ''] TO [' + @NewLogin + '];'' COLLATE DATABASE_DEFAULT
	   END AS ''Permission''
	FROM [' + @DbName + '].sys.database_permissions AS P
	  JOIN [' + @DbName + '].sys.database_principals AS U
		ON P.grantee_principal_id = U.principal_id
	  JOIN [' + @DbName + '].sys.certificates AS C
		ON P.major_id = C.certificate_id
	WHERE class = 25
	  AND U.name = ''' + @LoginToClone + ''';';

		EXEC (@SQL)
	END;

	IF EXISTS (
			SELECT COUNT(SqlCommand)
			FROM #CloneDbPermissionScript
			HAVING COUNT(SqlCommand) < 2
			)
	BEGIN
		SET @SQL = '/' + '*' + '---- No Database Permissions To Clone' + '*' + '/';;

		INSERT INTO #CloneDbPermissionScript (SqlCommand)
		SELECT @SQL
	END;

	SET @SQL = '/' + '*' + 'END: CLONE DATABASE PERMISSIONS' + '*' + '/';

	INSERT INTO #CloneDbPermissionScript (SqlCommand)
	SELECT @SQL;

	SELECT SqlCommand
	FROM #CloneDbPermissionScript;

	DROP TABLE #CloneDbPermissionScript;
END;
GO

USE [DATABASE_PLACEHOLDER_NAME];
GO

IF OBJECT_ID('dbo.CloneLoginAndAllDBPerms') IS NULL
	EXEC ('CREATE PROCEDURE [dbo].[CloneLoginAndAllDBPerms] AS SELECT 1')
GO

ALTER PROC dbo.CloneLoginAndAllDBPerms @NewLogin SYSNAME
	,@NewLoginPwd NVARCHAR(MAX)
	,@WindowsLogin CHAR(1)
	,@LoginToClone SYSNAME
	,@DatabaseName SYSNAME = NULL
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #CloneLoginAndAllDBPermsScript (SqlCommand NVARCHAR(MAX));

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.CloneLogin @NewLogin = @NewLogin
		,@NewLoginPwd = @NewLoginPwd
		,@WindowsLogin = @WindowsLogin
		,@LoginToClone = @LoginToClone;

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.CreateUserInDB @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.GrantUserRoleMembership @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	INSERT INTO #CloneLoginAndAllDBPermsScript
	EXEC DATABASE_PLACEHOLDER_NAME.dbo.CloneDBPerms @NewLogin = @NewLogin
		,@LoginToClone = @LoginToClone
		,@DatabaseName = @DatabaseName;

	SELECT SqlCommand
	FROM #CloneLoginAndAllDBPermsScript

	DROP TABLE #CloneLoginAndAllDBPermsScript
END;
GO

How to assess a SQL Server instance for GDPR compliance by writing every table and column to Excel

March 16, 2018Data, Database Administrator, Excel, How To, Project Management, Reporting, SQL Server, T-SQLall columns, All columns SQL Server Instance, all tables, All tables SQL Server Instance, assess, compliance, GDPR, guide, How To, howto, instance, lesson, SQL Server, tutorialTidbytez 1

This post uses the script I had written before here LINK modified slightly to include a count of each table. If you are a DBA you’re likely assisting compliance offers to find personal identifiable data within the databases at this time. This script will allow you to provide them with a record of every database, table and column on an entire instance. The compliance offer can then sieve through all the columns and highlight any columns that look like they contain personal data for further investigation.

Below is a SQL query that will return the following metadata about each table from each database located on a SQL server database server:

ServerName
DatabaseName
SchemaName
TableName
CountOfRows
ColumnName
KeyType

The output is provided in a table format with these additional formatted lines of text which can be used as queries or as part of queries.

A Select table query
A Count table row columns
A Select column query
Each column bracketed
Each table and column bracketed

The table returned by the query can be exported to excel. Using excels filter option applied to the columns of the table makes finding and selecting specific tables and columns very easy.

This process can be repeated for every SQL Server instance used by the business to generate a single mapped servers master excel file allowing the user to find any table or column available to the organization quickly.

Applying some colour coding like below adds to the ease of use.

Image of excel file with mapped database server structure

How to use:

Simply open SQL Server Management Studio and from object explorer right click on the server name and select new query. This will open a window set to the master database of the server. Copy and paste the SQL below into this SQL Server window and execute. When the query is finished you will have created the table above.

/*
SCRIPT UPDATED
20180316
*/

USE [master]
GO

/*DROP TEMP TABLES IF THEY EXIST*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;
	
IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

DECLARE @ServerName AS SYSNAME
DECLARE @Count INT

SET @ServerName = @@SERVERNAME

CREATE TABLE #DatabaseList (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,ServerName SYSNAME
	,DbName SYSNAME
	);

CREATE TABLE [#TableStructure] (
	[DbName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[ColumnName] SYSNAME
	,[KeyType] CHAR(7)
	) ON [PRIMARY];
	
CREATE TABLE [#TableCount] (
	[Id_TableCount] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,[ServerName] SYSNAME
	,[DatabaseName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[SQLCommand] VARCHAR(MAX)
	,[TableCount] INT
	);

CREATE TABLE #Count (ReturnedCount INT);

/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*
A LIST OF DISTINCT DATABASE NAMES IS CREATED
THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE
THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME
*/
INSERT INTO #DatabaseList (
	ServerName
	,DbName
	)
SELECT @ServerName
	,NAME AS DbName
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

/*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/
DECLARE @sqlCommand AS VARCHAR(MAX)
DECLARE @DbName AS SYSNAME
DECLARE @i AS INT
DECLARE @z AS INT

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #DatabaseList
		)

/*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/
WHILE @i < @z
BEGIN
	/*GET NEW DATABASE NAME*/
	SET @DbName = (
			SELECT [DbName]
			FROM #DatabaseList
			WHERE Id = @i
			)
	/*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/
	SET @sqlCommand = 'USE [' + @DbName + '];' + '

INSERT INTO [#TableStructure]
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,T.NAME AS TableName	
	,C.NAME AS ColumnName
	,CASE 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 
			THEN ''Primary'' 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1 
			THEN ''Foreign''
		ELSE NULL 
		END AS ''KeyType''
FROM SYS.TABLES AS t WITH (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK) 
ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA 
	AND T.NAME = iskcu.TABLE_NAME
	AND C.NAME = iskcu.COLUMN_NAME
ORDER BY SchemaName ASC
	,TableName ASC
	,ColumnName ASC;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @i = @i + 1
END

/*
JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER
ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER
*/
SELECT DISTINCT @@SERVERNAME AS ServerName
	,DL.DbName
	,TS.SchemaName
	,TS.TableName
	,TS.ColumnName
	,TS.[KeyType]
	,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
	,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
	,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
	,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
	,'SELECT COUNT(*) FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + 'WITH (NOLOCK)' AS [PerformTableCount]
INTO #MappedServer
FROM [#DatabaseList] AS DL
INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName
ORDER BY DL.DbName ASC
	,TS.SchemaName ASC
	,TS.TableName ASC
	,TS.ColumnName ASC

/*
HOUSE KEEPING
*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;
	
INSERT INTO #TableCount (
	[ServerName]
	,[DatabaseName]
	,[SchemaName]
	,[TableName]
	,[SQLCommand]
	)
SELECT DISTINCT [ServerName]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[PerformTableCount]
FROM #MappedServer
ORDER BY [ServerName] ASC
	,[DbName] ASC
	,[SchemaName] ASC
	,[TableName] ASC

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #TableCount
		)
	
WHILE @i < @z
BEGIN
	SET @SQLCommand = (
			SELECT SQLCommand
			FROM #TableCount
			WHERE Id_TableCount = @i
			)

	--ERROR HANDLING
	BEGIN TRY
		INSERT INTO #Count
		EXEC (@SqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @Count = (
			SELECT ReturnedCount
			FROM #Count
			)

	TRUNCATE TABLE #Count

	UPDATE #TableCount
	SET TableCount = @Count
	WHERE Id_TableCount = @i;

	SET @i = @i + 1
END

SELECT *
FROM #ErrorTable;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

/*
THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL
USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
*/
SELECT DISTINCT ms.ServerName
	,ms.DbName
	,ms.SchemaName
	,ms.TableName
	,ts.TableCount AS CountOfRows
	,ms.ColumnName
	,ms.KeyType
	,ms.BracketedColumn
	,ms.BracketedTableAndColumn
	,ms.SelectColumn
	,ms.SelectTable
FROM #MappedServer AS ms
LEFT JOIN #TableCount AS ts ON ms.ServerName = ts.ServerName
AND ms.DbName = ts.DatabaseName
AND ms.SchemaName = ts.SchemaName
AND ms.TableName = ts.TableName
ORDER BY ms.DbName ASC
	,ms.SchemaName ASC
	,ms.TableName ASC
	,ms.ColumnName ASC;
	
IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;

IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

How to get the default error log path for SQL Server with T-SQL

February 21, 2018Database Administrator, dba, Development, sql, SQL Server, T-SQL, tsqlerror, GET, guide, How To, howto, lesson, log, path, SQL Server, tutorial, variableTidbytez 1

Below is a script to get the default error log path for SQL Server and set it as a variable.

USE MASTER;
GO

DECLARE @LogPath AS VARCHAR(MAX)
DECLARE @ErrorLogPath TABLE (
	LogDate DATETIME
	,ProcessInfo VARCHAR(255)
	,PathText VARCHAR(MAX)
	);

INSERT INTO @ErrorLogPath
EXEC xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';

SET @LogPath = (
		SELECT REPLACE(REPLACE(REPLACE(PathText, 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG.', '')
		FROM @ErrorLogPath
		);

SELECT @LogPath AS DefaultLogPath;
GO

How to copy a large result set from SQL Server Management Studio to Excel

January 16, 2018Data, Database Administrator, dba, Excel, How To, Reporting, SQL Server, T-SQLcopy, data set, Excel, Execption of type 'System.OutOfMemoryException' was thrown, from, guide, How To, howto, lesson, management studio, ntile, paste, Query, results, section, segment, split, SQL Server, ssms, tutorialTidbytez 1

So you’ve tried copying and pasting the results of a query into an excel file only to get the out of memory exception. Now the fun starts!

There are multiple options to achieve copying data from SQL Server Management Studio to excel however most of them are a pain.

You’ve tried the save as option but the csv and text files have jumbled up content. You could use the Management Studio export function, but this is intended for physical tables not results sets and you might not have the permissions to create tables in the environment. You could use the bcp Utility but you’ve probably read leaving this option turned on represents a security risk. You could create an SSIS package . . . yeah that’s an efficient and effective option when you just want the damn results of an ad hoc query!!!

I’d suggest splitting the result set into chunks. You can then copy and paste the chunks into the excel file without running out of memory. Sure it’s kinda manual but trust me it’ll take less time than the options above.

I’d wager you probably only need the result set split into two, so you’ve to copy and paste twice rather than once. Not that big a deal right? I’ve even provided some code below that will really move things along.

Start by writing your query results into a temporary tablet called #QueryResult, for example SELECT * INTO #QueryResult FROM TableName.

Then all you need to do is determine how many segments you need. NTILE(n) is a function that allocates your output into n segments, each of the same size (give or take rounding when the number of rows isn’t divisible by n).

So this produces an output like:

Id	Name	Ntile
1	Mickey	1
2	Leo	1
3	Raph	2
4	Donnie	2

Start by leaving n set to the default of 2. Once the data is written to the table #QueryResult run the code below in the same SSMS window the temp table was created in. Running the code should produce the same number of returned result sets as the n value you provided. Use a higher n number to create more segments if you still run out of memory when you try to copy and paste the first segment.

/*
Write your query results to a temp table here
i.e. SELECT * INTO #QueryResult FROM TableName
*/
DECLARE @n INT
DECLARE @i INT

/*
Set n to how many segments/results set returned you need
*/
SET @n = 2
SET @i = 1

SELECT *
	,NTILE(@n) OVER (
		ORDER BY RowNum
		) AS NtileGroup
INTO #Export
FROM (
	SELECT ROW_NUMBER() OVER (
			ORDER BY (
					SELECT NULL
					)
			) AS RowNum
		,*
	FROM #QueryResult
	) AS a

WHILE @i <= @n
BEGIN
	SELECT *
	FROM #Export
	WHERE NtileGroup = @i
	ORDER BY RowNum ASC

	SET @i = @i + 1
END

DROP TABLE #QueryResult

DROP TABLE #Export

So that’s it, you should now be able to copy and paste your results. Maybe someday in the future Microsoft will add the option of saving results directly to excel . . .

How to search for SQL Server objects that exist anywhere across an instance using T-SQL

December 27, 2017Database Administrator, dba, How To, sql, SQL Server, T-SQL, tsqlall, databases, definition, guide, How To, howto, instance, lesson, name, objects, referencing, search, search for objects, SQL Server, string, T-SQL, tutorial, uncategorisedTidbytez 1

You’re probably never going to be familiar with every database object, i.e. Table, View, Stored Procedure and Function, that exists in a large production database, even if you were the one who designed it. So everyone who maintains an environment be it a call center back end or sales system back end or CRM back end etc. etc. needs to be able to locate objects quickly.

Redgate offer a fantastic free tool to do this within SQL Server Management Studio through a GUI called SQL Search.

As great as this is though sometimes you might want to search through object definitions programmatically.

To clarify I’m defining object definition as being column names of a table or view or the command that makes up a stored procedure or function.

Below is the Store Procedure I’ve written to do this called SearchObjectDefinition. To work this Stored Procedure also requires the User Defined Function (UDF) called Split which I used in the tutorial “How to pass a multi-value parameter to a stored procedure from a SSRS Report“.

Below are a few use cases for SearchObjectDefinition:

--List All Instance Tables, Stored Procedures, Views and Functions
EXEC dbo.SearchObjectDefinition

--List All Stored Procedures, and Functions in the Databases 
--TestDatabaseOne and TestDatabaseTwo
EXEC dbo.SearchObjectDefinition @ObjectType = 'Sp, Fn'
	,@DatabaseName = 'TestDatabaseOne, TestDatabaseTwo'

--List All Instance Tables, Stored Procedures, Views and Functions 
--where Object Definition contains the word Insert
EXEC dbo.SearchObjectDefinition @strFind = 'insert'

--List All Instance Tables where Object Name is Customers and 
--Column name contains the word Phone
EXEC dbo.SearchObjectDefinition @ObjectType = 'tb'
	,@ObjectName = 'Customers'
	,@strFind = 'Phone'

As always be sure to deploy the following Function and Store Procedure in a utility database not the master database as this is bad practice.

Split Function:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[Split]') IS NULL
	EXEC ('CREATE FUNCTION dbo.[Split](@i INT) RETURNS @RtnValue TABLE (j INT) AS BEGIN INSERT INTO @RtnValue (j) SELECT 1 RETURN END');
GO

ALTER FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

SearchObjectDefinition Stored Procedure:

--USE [DatabaseName];
--GO

IF OBJECT_ID('[SearchObjectDefinition]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[SearchObjectDefinition] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[SearchObjectDefinition] (
	@ObjectType AS VARCHAR(20) = NULL
	,@ObjectName AS SYSNAME = NULL
	,@DatabaseName AS SYSNAME = NULL
	,@strFind AS VARCHAR(MAX) = NULL
	)
AS
BEGIN
	SET NOCOUNT ON;
	SET @strFind = ISNULL(@strFind, '')
	SET @ObjectName = ISNULL(@ObjectName, '')

	IF OBJECT_ID('tempdb..#Result') IS NOT NULL
		DROP TABLE #Result;

	DECLARE @DatabaseTable TABLE (DbName SYSNAME)
	DECLARE @DbName AS SYSNAME
	DECLARE @Sql AS VARCHAR(MAX)

	CREATE TABLE #Result (
		DbName SYSNAME NULL
		,ObjectType VARCHAR(2)
		,ObjectName SYSNAME
		,ObjectDefinition VARCHAR(MAX)
		)

	IF @DatabaseName IS NOT NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT Value
		FROM dbo.Split(@DatabaseName, ',')
	END

	IF @DatabaseName IS NULL
	BEGIN
		INSERT INTO @DatabaseTable (DbName)
		SELECT NAME
		FROM master.dbo.sysdatabases
		WHERE NAME NOT IN (
				'tempdb'
				,'master'
				,'msdb'
				,'model'
				)
		ORDER BY NAME ASC
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL PROCEDURES  
	IF @ObjectType LIKE '%Sp%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
			USE ' + QUOTENAME(@DbName) + ';
			
			INSERT INTO #Result (
				DbName
				,ObjectType
				,ObjectName
				,ObjectDefinition
				)
			SELECT ''' + @DbName + ''' AS DbName
				,''Sp'' AS ObjectType
				,OBJECT_NAME(OBJECT_ID) AS ObjectName
				,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
			FROM ' + QUOTENAME(@DbName) + '.sys.procedures
			WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL VIEWS   
	IF @ObjectType LIKE '%Vw%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Vw'' AS ObjectType
			,OBJECT_NAME(OBJECT_ID) AS ObjectName
			,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.VIEWS
		WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL FUNCTION 
	IF @ObjectType LIKE '%Fn%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
		
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Fn'' AS ObjectType
			,ROUTINE_NAME AS ObjectName
			,ROUTINE_DEFINITION AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_DEFINITION LIKE ''%'' + ''' + @strFind + ''' + ''%''
			AND ROUTINE_NAME LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
			AND ROUTINE_TYPE = ''FUNCTION''
		ORDER BY ROUTINE_NAME
			'

			EXEC (@Sql)
		END
	END

	SET @DbName = ''

	--TO FIND STRING IN ALL TABLES OF DATABASE.  
	IF @ObjectType LIKE '%Tb%'
		OR @ObjectType IS NULL
	BEGIN
		WHILE @DbName IS NOT NULL
		BEGIN
			SET @DbName = (
					SELECT MIN(DbName)
					FROM @DatabaseTable
					WHERE DbName > @DbName
					)
			SET @Sql = '
		USE ' + QUOTENAME(@DbName) + ';	
			
		INSERT INTO #Result (
			DbName
			,ObjectType
			,ObjectName
			,ObjectDefinition
			)
		SELECT ''' + @DbName + ''' AS DbName
			,''Tb'' AS ObjectType
			,t.NAME AS ObjectName
			,c.NAME AS ObjectDefinition
		FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
		INNER JOIN ' + QUOTENAME(@DbName) + '.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
		WHERE c.NAME LIKE ''%'' + ''' + @strFind + ''' + ''%''
		AND t.Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
		ORDER BY [ObjectDefinition] ASC
			'

			EXEC (@Sql)
		END
	END

	SELECT DbName
		,ObjectType
		,ObjectName
		,ObjectDefinition
	FROM #Result
	ORDER BY DbName ASC
		,ObjectType ASC
		,ObjectName ASC

	DROP TABLE #Result
END

How to solve the SQL Server error ‘String or binary data would be truncated’

October 3, 2017Database Administrator, dba, Development, How To, Reporting, SQL Server, T-SQLbinary, correct, Data, fix, guide, How To, howto, lesson, solve, SQL Server, string, String or binary data would be truncated, truncated, tutorial, warningTidbytez 1

The ‘String or binary data would be truncated’ error will occur if an insert or update statement is trying to put too many characters into a field, defined in a table, which has been assigned too few character spaces. For example trying to write an email address with 255 characters into a table where the column email has been assigned 40 characters.

The easy fix is assign more characters to the column or columns you have determined are experiencing the problem. The more complicated but potentially necessary fix might be to change the logic or introduce validation at the source of data entry.

Finding the columns experiencing the problems however can be time consuming.

( . . . without the little script below of course)

SQL Server will kindly direct you to the stored procedure or insert/update statement that is experiencing the problem. However it will not pin point the exact column or columns that cannot be written to. The pain then is determining where the data won’t fit.

To speed things up take the entire query or query section you know to be causing the problem and write the results it into a temp table called #temp, i.e. SELECT * INTO #temp FROM SomeTable

Once the data has been written to the temp table #temp run the scrip below in the same window.

DECLARE @sql VARCHAR(MAX)

SET @sql = (
		SELECT (
				SELECT ',MAX(LEN(' + NAME + ')) AS [' + NAME + ']'
				FROM tempdb.sys.columns
				WHERE object_id = object_id('tempdb..#temp')
				FOR XML PATH('')
				)
		)
SET @sql = 'SELECT ' + RIGHT(@sql, LEN(@sql) - 1) + ' FROM #temp'

EXEC (@sql)

This will output results giving you the max character length of each field.

You can then compare these results to the defined destination table that the data could not be written to.

The source of the error will be where the max character number is greater than the assigned character spaces on the destination table.

For example the last time I used this query it easily highlighted that an agent had written a customers full address to the county name field which had a limit of 30 characters.

How to use a while loop to iterate through each table of each database within an instance

August 22, 2017Database Administrator, dba, Development, SQL Server, T-SQL, tsqlcreate list of databases, each database, each table, entire instance, every table on instance, guide, How To, how to execute code against every table, how to list SQL Server Databases, howto, iterate, lesson, list all, list all databases, list databases, LOOP, SQL Server, tutorial, whileTidbytez 1

Say you have code you want executed against every table on a SQL Server instance, you could use SQL Server’s inbuilt sp_MSForEachDB and sp_MSForEachTable. I’m not a big fan of them though because they are undocumented, so I’d always be concerned Microsoft might decide to kill it with any given patch or service pack update. (I know the likelihood of that is extremely low but I’m a risk adverse kinda guy)

I prefer to use the example below. It may not be the most efficient snippet of code available on the net but it’s good and simple and it’s not going anywhere unless I drop it.

SET NOCOUNT ON

DECLARE @Database TABLE (DbName SYSNAME)
DECLARE @DbName AS SYSNAME

SET @DbName = ''

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases
WHERE NAME <> 'tempdb'
AND state_desc = 'ONLINE'
ORDER BY NAME ASC

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)

	/*
	PUT CODE HERE
	EXAMPLE PRINT Database Name
	*/
	PRINT @DbName
END

How to archive tables that contain a keyword from one database to another using T-Sql

June 2, 2017Data, Database Administrator, dba, Development, How To, SQL Server, T-SQL, tsqlarchive, contain, guide, How To, howto, keyword, lesson, SQL Server, T-SQL, tables, tsql, tutorial, unwantedTidbytez 1

In the previous article I wrote about how to identify and remove unwanted tables. Link

In that tutorial I suggested prefixing the tables you want to remove with _DELETE_.

I also suggested you may want to archive the tables in some manner before you delete them. This could be because there is a chance someone might come looking for the data that was in a deleted table or something might break by removing the table and you might want to put it back asap.

If you have only a few tables you could script the tables out but if you have a lot of tables that becomes a little unmanageable. Also if the tables total in size to 10 Gb the script to recreate the tables will be a lot lot larger.

An alternative method is to create an Archive database, copy the tables across to this target database and then delete the tables in the source database. You can then backup and drop the Archive database saving the .bak file somewhere cheaper.

The script below will allow you to do just that. It prints the T-Sql to do the job, it doesn’t carry out the job, so it’s completely safe to execute and review.

To use the script below create a target database.

Use the target database name for the variable value @TargetDb

Use the source database name for the variable value @SourceDb

The @KeyWord variable is used to gather all the tables that contain the string of choice, in the example below _DELETE_.

The @RemoveKeyWord variable is a flag that will remove the keyword string from the target database table name, e.g. _DELETE_Sales will become Sales.

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
	DROP TABLE #Table
GO

DECLARE @RemoveKeyWord BIT
DECLARE @Id AS INT
DECLARE @KeyWord AS VARCHAR(256)
DECLARE @SourceDb AS SYSNAME
DECLARE @TargetDb AS SYSNAME
DECLARE @TableName AS SYSNAME
DECLARE @SchemaName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @RemoveKeyWord = 1
SET @KeyWord = '_DELETE_'
SET @TargetDb = 'Archive'
SET @SourceDb = 'Source'
SET NOCOUNT ON

CREATE TABLE #Table (
	Id_Table INT IDENTITY(1, 1)
	,SchemaName SYSNAME
	,TableName SYSNAME
	);

SET @Sql = '
INSERT INTO #Table (
	SchemaName
	,TableName
	)
SELECT s.NAME
	,so.NAME
FROM ' + QUOTENAME(@SourceDb) + '.sys.tables AS so
LEFT JOIN ' + QUOTENAME(@SourceDb) + '.sys.schemas AS s ON so.schema_id = s.schema_id
WHERE so.NAME LIKE ' + '''' + '%' + @KeyWord + '%' + '''' + '
ORDER BY s.NAME ASC'

EXEC (@Sql)

SET @SchemaName = ''

WHILE @SchemaName IS NOT NULL
BEGIN
	SET @SchemaName = (
			SELECT MIN(SchemaName)
			FROM #Table
			WHERE SchemaName > @SchemaName
				AND SchemaName <> 'dbo'
			)

	PRINT 'USE ' + QUOTENAME(@TargetDb) + ';
GO
	
IF NOT EXISTS (SELECT * FROM ' + QUOTENAME(@TargetDb) + '.sys.schemas WHERE name = ' + '''' + @SchemaName + '''' + ')
  BEGIN
    EXEC (' + '''' + 'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + ';' + '''' + ');
  END;
  
'
END

SET @Id = 1

WHILE @Id IS NOT NULL
BEGIN
	SELECT @TableName = TableName
		,@SchemaName = SchemaName
	FROM #Table
	WHERE Id_Table = @Id

	IF @RemoveKeyWord = 1
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(REPLACE(@TableName, @KeyWord, '')) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END
	ELSE
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END

	SET @Id = (
			SELECT MIN(Id_Table)
			FROM #Table
			WHERE Id_Table > @Id
			)
END

How to identify and remove unused tables in SQL Server with T-Sql

May 22, 2017Data, Database Administrator, dba, Development, Excel, How To, Reporting, SQL Server, T-SQL, tsqlclean, clear, empty tables, find, find empty tables, get rid of, guide, How To, howto, lesson, obsolete, remove, server cleanup, sql query to find, SQL Server, tables, tutorial, unused, unwantedTidbytez 1

In a perfect world an organisation should never get itself into the situation where tables need to be identified as functionally obsolete and removed in bulk. Schema changes should be stepped through the cycle of development, test, staging and live with developers cleaning as they go, but we don’t live in a perfect world as you may have noticed.

What do I mean by functionally obsolete? This means the tables are no longer being interacted with by user generated objects like stored procedures, functions or views. There may also be tables that could be classified as business redundant. That is they are being referenced by user generated objects frequently but they no longer have a use to the business, i.e. one job may have been replaced by another without the former being disabled or dropped. Business redundant objects are more difficult to determine and finding them may require input from multiple stakeholders.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no impact on the overall environment script out the object then drop it. (Obviously do this in a test environment first if possible)

The script below is mostly a light weight SELECT statement that can be run on any environment. It does not execute any of the code it generates. It uses the sys.dm_db_index_usage_stats dynamic management view to determine when the tables were last interacted with. Interaction being defined as the following actions being applied against the object, update, seek, scan, lookup.

Caveat: Entries in this view reset to NULL after a Server reboot. Also the DMV has been known to be a bit unreliable with earlier versions of SQL Server with cases of the view being reset when a full index rebuild is carried out against a table. So don’t execute any code without first reviewing it.

Tables with NULL values for the fields below should be tables that have not been referenced at all or at least since the last time the server was rebooted.

LastUserUpdate
LastUserSeek
LastUserScan
LastUserLookup

Removing these objects is the low hanging fruit of cleaning up an environment. The script also provides stats on how many times these interactions happened and the size of the object. All these stats together should help you determine if an object is functionally redundant or business redundant and can be removed.

To aid further in the cleanup the script also creates the fields Action, Comments, Renamed, RenamedDate, RenameForDeletion, RestoreOriginalName, DropTable, DroppedDate.

The script output can then be copied and pasted into an Excel spread sheet and used to coordinate and track the cleanup progress.

SET NOCOUNT ON

DECLARE @Database TABLE ([DbName] [sysname])
DECLARE @DbName AS [sysname]
DECLARE @Sql AS [varchar] (max)

IF OBJECT_ID('tempdb..#TableStats', 'U') IS NOT NULL
	DROP TABLE #TableStats

IF OBJECT_ID('tempdb..#IndexStats', 'U') IS NOT NULL
	DROP TABLE #IndexStats

IF OBJECT_ID('tempdb..#TableUsageStats', 'U') IS NOT NULL
	DROP TABLE #TableUsageStats

IF OBJECT_ID('tempdb..#TableSizeStats', 'U') IS NOT NULL
	DROP TABLE #TableSizeStats

CREATE TABLE #TableStats (
	[DbName] [sysname]
	,[SchemaName] [sysname]
	,[ObjectId] [bigint]
	,[TableName] [sysname]
	,[ModifiedDate] [datetime]
	);

CREATE TABLE #IndexStats (
	[DbName] [sysname]
	,[ObjectId] [bigint]
	,[HasIndex] [bit]
	);

CREATE TABLE #TableSizeStats (
	[DbName] [varchar](255) NULL
	,[SchemaName] [varchar](255) NULL
	,[ObjectId] [bigint]
	,[TableName] [varchar](255) NULL
	,[RowCount] [bigint] NULL
	,[AvailableSpacePercentage] [numeric](6, 2) NULL
	,[UnusedSpaceGb] [numeric](10, 3) NULL
	,[UsedSpaceGb] [numeric](10, 3) NULL
	,[TotalSpaceGb] [numeric](10, 3) NULL
	,[UnusedSpaceMb] [numeric](13, 3) NULL
	,[UsedSpaceMb] [numeric](13, 3) NULL
	,[TotalSpaceMb] [numeric](13, 3) NULL
	,[UnusedSpaceKb] [bigint] NULL
	,[UsedSpaceKb] [bigint] NULL
	,[TotalSpaceKb] [bigint] NULL
	)

CREATE TABLE #TableUsageStats (
	[DbName] [sysname]
	,[ObjectId] [bigint]
	,[TableName] [nvarchar](128) NULL
	,[LastUserUpdate] [datetime] NULL
	,[LastUserSeek] [datetime] NULL
	,[LastUserScan] [datetime] NULL
	,[LastUserLookup] [datetime] NULL
	,[UserUpdateCount] [bigint] NOT NULL
	,[UserSeekCount] [bigint] NOT NULL
	,[UserScanCount] [bigint] NOT NULL
	,[UserLookupCount] [bigint] NOT NULL
	);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases 
WHERE NAME NOT IN (
		'tempdb'
		,'master'
		,'mode'
		,'model'
		)
AND state_desc = 'ONLINE'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);
	SET @Sql = '
INSERT INTO #TableStats (
	DbName
	,schemaName
	,ObjectId
	,TableName
	,ModifiedDate
	)
SELECT DbName
	,SchemaName
	,ObjectId
	,TableName
	,ModifiedDate
FROM (
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,s.NAME AS SchemaName
	,t.object_id AS ObjectId
	,t.NAME AS TableName
	,t.modify_date AS ModifiedDate
FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON t.schema_id = s.schema_id
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.extended_properties AS ep ON ep.major_id = t.[object_id] /*Exclude System Tables*/
WHERE t.NAME IS NOT NULL
	AND s.NAME IS NOT NULL
	AND (ep.[name] IS NULL OR ep.[name] <> ''microsoft_database_tools_support'')
	) AS rd
WHERE rd.SchemaName IS NOT NULL
ORDER BY DbName ASC
	,TableName ASC;
'

	EXEC (@Sql)

	SELECT @Sql = '
INSERT INTO #IndexStats (
	DbName
	,ObjectId
	,HasIndex
	)
SELECT ' + '''' + @DbName + '''' + ' AS DbName
	,OBJECT_ID AS ObjectId
	,IndexCheck AS HasIndex
FROM (
	SELECT DISTINCT OBJECT_ID
		,CASE 
			WHEN (
					[TYPE] > 0
					AND is_disabled = 0
					)
				THEN 1
			ELSE 0
			END AS IndexCheck
	FROM ' + QUOTENAME(@DbName) + '.sys.indexes
	) AS rd
WHERE rd.IndexCheck = 1
'

	EXEC (@Sql)

	SET @Sql = 
		'
INSERT INTO #TableSizeStats (
	[DbName]
	,[SchemaName]
	,[ObjectId]
	,[TableName]
	,[RowCount]
	,[AvailableSpacePercentage]
	,[UnusedSpaceGb]
	,[UsedSpaceGb]
	,[TotalSpaceGb]
	,[UnusedSpaceMb]
	,[UsedSpaceMb]
	,[TotalSpaceMb]
	,[UnusedSpaceKb]
	,[UsedSpaceKb]
	,[TotalSpaceKb]
	)
SELECT DISTINCT rd.[DbName]
	,rd.[SchemaName]
	,rd.[ObjectId]
	,rd.[TableName]
	,rd.[RowCount]
	,CASE 
		WHEN TotalSpaceKb > 0
			THEN ((UnusedSpaceKb / TotalSpaceKb) * 100)
		ELSE 0
		END AS AvailableSpacePercentage
	,CONVERT(NUMERIC(10, 3), (rd.[UnusedSpaceKb] / 1024.) / 1024.) AS UnusedSpaceGb
	,CONVERT(NUMERIC(10, 3), (rd.[UsedSpaceKb] / 1024.) / 1024.) AS UsedSpaceGb
	,CONVERT(NUMERIC(10, 3), (rd.[TotalSpaceKb] / 1024.) / 1024.) AS TotalSpaceGb
	,CONVERT(NUMERIC(13, 3), (rd.[UnusedSpaceKb] / 1024.)) AS UnusedSpaceMb
	,CONVERT(NUMERIC(13, 3), (rd.[UsedSpaceKb] / 1024.)) AS UsedSpaceMb
	,CONVERT(NUMERIC(13, 3), (rd.[TotalSpaceKb] / 1024.)) AS TotalSpaceMb
	,rd.[UnusedSpaceKb]
	,rd.[UsedSpaceKb]
	,rd.[TotalSpaceKb]
FROM (
	SELECT ' 
		+ '''' + @DbName + '''' + ' AS DbName
		,t.Object_id AS ObjectId
		,s.[name] AS [SchemaName]
		,t.[name] AS [TableName]
		,p.[rows] AS [RowCount]
		,SUM(a.[used_pages]) * 8 AS [UsedSpaceKb]
		,(SUM(a.[total_pages]) - SUM(a.[used_pages])) * 8 AS [UnusedSpaceKb]
		,SUM(a.[total_pages]) * 8 AS [TotalSpaceKb]
	FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON t.schema_id = s.schema_id
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.indexes AS i ON t.OBJECT_ID = i.object_id
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.partitions AS p ON i.object_id = p.OBJECT_ID
		AND i.[index_id] = p.[index_id]
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.allocation_units a ON p.[partition_id] = a.[container_id]
	WHERE t.[is_ms_shipped] = 0
		AND i.OBJECT_ID > 255
	GROUP BY s.[name]
		,t.[name]
		,t.[object_id]
		,p.[rows]
	) AS rd
ORDER BY DbName ASC
	,SchemaName ASC
	,TableName ASC;
'

	EXEC (@Sql)

	SET @Sql = '
INSERT INTO #TableUsageStats (
	[DbName]
	,[ObjectId]
	,[TableName]
	,[LastUserUpdate]
	,[LastUserSeek]
	,[LastUserScan]
	,[LastUserLookup]
	,[UserUpdateCount]
	,[UserSeekCount]
	,[UserScanCount]
	,[UserLookupCount]
	)
SELECT DbName
	,ObjectId
	,TableName
	,LastUserUpdate
	,LastUserSeek
	,LastUserScan
	,LastUserLookup
	,UserUpdateCount
	,UserSeekCount
	,UserScanCount
	,UserLookupCount
FROM (
	SELECT DISTINCT ROW_NUMBER() OVER (
			PARTITION BY ius.Object_Id ORDER BY last_user_update DESC
			) AS RN
		,' + '''' + @DbName + '''' + ' AS DbName
		,ius.OBJECT_ID AS ObjectId
		,o.NAME AS TableName
		,ius.last_user_update AS LastUserUpdate
		,ius.last_user_seek AS LastUserSeek
		,ius.last_user_scan AS LastUserScan
		,ius.last_user_lookup AS LastUserLookup
		,ius.user_updates AS UserUpdateCount
		,ius.user_seeks AS UserSeekCount
		,ius.user_scans AS UserScanCount
		,ius.user_lookups AS UserLookupCount
	FROM ' + QUOTENAME(@DbName) + 
		'.sys.dm_db_index_usage_stats AS ius
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.objects AS o ON ius.OBJECT_ID = o.OBJECT_ID
		AND o.NAME IS NOT NULL
	) AS rd
WHERE rd.RN = 1
ORDER BY rd.DbName ASC
	,rd.TableName ASC
	,rd.LastUserUpdate DESC
	,rd.LastUserSeek DESC
	,rd.LastUserScan DESC
	,rd.LastUserLookup DESC;
'

	EXEC (@Sql)
END;
GO

SELECT DISTINCT ROW_NUMBER() OVER (
		ORDER BY ts.[DbName] ASC
			,ts.[SchemaName] ASC
			,ts.[TableName] ASC
		) AS Row
	,ts.[DbName]
	,ts.[SchemaName]
	,ts.[TableName]
	,'' AS Action --Rename, keep etc.
	,'' AS Comments
	,'' AS Renamed --boolean flag
	,'' AS RenamedDate 
	,i.[HasIndex] --Tables without an index are heaps
	,tss.[TotalSpaceMb] AS TableSizeInMb
	,ts.[ModifiedDate]
	,tus.[LastUserUpdate]
	,tus.[LastUserSeek]
	,tus.[LastUserScan]
	,tus.[LastUserLookup]
	,tus.[UserUpdateCount]
	,tus.[UserSeekCount]
	,tus.[UserScanCount]
	,tus.[UserLookupCount]
	,tss.[AvailableSpacePercentage]
	,tss.[UnusedSpaceGb]
	,tss.[UsedSpaceGb]
	,tss.[TotalSpaceGb]
	,tss.[UnusedSpaceMb]
	,tss.[UsedSpaceMb]
	,tss.[TotalSpaceMb]
	,tss.[UnusedSpaceKb]
	,tss.[UsedSpaceKb]
	,tss.[TotalSpaceKb]
	,'USE ' + QUOTENAME(ts.[DbName]) + '; EXEC sp_rename ' + '''' + ts.[SchemaName] + '.' + ts.[TableName] + '''' + ', ' + '''' + '_DELETE_' + ts.[TableName] + '''' + ';' AS RenameForDeletion
	,'USE ' + QUOTENAME(ts.[DbName]) + '; EXEC sp_rename ' + '''' + ts.[SchemaName] + '.' + '_DELETE_' + ts.[TableName] + '''' + ', ' + '''' + ts.[TableName] + '''' + ';' AS RestoreOriginalName
	,'USE ' + QUOTENAME(ts.[DbName]) + '; DROP TABLE ' + QUOTENAME(ts.[SchemaName]) + '.' + '[' + '_DELETE_' + ts.[TableName] + ']' + ';' AS 'DropTable'
	,'' AS DroppedDate
FROM #TableStats AS ts
LEFT JOIN #TableSizeStats AS tss ON ts.ObjectId = tss.ObjectId
	AND tss.DbName = ts.DbName
LEFT JOIN #IndexStats AS i ON ts.ObjectId = i.ObjectId
	AND i.DbName = ts.DbName
LEFT JOIN #TableUsageStats AS tus ON ts.ObjectId = tus.ObjectId
	AND tus.DbName = ts.DbName
ORDER BY ts.[DbName] ASC
	,ts.[SchemaName] ASC
	,ts.[TableName] ASC

	-- REF: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

How to disable all enabled user job schedules in SQL Server with T-Sql

April 26, 2017Database Administrator, dba, How To, sql, SQL Server, System, T-SQL, tsqlall, disable, enabled, guide, How To, howto, Job, lesson, schedules, SQL Server, tutorial, with t-sqlTidbytez 1

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to test on a development environment.

Running the below script will output the T-Sql required to disable all enabled job schedules on the SQL Server instance. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

SET NOCOUNT ON

DECLARE @Schedule TABLE (ScheduleId INT)
DECLARE @ScheduleId AS INT
DECLARE @Sql AS VARCHAR(MAX)

SET @ScheduleId = ''

INSERT INTO @Schedule (ScheduleId)
SELECT s.schedule_id
FROM msdb.dbo.sysschedules AS s
WHERE s.[enabled] = 1
	AND s.owner_sid <> 0x01
ORDER BY s.NAME ASC

WHILE @ScheduleId IS NOT NULL
BEGIN
	SET @ScheduleId = (
			SELECT MIN(ScheduleId)
			FROM @Schedule
			WHERE ScheduleId > @ScheduleId
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_schedule @schedule_id = ' + '''' + CONVERT(VARCHAR(5), @ScheduleId) + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: