CODEWITHSUNDEEP
sql-server
user3063176
user3063176

Reputation: 1

Set variable as schema

I have a query that I want to be able to use across database schemas. Right now it is written so that I need to replace the schema in several places of the query. How do I set that up as a variable so that all I need to do is change it in one place?

Upvotes: 0

Views: 2854

Answers (3)

Trojan.ZBOT
Trojan.ZBOT

Reputation: 1488

You can use dynamic SQL. Here is an example - 

DECLARE @Column varchar(25)
DECLARE @sqlStmt varchar(max)
SET @Column = 'MyColumn' 
SET @sqlStmt = N'SELECT ' + @Column + ' FROM MyTable'
EXEC (@sqlStmt)

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You can do this with Dynamic SQL:

DECLARE @sql VARCHAR(MAX)
       ,@schema VARCHAR(255) = 'dbo'
SET @sql = 'SELECT *
            FROM '+@schema+'.yourTable
           '
EXEC (@sql)

You could use this in a cursor to loop through schema's:

DECLARE @Iterator varchar(255)
        ,@strSQL varchar(MAX) 
DECLARE xyz CURSOR
FOR
--Select stuff to iterate over
        SELECT name
        FROM  sys.schemas
OPEN xyz 
FETCH NEXT FROM xyz 
INTO @Iterator 
WHILE @@FETCH_STATUS = 0
BEGIN
--Do stuff
SET @strSQL = 'SELECT *
                FROM '+@Iterator+'.yourTable
               '
Exec (@strSQL)
    FETCH NEXT FROM xyz 
    INTO @Iterator 
END
CLOSE xyz 
DEALLOCATE xyz 
GO

To test your dynamic SQL statements, you can change EXEC to PRINT and ensure that the resulting query is as you intended.

Upvotes: 2

bjnr
bjnr

Reputation: 3437

Synonyms might be the answer to your question.

Upvotes: 0

Related Questions