Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

share|improve this question
    
<facepalm> You should have asked me about this the other day -- I pulled my answer from the script I have that does exactly this. :) –  Jon Seigel Mar 22 '13 at 22:43
    
@JonSeigel ok, how about a level of indrection around this to run once on each of several dbs? :D –  jcolebrand Mar 22 '13 at 22:48
add comment

2 Answers

up vote 5 down vote accepted

Ok, couple of things.

  1. always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the only statement in the batch (and that will not 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.

    DECLARE @sql NVARCHAR(MAX) = N'';
    
    SELECT @sql += 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
    WHERE d.NAME like 'DF[_][_]%';
    
    PRINT @sql;
    -- EXEC sp_executesql @sql;
    
share|improve this answer
    
Good to know about the EXEC for each statement, I didn't know about that. –  jcolebrand Mar 22 '13 at 21:08
3  
Yeah try to execute this: sp_help; sp_help;. –  Aaron Bertrand Mar 22 '13 at 21:09
add comment

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; 
share|improve this answer
    
I removed it because the previous question was missing a key bit of detail, since the column names change in each db, so I need to run the inner query per context of the individual db. –  jcolebrand Mar 22 '13 at 22:09
    
@jcolebrand thanks for the update. In any case, the code with my changes will help you what you are looking. –  Kin Mar 22 '13 at 22:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.