[RESOLVED] SQL Script Creator

[Mark Gambo]

This is a little overkill but I used a Cursor to output the script the way I wanted it to look:

/*Change Owners of a table:*/
 
DECLARE @TargetOwner CHAR(25)
DECLARE @SourceOwner CHAR(25)
DECLARE @OwnerScript CHAR(100)
 
SET @TargetOwner ='dbo'
SET @SourceOwner = 'IA_ADM'
 
 
DECLARE TablesCC CURSOR
FOR SELECT 'exec sp_changeobjectowner ' + CHAR(39) + RTRIM(LTRIM(@TargetOwner)) + '.' + [Name] + CHAR(39) +',' + CHAR(39)+ RTRIM(LTRIM(@SourceOwner)) + CHAR(39) [Owner Change Script]
    FROM sysobjects
    WHERE xtype = 'U' AND UID = 5 
    ORDER by [Name]
 
OPEN TablesCC
 
FETCH NEXT FROM TablesCC INTO @OwnerScript
 
WHILE @@FETCH_STATUS=0
    BEGIN
        PRINT @OwnerScript
        PRINT 'GO'
        FETCH NEXT FROM TablesCC INTO @OwnerScript
    END
 
 
Close TablesCC
DEALLOCATE TablesCC

[szlamany]

I love the challenge of making something SET-based - it's great!

I've done this before so it came quickly

/*Change Owners of a table:*/
 
Declare @Dup Table (Dup int)
Insert into @Dup values (1)
Insert into @Dup values (2)

DECLARE @OldOwner CHAR(25)
DECLARE @NewOwner CHAR(25) 

SET @OldOwner ='dbo'
SET @NewOwner = 'ADM' 

SELECT Case When Dup=2 Then 'Go' Else 'exec sp_changeobjectowner ' + CHAR(39) + RTRIM(LTRIM(@OldOwner)) + '.' + 
         Name + CHAR(39) +',' + CHAR(39)+ RTRIM(LTRIM(@NewOwner)) + CHAR(39) End Owner_Change_Script
FROM sysobjects
Left Join @Dup on 1=1
WHERE xtype = 'U' --AND UID = 5
 ORDER BY Name,Dup

Create a dummy table with two rows. Join to that table with a "forced true".

You end up doubling your rows.

And each row has a unique identifier from the dummy table.

You end up getting:

Owner_Change_Script
------------------------------------------------
exec sp_changeobjectowner 'dbo.Addr_T','ADM'
Go
exec sp_changeobjectowner 'dbo.AppConnect_T','ADM'
Go
exec sp_changeobjectowner 'dbo.AppElem_T','ADM'
Go
.
.
.