May 15, 2013 at 6:43 am
Great idea - but I found a flaw...
If you have a view (2nd level) that references another view (1st level), unfortunately, the stored procedure does not drop any 2nd level view, which makes the drop of the datatype fail.
May 20, 2013 at 1:36 pm
Hello, I've enjoyed working with these scripts and adapting them to my environment. Thank you. Couple of changes and ideas.
1. I've modified the functions to take two parameters: Table Name and Index Name as some indexes have the same name but different tables.
2. Generated an automated loading of the two functions across multiple databases: Change_UDT_Definition_LoadFunctions.
3. Generated a wrapper script to run the proc in multiple databases: Change_UDT_Definition_WrapperScript.
4. Created logging to generate a manual way to script of what is going to run before it makes changes. ( this has come in handy in reference to the 2nd level Views failing out the DROP TYPE)
5. The Second part of the logging conducts the actual transformations and drops etc... and loads into the same table for tracking.
6. In my environment we are not needing to drop PKs etc.. just the FK so I've modified the script to get just the FK_s with a WhereIs_Foreign_Key = 1
7. Added a parameter to turn on [ @PrintOnly =1 or 0 ] so you can run in an environment to see what is going to happen and not modify anything.
Hope this helps... let me know if you have any questions.
Best,
Andrew
Andrew Brittain
Database Administrator
www.galaxysql.com
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply