January 8, 2008 at 2:38 am
hello all!
I have a few SQL servers and one is a test server,
Every night I backup and restore the live database to a test server.
The tasks that I now need to do is..
1. rename the database
2. rename Most of the tables inside the database.
3. alter data within tables etc etc.
a bizarre request I know, but how this particular system works is that all the tables have a naming convention that the Client reads.
"companyname$tablename"
e.g.
fredBlogsLtd$InvoiceData
fredBlogsLtd$SalesData
fredBlogsLtd$PurchaseData
There are something like 260 tables and 245 have a prefix of company name.
Is there a simple script that I can run to rename all the tables?
joeBlogsLtdTEST$InvoiceData
joeBlogsLtdTEST$SalesData
joeBlogsLtdTEST$PurchaseData
Many thanks in advance!
Dave
January 8, 2008 at 4:35 am
You do have a reasonable number of tables to rename as well as a good proportion that you do not want to rename. My suggestion would be as follows:
1. Create a small table with two attributes: TableName and IsActive; the latter will be used to flag a table for renaming purposes;
2. Write a script to populate this table with the table names. You will need to execute this script daily; therefore, the script should check for non-existing tables and to insert it;
3. Write a script to loop through the tables with IsActive = 'True', generate the sql statement using 'sp_rename' and execute this sql statement;
HTH
Paul
January 8, 2008 at 5:06 am
Thanks for the reply paul.
this is the script that ive come up with so far...
ActiveX Script
' db connection
set dbConn= createObject("ADODB.connection")
dbConn.mode = adModeReadWrite
dbConn.connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=myUserAccount;Initial Catalog=MyDatabaseName;Data Source=172.20.1.7; network library=dbmssocn"
dbConn.open
Cmd = CreateObject("ADODB.Command")
SQL = "select Name from sysObjects"
set rs = createObject("adodb.recordset")
set rs = dbconn.execute(SQL)
while not rs.eof
if left(rs("name"),12) = "oldbusinessname" then
Oldname = rs("name")
OldNamelength = len(rs("name")
NewName = replace(oldname, "OldBusinessName", "newBusinessName")
Cmd.ActiveConnection = dbconn
Cmd.CommandType = adStoredProcedure
Cmd.CommandText = "SP_rename" & ",'" & oldname & "','" & newName & "'"
Set rs1 = cmd.Execute
end if
rs.movenext
wend
set rs1 = nothing
set rs = nothing
I'll just keep tweeking this until it works..
cheers
Dave
January 8, 2008 at 5:12 am
I was more thinking of writing 'stored procedure' scripts to perform these operations.
If you need to execute these from a VB app, you can simply invoke the relevant 'stored procedure'.
Paul
January 8, 2008 at 5:21 am
ahh, my weakness,
I really should buy a book on writing Stored Procedures 🙁
I'll give it go..whats the worst that could happen 😉
cheers Paul
Dave
January 8, 2008 at 5:22 am
Hi Dave,
Try this SQL script. If it works as you want just uncomment commented line. It's a good idea to create SP having old and new names as input parameters. Have a nice day.
declare @TableName sysname
declare @OldBusinessName nvarchar(128)
declare @newBusinessName nvarchar(128)
declare @Cmd nvarchar(max)
set @OldBusinessName = 'fredBlogsLtd'
set @newBusinessName = 'joeBlogsLtdTEST'
declare c cursor
for
select [name]
from sysobjects
where objectproperty(id, 'isusertable') = 1
and [name] like @OldBusinessName + '%'
open c
FETCH NEXT FROM c INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @Cmd = 'sp_rename ''' + @TableName + ''', ''' + replace(@TableName, @OldBusinessName, @newBusinessName) + ''''
-- EXEC sp_executesql @Cmd
PRINT @Cmd
END
FETCH NEXT FROM c INTO @TableName
END
CLOSE c
DEALLOCATE c
January 8, 2008 at 5:32 am
thanks Nebojsa
I'll give it a whirl!
cheers
Dave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply