Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fix Foreign Key Constraint Naming Expand / Collapse
Author
Message
Posted Thursday, October 7, 2010 10:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:35 AM
Points: 233, Visits: 463
Comments posted to this topic are about the item Fix Foreign Key Constraint Naming
Post #1001019
Posted Friday, October 8, 2010 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 11, 2012 5:42 AM
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.



Post #1001342
Posted Friday, October 8, 2010 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 1, 2014 3:35 AM
Points: 233, Visits: 463
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.
Post #1001405
Posted Sunday, November 6, 2011 9:26 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 12:25 PM
Points: 705, Visits: 1,657
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
Post #1201216
Posted Monday, November 14, 2011 7:01 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 12:25 PM
Points: 705, Visits: 1,657
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.
Post #1205114
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse