Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D
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.
Best Answer
Ok, couple of things.
EXEC
when executing stored procedures; the shorthand withoutEXEC
only works when it is the first statement in the batch (and that will not be the case here).QUOTENAME()
instead of manually applying square brackets yourself. In this case you're probably safe, but there are cases where the manual approach will break.you can test the
PRINT
output but it won't necessarily be complete if your total command is > 8k (see this tip for some alternative approaches).