query analyzer table owner

16 Feb 2006 2:04 AM

g

i have a table owner of sysdba so when writing queries as sa I have to
prefix the tables... sysdba.[TableName] but when I do a trace it shows as
just [TableName] as the sql commands are executed by sysdba, is my guess. I
cant change that...

is there a quick way of prefixing tablenames with the table owner? when
writing scripts and executing them in query analyzer it gets annoying having
to change sql code to prefix properly.

TIA!

16 Feb 2006 7:24 AM

Jens.Suessmeyer

You can quickly create scripts by using this command:

SELECT 'sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + Table_name +
''',''newowner'''
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_SCHEMA = 'oldowner'

(Untested)

HTH, jens Suessmeyer.

16 Feb 2006 4:35 PM

g

Thanks but I cant create the scripts dynamically, the scripts are already
generated for me by another application so I get something like:

Select account, email from account where accountid = 'xyz'

and I need to change it to

select account, email from sysdba.account where accountid = 'xyz'

it's not so bad for a simple statement like this but once you get joins and
nested sql it gets cumbersome

<Jens.Suessme***@googlemail.com> wrote in message
Show quote

news:1140074651.542770.75340@g43g2000cwa.googlegroups.com...
> You can quickly create scripts by using this command:
>
> SELECT 'sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + Table_name +
> ''',''newowner'''
> FROM INFORMATION_SCHEMA.Tables
> WHERE TABLE_SCHEMA = 'oldowner'
>
> (Untested)
>
> HTH, jens Suessmeyer.
>

16 Feb 2006 6:01 PM

David Gugick

g wrote:

> i have a table owner of sysdba so when writing queries as sa I have to
> prefix the tables... sysdba.[TableName] but when I do a trace it
> shows as just [TableName] as the sql commands are executed by sysdba,
> is my guess. I cant change that...
>
> is there a quick way of prefixing tablenames with the table owner?
> when writing scripts and executing them in query analyzer it gets
> annoying having to change sql code to prefix properly.
>
> TIA!

If you don't add the owner, you won't ever see it in a trace, except
when looking at execution plan events, which will show the fully
qualified object name since it has been resolved by that time.

Are you looking to change existing code to add the owner to the SQL or
are you looking for a way to do something in QA?

--
David Gugick - SQL Server MVP
Quest Software