October 7, 2010 at 10:56 pm
Comments posted to this topic are about the item Fix Foreign Key Constraint Naming
October 8, 2010 at 8:22 am
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.
October 8, 2010 at 9:29 am
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.
November 6, 2011 at 9:26 pm
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
November 14, 2011 at 7:01 am
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.
May 18, 2016 at 7:03 am
Thanks for the good script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply