Let SQL Server Write SQL Statements

By Bill Graziano on 10 November 2000 | Tags: Queries

How many times have you wanted to call a stored procedure a number of times to manipulate a series of objects? You might do this change permissions or to reset object owners. Here's an article about how a lazy DBA approached this problem with a minimum of fuss.

Ok, I'm a lazy DBA. I don't like to work. And I especially don't like boring work. I think the computer should do all the work.

When I first transferred the SQLTeam database objects to my host, I used the Enterprise Manager's Generate SQL scripts to create the objects. This script created the objects as the dbo. It put the word dbo in the script. And it worked just fine.

The next few objects I created I copied up the scripts myself and used Query Analyzer to create them. These were mostly stored procedures. These were created using the login my host had assigned to me. We'll call this lname for this article. As I created more objects some were owned by the dbo and some where owned by lname. And it still worked just fine. Mostly.

Certain times where scripts where generated they would explicitly reference a dbo owned object. Enterprise Manager is especially nasty about doing this. What I needed to do was update all my objects to the same owner and then keep them that way.

You can use the sp_changeobjectowner system stored procedure to change the owner of an object. The syntax looks like this:

sp_changeobjectowner [@objname =] 'object', [@newowner =] 'owner'
Unfortunately I had to do this for about fifteen objects. And remember I'm a lazy DBA. If I'm going to actually work I'd much rather it be interesting work. I thought I'd let SQL Server write my commands for me.

The hard approach would be to write a cursor that would loop through the objects. It would issue the sp_changeobjectowner for each object and we'd be done. That sounded too hard and too boring. I already knew how to do that. What I wanted was to have SQL Server write a script that I could copy and paste into the Query Analyzer. And I wanted to do it in one select statement. (My proof reader says we should spring this one on that recruiter).

After much experimenting I came up with this SELECT statement:

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
ltrim(u.name) + '.' + ltrim(s.name) + ''''''
+ ', @newowner = dbo'')'
FROM sysobjects s,
sysusers u
WHERE s.uid = u.uid
AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND u.name not like 'INFORMATION%'
order by s.name
And doesn't this look ugly. Next time I'm tempted to just write the darn cursor. Or even worse, change them by hand one at a time. Getting the apostrophe's to work properly is a pain!

This query finds every view, stored procedure and user table in the database not owned by the dbo and converts ownership to the dbo. The output looks like this:

EXEC('sp_changeobjectowner @objname = ''lname.Authors'', @newowner = dbo') EXEC('sp_changeobjectowner @objname = ''lname.BANNER_Ads'', @newowner = dbo') EXEC('sp_changeobjectowner @objname = ''lname.Comments'', @newowner = dbo')

You can simply copy and paste the EXEC statements into Query Analyzer and run it. This might have been overkill for fifteen objects in my database. At work I deal with a database that has over 6,000 stored procedures and approaches like this are a little more appropriate. Enjoy.