SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Let SQL Server Write SQL Statements		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 11/09/2000 :  21:15:06  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts
Posted - 11/14/2000 :  13:07:21  Show Profile  Reply with Quote
Creating strings containing single quotes

When creating strings that contain single quotes I find the easiest way is to use double quotes to start and stop the constant which allows the single quotes to be treated as simple single quotes.

As an example I've re-worked graz's example

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
Go to Top of Page

Anonymous
Starting Member

0 Posts
Posted - 11/20/2000 :  05:43:03  Show Profile  Reply with Quote
Or...

Even easier is to not do dynamic SQL at all. Instead of generating strings to execute just do the statements straight away to avoid quotes at all, so instead of:

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')

just produce

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

a lot less messing about, and you can still copy and paste it to execute it.
Go to Top of Page

anton
Starting Member

South Africa
5 Posts
Posted - 03/02/2002 :  07:40:26  Show Profile  Visit anton's Homepage  Reply with Quote
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!
Go to Top of Page

ebuzoku
Starting Member

1 Posts
Posted - 07/27/2005 :  07:03:20  Show Profile  Reply with Quote
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
Go to Top of Page

akak1701
Starting Member

USA
2 Posts
Posted - 06/23/2006 :  13:59:45  Show Profile  Visit akak1701's Homepage  Reply with Quote
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/
Go to Top of Page

mech55
Starting Member

1 Posts
Posted - 04/05/2007 :  18:15:31  Show Profile  Reply with Quote
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'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000