David Gerler
asked on
Rename table with dependancies in SQL Server 2005
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?
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?
Agree, dependent procs should not prevent the rename. What exactly is the error message?
ASKER
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.Conne ctionInfo)
-------------------------- ----
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
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.Conne
--------------------------
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
ASKER
I removed the foriegn key already.
What about views? would that cause the problem?
What about views? would that cause the problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Correction. A view can be the cause if it is stored with the Schema Binding option.
ASKER
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
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
ASKER
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.
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