Problem Transact-SQL Statement

[Allen Yu]

I've come across a Transact-SQL statement as follows:

-- Statements start here

select 'EXECUTE sp_changeobjectowner ''' + name + ''', ''dbo''' from
sysobjects where type = 'U'

-- end here

Questions:

(1) How should the 'select' statement be interpreted?

(2) What are the rules for using the quotation marks: ''', '',,, etc?

Thanks!

Allen

 

[Sylvain Lafontaine]

The online documentation says that the stored procedure sp_changeobjectowner
will return 0 for a successfull completion or 1 for a failure; this is what
will be returned by the select command.

For the quotation marks, in SQL, the string delimiter is the single quote,
', not the double quote " as in VBA (however, one option for SQL give you
the right to use the double quote also as the string delimiter; which raises
a big deal of confusion for newbies) and you must use two single quotes, '',
to enclose a single quote into a string. You can follow the process with
the following example:

EXEC sp_changeobjectowner 'MyName', 'dbo'

to:
Select 'EXEC sp_changeobjectowner ''MyName'', ''dbo'''

to:
Select 'EXEC sp_changeobjectowner ''' + name + ''', ''dbo'''


The second statement can be rewritten this way, to make it easier to
distingued between the levels for the quotes:

Select ' EXEC sp_changeobjectowner ''MyName'', ''dbo'' '

S. L.

 

[Guest]

1. It generates code to change owner of all user tables in the database.

2. You can put table name and owner in quotes like your code does or not
like following:

select 'EXECUTE sp_changeobjectowner ' + name + ', dbo' from sysobjects
where type = 'U'

“Allen Yuâ€ç¼–写：

 

[Guest]

By the way, if existing owner is not dbo, you need following code to change
owner to dbo:


select 'EXECUTE sp_changeobjectowner [' + user_name(uid) + '.' + name + '],
dbo' from sysobjects where type = 'U'

“Allen Yuâ€ç¼–写：

 

[Allen Yu]

Thanks!

The following statement is understood:
Select 'EXEC sp_changeobjectowner ''MyName'', ''dbo'''

But, what is the rule for converting "MyName" to ''' + Name + ''' ?

Allen

 

[Guest]

You can concatenate strings with select statement like this:

select 'this is first name ' + au_fname from pubs..authors

If you like to put name in quotes, can use this:

select 'this is first name ''' + au_fname + '''' from authors

In your case, just replace 'this is first name ''' with 'EXEC
sp_changeobjectowner ''' then get object name from sysobjects table.

 

[Heriberto]

Allen as far as I understand you should use cursor


DECLARE @OBJNAME sysname
DECLARE CUROBJNAME CURSOR READ_ONLY FAST_FORWARD FOR SELECT name FROM
sysobjects where type = 'U'
OPEN CUROBJNAME
FETCH NEXT FROM CUROBJNAME INTO @OBJNAME
WHILE @@FETCH_STATUS = 0
BEGIN

EXECUTE sp_changeobjectowner @OBJNAME, 'dbo'
FETCH NEXT FROM CUROBJNAME INTO @OBJNAME

END

CLOSE CUROBJNAME
DEALLOCATE CUROBJNAME


Heriberto

 

[Guest]

Don't have to use cursor, the select statement here generates those code.
Just run those result code.

“Heribertoâ€ç¼–写：

 

[Allen Yu]

Thanks! That's another solution to my problem!
Allen