[RESOLVED] SQL Script Creator

[Mark Gambo]

I have created the following SQL code to create a script to change the owner of a MS SQL Server database:

/*Change Owners of a table:*/
 
DECLARE @OldOwner CHAR(25)
DECLARE @NewOwner CHAR(25)
 
SET @OldOwner ='dbo'
SET @NewOwner = 'ADM'
 
SELECT 'exec sp_changeobjectowner ' + CHAR(39) + RTRIM(LTRIM(@OldOwner)) + '.' + _
         Name + CHAR(39) +',' + CHAR(39)+ RTRIM(LTRIM(@NewOwner)) + CHAR(39) Owner_Change_Script
FROM sysobjects
WHERE xtype = 'U' AND UID = 5 
ORDER by Name

This code outputs the following:

exec sp_changeobjectowner 'dbo.CLASSES','ADM'
exec sp_changeobjectowner 'dbo.CAUSES','ADM'
exec sp_changeobjectowner 'dbo.LEVEL','ADM'
exec sp_changeobjectowner 'dbo.ACCIDENTS','ADM'
exec sp_changeobjectowner 'dbo.ACTIONS','ADM'

But I want the Script to insert a 'Go' in between each exec statement like this:

exec sp_changeobjectowner 'dbo.CLASSES','ADM'
GO
exec sp_changeobjectowner 'dbo.CAUSES','ADM'
GO
exec sp_changeobjectowner 'dbo.LEVEL','ADM'
GO
exec sp_changeobjectowner 'dbo.ACCIDENTS','ADM'
GO
exec sp_changeobjectowner 'dbo.ACTIONS','ADM'
GO

But for the life of me I can't seem to figure it out. Any ideas how I can go about doing this??? The databases I have to convert have hundreds and hundreds of tables and when it throws an error I want to be able to click on the error and be brought to the line that threw the error.

Thanks!

[si_the_geek]

Something like this perhaps:

... + CHAR(13) + 'GO' + CHAR(13)

Instead of 13 you may need 10, or 13 followed by 10.

[Mark Gambo]

Something like this perhaps:

... + CHAR(13) + 'GO' + CHAR(13)

Instead of 13 you may need 10, or 13 followed by 10.

Thanks, I tried your suggestion but I get the following:

exec sp_changeobjectowner 'dbo.CLASSES','ADM' GO
exec sp_changeobjectowner 'dbo.CAUSES','ADM' GO
exec sp_changeobjectowner 'dbo.LEVEL','ADM' GO

Which throws an error.

I probably have to use some sort of UNION but for the life of me I can't get it to work.

[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
.
.
.

[Mark Gambo]

Nice, thanks Steve!