| Author |
Topic |
|
Razzle00
Starting Member
35 Posts |
 Posted - 2012-12-20 : 13:44:35
|
| Hi, I have a database with over a 100 tables, stored procedures and views. I would like to rename the data to something more descriptive to the project. Can someone tell me the best practice to do this so that all of the tables, stored procedures and views and the Logical Name and Physical File Name database is updated as well. I found this article on the internet, but was not sure if this is 100% correct. Thanks in advance!http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/Razzle |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 14:00:21
|
| looks good. can you afford downtime?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Razzle00
Starting Member
35 Posts |
Posted - 2012-12-20 : 14:13:41
|
| Yes, I can schedule downtime. Just need to make sure that the rename works without cause any problems with identity, permissions and to be sure backups will be able reference the newly named database. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-12-20 : 14:49:20
|
| >>"...so that all of the tables, stored procedures and views..."I'm confused by this. Are all your objects named with the database name as part of the table/sp/view name? The link doesn't address that.Be One with the OptimizerTG |
|
|
|
Razzle00
Starting Member
35 Posts |
Posted - 2012-12-20 : 15:03:20
|
| TG, No, the tables, stored procedures and views are not named with the database name. What I meant was, for example, every stored procedure when you look at the code, reference the database name at the beginning. Like this..USE [MyDataBaseName]GO/****** Object: StoredProcedure [dbo].[MyTableName] Script Date: 12/20/2012 2:58:53 PM ******/would the USE [MyDataBaseName] be renamed as well in the stored procedure code? That is what I am refering to.Thanks,Razzle |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-20 : 16:26:25
|
| Those will change based on Databasename but if you have used USE DB inside the code then you will have to change it or if you are using three part naming convention |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-12-20 : 16:43:22
|
You do need to consider any cross database references in existing code. processes outside the database that rely on connection strings, SQLCMD or OSQL script files, source control code, etc.I have found that changing the the name of existing objects is just not worth it. It's hard enough on new project that has been under way for a little while but on projects that are deployed, in use and been around can be a nightmare. Especially if it is just a matter of not being descriptive enough.I've lived with some stupid names. And that is why the worst part of my job when I have to come up with new object names. It can take me longer to decide on one stupid column name than it takes me to code half the app  Be One with the OptimizerTG |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2012-12-21 : 17:46:47
|
| No, the article is certainly not the best approach to use to rename a db.In particular, you do NOT want to detach a db; that's an obsolete method of renaming it. Instead, you make the needed changes in the master db using ALTER commands; there are examples in Books Online. Then take the db OFFLINE and back ONLINE to put the changes into affect.The "USE <db_name>" when you look at a stored proc are generated at that time, so a db name change won't be any issue there.However, any cross-database references you have, or any 3-part names within the db code, will NOT automatically be changed. You need to idenity and change all those yourself. |
|
|
|
|
Best way to rename a database