Monday, April 16, 2012

Run queries against different schema


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'


No comments:

Post a Comment