Rename table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rename table

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-11-08 : 20:09:05

Hi,
How can I rename a table in sql database through sql code?

Thanks,
Mavericky

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-08 : 20:17:43

sp_rename

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:00:23

make sure you check the dependencies on table before you do that using sp_depends

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 03:17:23

quote:
Originally posted by visakh16

make sure you check the dependencies on table before you do that using sp_depends

Interesting. What might sp_depends pick up that sp_rename would not sort out?

I'm thinking Sprocs and Triggers - not sure about Views?

Are there other things too? (We have processes in place for those, but if there are others I might tighten up the checks we make before using sp_rename in future

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 03:34:28

quote:
Originally posted by Kristen

quote:
Originally posted by visakh16

make sure you check the dependencies on table before you do that using sp_depends

Interesting. What might sp_depends pick up that sp_rename would not sort out?

I'm thinking Sprocs and Triggers - not sure about Views?

Are there other things too? (We have processes in place for those, but if there are others I might tighten up the checks we make before using sp_rename in future )

yep same . views would be dropped and recreated
we even do a further check using sys.sql_modules on table name to find out any instances of usage of table inside dynamic sql in procs which sp_depends wont return

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 14:11:20

We have unique column names (all column names unique within the database), so we can do FIND on a column name with reasonable certainty. All parameters, which relate directly to a column, include the column name, in full, as part of the parameter / working variable, for the same reason.

But we don't check Check Constraints, or other such "soft" code ... maybe we should? Trouble is we use them rarely, so aren;t looking there - but that will "bite us" when we first get caught out.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 14:13:32

quote:
Originally posted by Kristen

We have unique column names (all column names unique within the database)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 15:38:25

Personal choice of course.

Our thoughts are that having a column called [ID] in every table makes it more likely for errors to be introduces and, if that column needs to change Datatype / Length etc., for changes to take more time to implement and more bugs to be left to be found - often, expensively, "post-live".

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 15:59:05

well fine, but what about PersonID or what have you..is your address or phone table going to have a differnt column name?

Address_Person_Id?

I like to keep the column name the same when I establish relationships..otherwise..doesn't confusion reign??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 17:52:50

"Address_Person_Id?"

Yup, we do something like that.

We assign a Mnemonic for the Sub-System, and another for each Table. "Customer" is in the "Sales Order Processing" Sub-System - thus the table is "SOP_CUST_Customer" and all columns are prefixed "sop_cust_"

sop_cust_ID
sop_cust_Name
etc.

The Order Header table, also in Sales Order Processing Sub-Systen, is SOP_ORDH_OrderHeader, and all columns are prefixed "sop_ordh_":

sop_ordh_ID
sop_ordh_Date
etc.

and the Customer for the Order is:

sop_ordh_sop_cust_ID

indicating the relationship. If there are two - e.g. Invoice Address and Delivery Address then:

sop_ordh_sop_adr_ID_Invoice
sop_ordh_sop_adr_ID_Delivery

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 20:46:27

I've been around since man invented fire...you know that's VERY old school

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 03:01:07

I used Hungarian notation in C days, and found that useful in keeping down bugs.

So

SELECT @intXXX = @strYYY

is an alert that the datatypes are mixed and may not be the right variables.

Same thing, for us, in a join:


SELECT *
FROM SOP_ORDH_OrderHeader
     JOIN SOP_CUST_Customer
         ON sop_cust_ID = sop_ordh_sop_cust_ID

gives visual feedback that we haven't mixed inappropriate columns which this would immediately indicate:


SELECT *
FROM SOP_ORDH_OrderHeader
     JOIN SOP_CUST_Customer
         ON sop_cust_ID = sop_ordh_sop_adr_ID

Plus, as mentioned, we can do a guaranteed-hit Find & Replace when a column changes name, or some attribute - such as Length or Datatype.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources