Rename Tables T-SQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Dale Burrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Ok, so I still need to specify the table names. Is there a script I can run that will list all the tables. I am working with a lot of tables so it would make it easier if I could do this.

@fjkaykr11 : I have already given you a script in my earlier comment, did you get a chance to try that

ASKER

@Neo_jarvis thanks for the scirpt. I ran it and didn't get any errors but the table names didn't change. I hit refresh.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

When I run the script I get a blank results window.

what is your selection criteria, I am assuming your selection criteria for tables is OrderItem% isn't that correct?

ASKER

I ran the script that you posted

ASKER

I figured out why I was getting a blank results. I had the wrong naming convention. It runs ok now and returns the results:
exec sp_rename 'OrderItem' 'OrderItem2'
exec sp_rename 'OrderItems' 'OrderItems2'

When I run the
exec sp_rename 'OrderItem' 'OrderItem2' I get a syntax error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'OrderItem2'
-- any ideas?

You are missing a comma. It should be (no points please):
select 'exec sp_rename ' + '''' + TABLE_NAME + '''' + ', ' +'''' + TABLE_NAME + '2' + ''''

ASKER

@acperkins, thanks for the info.
I ran this again with your suggestion and it works, I actually got back all the tables in the database by just specifying % instead of OrderItems%.
However, when I execute one of the stored procedures.
It says:
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332
No item by the name of 'OrderItems' could be found in the current database 'Customers', given that @itemtype was input as '(null)'.

You obviously overlooked the statement made in the very first comment. Here it goes again:

CAUTION:
Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

Since you have changed the table name you are now going to have to change all references to that table in all VIEWs, Stored Procedures and User Defined Functions.

Incidentally, if you have Red-Gate's SQL Prompt they have a feature called Smart Rename that not only changes the table name, but also all dependencies:

ASKER

@acperkins, thanks for pointing out the info from the previous post. I remember seeing that when I first posted this. I was only trying out different methods suggested. Are you saying there might not be a method with T-SQL to do this without breaking functionality?

ASKER

This isn't a production database so I am not really concerned about implication.
I think I realize the problem now. When I tried to get a list of all the tables in the database by using:
select * from sys.tables;
I am only getting back part of the tablename: OrderItems
The full name is Customer.OrderItems

So when I run the sp_rename with full table name it runs ok.
I figured a workaround with having to use the wildcard by
putting the output into Excel and creating the list from that.
My question is being able to get back the full table name
Customer.OrderItems instead of OrderItems by using another method
than sys.tables (if there is another one). Thanks again.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

@Neo_Jarvis, Thanks for your follow up. With that method I still have to specify the table name which defeats the purpose of me doing this. For now I am just looking to get back the
full table name of all the tables, then I will build something in Excel to create a query for changing the table name (unless I can eventually find another method). For now I am just looking to find a method for returning all the tables in a database with the full table name.
If I run the command: select * from sys.tables;
I am only getting the second part of the table name, in this case OrderItems
I need it to return Customer.OrderItems. Any ideas? Thanks.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Ok that method worked. I am going to close this post and award points. I am still not sure why the select * from sys.tables; command is wrong for this instance I am using SQL Server 2005. I will open another post with this issue of the proper method to look up all table names in SQL Server 2005 so @acperkins suggests another 3rd party tool to use instead of T-SQL. Thanks.

ASKER

Thanks