June 15, 2010 at 3:01 am
Hello community!
I am working on a project in which hibernate is included. Some tables have been created by Hibernate with very cryptic primary key names.
My current task is to check the names of the primary keys and rename them if needed.
I do this with the following code
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME = 'PK_CrypticName1')
EXEC sp_rename 'tblOMgmOrder.PK_CrypticName1', 'PK_tblOMgmOrder', 'INDEX'
GO
When this code is executed i get the warning that scripts and stored procedures could get out of use if the name of the primary key is changed. This is no problem.
Before i found the sp_rename, I tried the following code
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME = 'PK_CrypticName1' )
ALTER TABLE tblOMgmOrder DROP CONSTRAINT PK_CrypticName1
ALTER TABLE tblOMgmOrder ADD CONSTRAINT PK_tblOmgmOrder PRIMARY KEY CLUSTERED (IDOMgmOrder ASC)
GO
But this code was not executed as the error 3725 was thrown saying that a FOREIGN KEY constraint depends on this primary key so it cannot be deleted.
1. How could I find out which foreign keys depend on this primary key if I do not want to generate a database diagram?
2. Why does sp_rename function and the ALTER DROP/ALTER ADD not. Should not the same error message be displayed by sp_rename? Or are all dependent objects modified with the sp_rename?
Thank you for the information
Alex
June 15, 2010 at 4:21 am
Do:
sp_helptext sp_rename
and you will see how sp_rename does it...
June 15, 2010 at 4:27 am
You can find which table is referencing a specific table by running sp_help on the specific table. You’ll get lots of information about the table, and the last piece of information is what object references the table. Another way is to query sys.foreign_keys view.
The reason that sp_rename didn’t cause an error and alter..drop with alter..add caused an error is that the server manages everything with IDs and not by name. So if you have a primary key, it has its own ID. When you rename it, the ID stays the same and you just modify one of it’s properties, so no error is generated. If you try to drop and recreate it, then the server tries to delete a constraint that another constraint is depended on, and you get an error.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply