Tag Archives: drop

How to rename and/or remove tables in SQL Server with T-Sql generated by Excel formulas

This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)

Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.

With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.

To do this you can just download the Excel file template here. Download

Rename And Drop Script Generator

The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.

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 negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)

To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.

You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.

Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.

You can create the Excel file manually yourself without downloading it.

To do so open a new Excel file and in an empty sheet name the first 9 columns as below:

A1: DatabaseName
B1: SchemaName
C1: TableName
D1: RenameForDeletion
E1: RestoreOriginalName
F1: DropTable
G1: RenameDate
H1: RestoreDate
I1: DropDate

For D2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For E2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For F2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”

To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.

CREATE DATABASE [TidBytez];
GO

USE [TidBytez]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]
GO

 

How to remove Logins and Users from SQL Server en masse

The script below will create SQL code to drop every user and login from a SQL Server instance. Not very useful unless you’re trying to give someone, possibly yourself, a very bad day. Don’t worry though the script does not execute the code, to reiterate it only generates it. However given the potential for you using the output of the script incorrectly this post comes with a disclaimer (Link to disclaimer).

To make the script a little more useful you can populate a temp table called #TheseUsersOnly in the script with the specific login/user names you want to remove.

This will limit the code outputted to only the logins and users specified.

Go to the /*INSERT LOGINS HERE*/ section to populate the temp table with hard coded login/user names.

Be sure to thoroughly review the code outputted before executing it.

If you encounter this error message “The database principal owns a schema in the database, and cannot be dropped“, see this post (Link).

SET NOCOUNT ON;

/*DECLARE VARIABLES*/
DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sqlCommand AS VARCHAR(MAX);
DECLARE @UserName AS VARCHAR(128);
DECLARE @i AS INT;
DECLARE @z AS INT;
DECLARE @j AS INT;
DECLARE @y AS INT;

/*DROP EXISTING TEMP TABLES*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
	DROP TABLE #ErrorTable
END;

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
	DROP TABLE #UserOrdered
END;

IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
	DROP TABLE #TheseUsersOnly
END;

IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
	DROP TABLE #Return
END;

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

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

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

CREATE TABLE #TheseUsersOnly (UserName SYSNAME NULL);

CREATE TABLE [#Return] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*INSERT LOGINS HERE*/
/*
/*Single Login*/
INSERT INTO #TheseUsersOnly (UserName)
SELECT 'Test_Login';
*/
/*
/*Multiple Logins*/
INSERT INTO #TheseUsersOnly (UserName)
VALUES (Test_Login_1)
,(Test_Login_2)
,(Test_Login_3)
*/
SET @DbName = '';

/*GENERATE LIST TABLE OF DATABASE NAMES*/
INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;

/*GENERATE LIST OF USERS FOR EACH DATABASE*/
WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)
	SET @sqlCommand = '
INSERT INTO #User (
DbName
,UserName
)
SELECT ' + '''' + @DbName + '''' + ' AS DbName 
,princ.name AS UserName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals AS princ
WHERE princ.name IS NOT NULL
';

	/*OPTION TO LIMIT USERS TO ONLY THE USERS SPECIFIED*/
	IF EXISTS (
			SELECT *
			FROM #TheseUsersOnly
			)
	BEGIN
		SET @sqlCommand = @sqlCommand + 'AND princ.Name IN (SELECT UserName FROM #TheseUsersOnly);'
	END
	ELSE
	BEGIN
		SET @sqlCommand = @sqlCommand + ';'
	END

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

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

/*REORDER USER TABLE BY NAME ALPHABETICALLY ASCENDING*/
INSERT INTO #UserOrdered (
	DbName
	,UserName
	)
SELECT DbName
	,UserName
FROM #User
ORDER BY UserName ASC
	,DbName ASC;

/*SET LOOP VARIABLES*/
SET @y = 0;
SET @i = 1;
SET @z = (
		SELECT COUNT(*)
		FROM #UserOrdered
		);

/*LOOP TO GENERATE SQL CODE*/
WHILE @i <= @z
BEGIN
	SELECT @DbName = DbName
		,@UserName = UserName
	FROM #UserOrdered
	WHERE Id = @i

	IF @y = 0
		SET @j = 1;

	BEGIN
		SET @y = (
				SELECT COUNT(UserName)
				FROM #UserOrdered
				WHERE UserName = @UserName
				)
	END

	SET @sqlCommand = 'USE [' + @DbName + '];
IF EXISTS (SELECT * FROM ' + QUOTENAME(@DbName) + '.sys.database_principals WHERE name = N' + '''' + @UserName + '''' + ')
BEGIN DROP USER [' + @UserName + '] END;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		INSERT INTO #Return
		SELECT (@sqlCommand)
	END TRY

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

	IF @j = @y
	BEGIN
		IF EXISTS (
				SELECT NAME
				FROM master.sys.server_principals
				WHERE NAME = @UserName
				)
		BEGIN
			INSERT INTO #Return (SqlCommand)
			SELECT 'USE [master];
IF EXISTS (SELECT NAME
FROM master.sys.server_principals
WHERE NAME = ' + '''' + @UserName + '''' + '
) BEGIN
DROP LOGIN [' + @UserName + '] END;'
		END

		SET @y = 0;
	END

	SET @i = @i + 1;
	SET @j = @j + 1;
END

/*RESULTS*/
SELECT SqlCommand
FROM #ErrorTable;

SELECT SqlCommand
FROM #Return;

/*HOUSEKEEPING*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
	DROP TABLE #ErrorTable
END;

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
	DROP TABLE #UserOrdered
END;

IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
	DROP TABLE #TheseUsersOnly
END;

IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
	DROP TABLE #Return
END;

 

How to drop a user from a SQL Server database when you encounter the error message “The database principal owns a schema in the database, and cannot be dropped”

principalowner

So if you have encountered the error above “The database principal owns a schema in the database, and cannot be dropped” you will not be able to drop the user until ownership of the effected schema has been transferred to another user/role. In order to drop the user, you have to find the schema that is assigned first. You can do this by running the script below replacing myUser with the user name in question. 

SELECT name 
FROM  sys.schemas 
WHERE principal_id = USER_ID(myUser)
Then, use the schema found from the above query in place of the SchemaName below. This transfers ownership to dbo. You may need to alter authorization for multiple schema. Just run the statement for each returned schema replacing SchemaName. You can then drop your user.
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo;
 
GO

DROP USER myUser;