Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like:
I would prefer to have them all manageable with a consistent naming like
DF_Users_TimeZoneInfo so that we can ensure that the appropriate constraints exist on future target tables (like in RedGate compare, or even just visually)
I've got a script that mostly works for what I want:
select 'sp_rename N''[' + s.name + '].[' + d.name + ']'', N''[DF_' + t.name + '_' + c.name + ']'', ''OBJECT'';' from sys.tables t join sys.default_constraints d on d.parent_object_id = t.object_id join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id join sys.schemas s on t.schema_id = s.schema_id WHERE d.NAME like 'DF[_][_]%'
But this just gives me a resultset, and not something I can actually pass into an exec or whatever.
How can I make this so I can just execute those
sp_rename scripts without having to resort to copying out all the returned elements and pasting them into a new query window and running them again? Trying to save as many keystrokes as possible so I can correct this in many environments.