Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fix Foreign Key Constraint Naming


Fix Foreign Key Constraint Naming

Author
Message
Ligtorn
Ligtorn
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 486
Comments posted to this topic are about the item Fix Foreign Key Constraint Naming
maleitzel
maleitzel
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 261
Hello, Michael.

An excellent stored procedure.

It would be beneficial to create this in a database where all database utilities are stored and send the fks database name as an additional parameter. That way the stored procedure, functions, etc., will be in only one database and can be executed from the utilities database against any other database.

The benefits would be that you have the new objects in one database. Any updates/changes to the process would be done in only one place.

You'd need to redo some of the t-sql as dynamic sql and string the database name parameter into the t-sql stream. Maybe this change can be made in an update?

Once again, a very useful stored procedure! Thanks.



Ligtorn
Ligtorn
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 486
Actually I tried to design it as you described. But it is not that easy, if I want the code reusablity, which my TVF gives me.

I reuse the same TVF's in a lot of my utility stored procedure procedures. TVF are not easy to convert into dynamic sql, otherwise I would have done it.
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
I have tested this stored procedure against a local copy of my database at work and it works great. The only problem is I would like to alias the table names. For example, if the table name is ProductName, I want the alias used to be PN, if it is Employee I want the alias used to be E.

Where in Michael's code http://files.soendergaard.info/Install_spFixForeignKeyNaming_V2005-2008.sql do I make this change?


Michael says:
Example 6 – Solving long names with object aliases

The stored procedure can automatically replace schema, table, parent or referenced column name, with an object alias instead.

It works by using a mapping table and a synonym. The mapping table requires a certain structure, but may be named whatever you like. This is there the synonym comes in. The stored procedure requires the name to be called dbautils.AliasRulesSynonym, but it may point to a table in an external database. This is useful if you like to have a central object alias repository. The synonym give the flexibility, that you don't need to change the code of the stored procedure, you only need to change what the synonym points to.

The parameter @UseAliases is a char mask. By setting the correct char mask, you have total control of what types of aliasing you want to enable.

'C' = Column aliasing

'T' = Table aliasing

'S' = Schema aliasing
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
The answer to my question is that Michael's sproc creates a table in the db. It is called AliasRules. All you have to do is insert into that table, your aliases. Make sure to look at his sample.
All good.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8684 Visits: 885
Thanks for the good script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search