Generate script to automate renaming of default constraints

Ok, couple of things.

1. 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).
2. 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.
3. 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.

4. 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;
   

Based on your question .. which you have removed "automate the same script on some of the databases on the instance"

Below is the code that will help you

set nocount on
DECLARE @table TABLE 
  ( 
     dbname VARCHAR(30) 
  ) 

INSERT INTO @table 
            (dbname) 
VALUES      ( 'dev_construct1' ), 
            ('dev_construct2'), 
            ('dev_construct3' ); 

DECLARE @sql NVARCHAR(max) = N''; 
DECLARE @dbname VARCHAR(30) 

/*  
Added by Kin : While loop and an extra @dbname variable 
*/ 
SELECT @dbname = Min(dbname) 
FROM   @table 

WHILE @dbname IS NOT NULL 
  BEGIN 
      SELECT @sql = N'USE ' + tt.dbname + Char(10) + N' GO;'
      FROM   @table tt 
      WHERE  @dbname = dbname 

      SELECT @sql += Char(10) + N'EXEC sp_rename N''' 
                     + Quotename(s.name) + '.' + Quotename(d.name) 
                     + ''', N''DF_' + t.name + '_' + c.name 
                     + ''', ''OBJECT'';' 
      FROM   sys.tables AS 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 
             JOIN @table tt 
               ON tt.dbname = tt.dbname 
      WHERE  d.name LIKE 'DF[_][_]%'; 

      PRINT @sql 

      SELECT @dbname = Min(dbname) 
      FROM   @table 
      WHERE  dbname > @dbname 
  END 
-- EXEC sp_executesql @sql;