|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
query analyzer table owneri 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! 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. 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 quoteHide 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. > g wrote:
> i have a table owner of sysdba so when writing queries as sa I have to If you don't add the owner, you won't ever see it in a trace, except > 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! 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
Other interesting topics
Diagram to Image
Read80Trace.exe in SQL Server 2005 Just how to navigate SQL Server? Database Diagrams SQL stored proc source control Cannot create BI package get funny error 0x8002801D Library Not Last Access to the Database Scripting options file damage and repair workgroup SQL server database backup to a second server |
|||||||||||||||||||||||