?
Solved

Rename table with dependancies in SQL Server 2005

Posted on 2010-08-23
8
Medium Priority
?
2,583 Views
Last Modified: 2012-08-13
I have made primary key and indexing changes to a relatively large table in my database and now I need to rename the old table so I can rename the new table to replace it.

The problem comes in with the fact that the old table has about 20 stored procedures that reference the table. SQL Server is complaining about the dependancies when I try to rename it.

How do I get around this?
0
Comment
Question by:dgerler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:mnachu
ID: 33502863
Interesting I thought SQL server would just give a warning if only SPs are referencing the tables and let you rename it.

It will complain only if the tables you are trying to rename is referrenced by other tables using Foreign keys.

Anyways one work around I can think of is just drop the procedures, rename the tables, create the new tables, and create the dropped procedures.

Regards,
Nachi
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33502936
Agree, dependent procs should not prevent the rename.  What exactly is the error message?
0
 

Author Comment

by:dgerler
ID: 33503045
The error...

TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to rename TicketDetailold. (ObjectExplorer)

------------------------------
ADDITIONAL INFORMATION:

Rename failed for Table 'dbo.TicketDetail'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Table&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Object '[dbo].[TicketDetail]' cannot be renamed because the object participates in enforced dependencies. (Microsoft SQL Server, Error: 15336)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=15336&LinkId=20476

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:dgerler
ID: 33503060
I removed the foriegn key already.

What about views? would that cause the problem?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 33503140
Views?  I don't think so.

Try this:

select o.name as ObjName, r.name as ReferencedObj
from sys.sql_dependencies d
join sys.objects o on o.object_id=d.object_id
join sys.objects r on r.object_id=d.referenced_major_id
where d.class=1
AND r.name = 'TicketDetail'
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33503155
Correction.  A view can be the cause if it is stored with the Schema Binding option.
0
 

Author Comment

by:dgerler
ID: 33503175
Looks like it's a view

Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
Detail      TicketDetail
0
 

Author Comment

by:dgerler
ID: 33503228
Yep.. I was able to rename after droping  the view. Of course I scripted it to query window first to make it easy to put back. :) Thank you.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question