Generate script to automate renaming of default constraints

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.

Ok, couple of things.

• always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here).

• always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have.

• always use 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).

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'EXEC sys.sp_rename N''' 
    + QUOTENAME(s.name) + '.' + QUOTENAME(d.name) 
    + ''', N''DF_' + t.name + '_' + c.name + ''', ''OBJECT'';'
  FROM sys.tables AS t
  INNER JOIN sys.default_constraints AS d
     ON d.parent_object_id = t.object_id
  INNER JOIN sys.columns AS c
     ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
  INNER JOIN sys.schemas AS s
     ON t.schema_id = s.schema_id
  WHERE d.NAME LIKE N'DF[_][_]%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

StackExchange Database Administrators Q#37383, answer score: 16