Article: Let SQL Server Write SQL Statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Let SQL Server Write SQL Statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-09 : 21:15:06

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.

Article Link.

anton
Starting Member

5 Posts

Posted - 2002-03-02 : 07:40:26

I was having some issue with the above code so i just used:

SELECT 'exec sp_changeobjectowner ''' + ltrim(u.name) + '.' + ltrim(s.name) + ''',' + '''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

Thanks guys, saved me lotsa time!

ebuzoku
Starting Member

1 Post

Posted - 2005-07-27 : 07:03:20

I am using a cursor as I needed to perform the change on the fly.
Any better suggestions!

Thanks everyone!


DECLARE @sObject VARCHAR(255)
DECLARE cursObjects CURSOR FOR
	SELECT 
		SU.NAME + '.' + SO.NAME AS OBJECT
	FROM 
		SYSOBJECTS SO , SYSUSERS SU 
	WHERE 
		SO.UID = SU.UID
		AND SU.NAME <> 'dbo' 
		AND SU.NAME NOT LIKE 'INFORMATION%'
		AND XTYPE IN ('V', 'P', 'U')
	ORDER BY SO.NAME 

OPEN cursObjects
	FETCH NEXT FROM cursObjects INTO @sObject
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC ('sp_changeobjectowner ''' + @sObject + ''' , ''dbo''')
		FETCH NEXT FROM cursObjects INTO @sObject
	END
CLOSE cursObjects 

DEALLOCATE cursObjects

And just so you know I love indenting my code!

Nom Ercy

akak1701
Starting Member

2 Posts

Posted - 2006-06-23 : 13:59:45

Thanks for the script. I did have one minor issue in that it didn't include functions. I added in the xtype FN and it works!

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', 'FN')
AND u.name not like 'INFORMATION%'
order by s.name

Greg Benedict
http://www.tgfi.net/

mech55
Starting Member

1 Post

Posted - 2007-04-05 : 18:15:31

Hi guys, I have a problem where I have to change the owner of several hundred stored procedures and functions. I created a script based on what I saw in this column but it's not quite working i'm not sure exactly where I got it wrong though. help!

select 'EXEC sp_changeobjectowner '''+ name + ''', ''dbo'';'
from dbo.sysobjects
where type='P' and category='0'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources