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
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:
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.
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).
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.