Often times I run multiple queries in a TSQL script in a new query window. But some or all the objects might belong to a schema other than 'dbo'. This means we have to explicitly mention the schema name like
$schemaname.$tablename
But it might be difficult to edit the TSQL to mention the schemaname explicitly. As a DBA who comes from a different RDBMS background, I am used to commands such as
SET CURRENT SCHEMA ABC
or
USE SCHEMA ABC
or
ALTER SESSION SET CURRENT_SCHEMA='schemaname'
So I have always thought of a command or a query option to set the schema for all subsequent commands.
If you run the commands without the correct schema name then you will get the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tablename'.
In order to run all subsequent queries against a different schema use the following command
EXECUTE AS USER = 'SCHEMANAME'
Unfortunately it doesn't work on most recent versions of MSSQL.
ReplyDelete