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.
CREATE DATABASE [TidBytez];
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]