SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fix Default Constraint Naming


Fix Default Constraint Naming

Author
Message
Ligtorn
Ligtorn
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 489
Comments posted to this topic are about the item Fix Default Constraint Naming
David Walker-278941
David Walker-278941
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 231
The link to "sql.soendergaard.info" says "no scripts found".
Ligtorn
Ligtorn
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 489
use these links below or type the sql.soendergaard.info. in the address bar.

-- # You can download a complete install script here.
-- # http://files.soendergaard.info/Install_spFixColumnDefaultNaming_V2005-2008.sql
-- #
-- # I also have a test case script, which can be tried out in tempdb
-- #
-- # http://files.soendergaard.info/TestCasesOnly_spFixColumnDefaultNaming_V2005-2008.sql
David Walker-278941
David Walker-278941
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 231
Thanks.
johnzabroski
johnzabroski
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 191
Just used this last night. Fixed 4,500 naming violations. Wow. I had no idea people here were so bad at keeping up with our internal naming conventions. Stuff like default constraints get ignored every time!

I have to say, thank you very much for providing this script.

Overall, this was one of the easiest scripts I ever used and I was in and out in 5 minutes. Beautiful.

My only "missing feature" complaint would be that it doesn't let me deploy this to a "dbacode" database and provide a @Database parameter to say which database to run it in.

My only "bug report" complaint is that the script can't handle situations where it is renaming a default constraint to one already in the database. Usually this happens when people rename columns. I personally don't mind hunting down the offending problem, though, and fixing it manually.

Since I didn't want to keep this code in the general production databases, I added this afterward:

drop table dbautils.aliasrules
drop synonym dbautils.aliasrulessynonym
drop function dbautils.fnGetFilteredObjects
drop function dbautils.fnGetFilteredTables
drop function dbautils.fnGetObjectAliases
drop function dbautils.fnStringList2Table
drop procedure dbautils.spFixColumnDefaultNaming
drop schema dbautils


Ligtorn
Ligtorn
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 489
Thanks for the feedback.

Your are right with the issue with renamed columns, I have already runned into it, but it is not easy to fix, as it would need to be aware of the changed it is going to do, and with defaults matching other columns. Because of this I decided people should tweak the scripts themselves

I would also like to have every dba proc's in one database, but it is not possible to do dynamic sql in table valued functions. This means that all my renaming sproces would have to duplicate the TVF code into the sproc sometimes numerous times, and that would make code reuse and maintenance impossible.

The reason I have to use dynamic code in TVF, is because I query sys tables a lot, and those contain local database schema information only, hense the need for dynamic sql. I also use the TVF alot in other unpublished script at work. It would have been nice if Transact SQL supported the ability to have functions inside sprocs, which can only be called from the sproc. Kinda like functions declared in functions as in pascal
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