Tag Archives: auto

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:


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.


USE [TidBytez]



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


How to get tableau data to refresh automatically within the internet browser

This tutorial describes how to embed a Tableau view, i.e. a published sheet or dashboard etc., into your web page and set the view to refresh every 30 seconds.

Step 1. Open note pad and save the code below into it calling the file template.html

Step 2. Copy the URL

  • Publish the Tableau view and copy the URL from the share button


  • Copy the URL if you have already published the report

Note: If a hash symbol (#) and number, or a “:iid=<n>” appear at the end of the URL, do not include those characters. For example, in the following URL, you would not copy the #3 characters:


Step 3. Replace the following in the template file with your URL and change the title to something more appropriate than Auto Refresh Example.


Note: You can change the rate of refresh by changing the content=”30″ value to however many seconds you need. Also you probably don’t want purple as your background color unless you’re Prince so go ahead and change that too.¬†background-color: #452775

<!DOCTYPE html PUBLIC "-//W3C//DTDXHTML 1.0 Transitional//EN" <html lang="en-US" xml:lang="en-US"> 
<title>Auto Refresh Example</title> 
<meta http-equiv="refresh" content="30">
body {background-color: #452775}


Your browser does not support iframes.