Dynamic change schema in SQL procedure

Question

I have a database with multiple schemas. In every schema I got table called [Logs], so my database tables looks like:

[s1].[Logs]
[s2].[Logs]
[s3].[Logs]
...
[sN].[Logs]

Every day I would like to run stored procedure, which will do same operations on every above table. Is there a way to pass schema name into stored procedure? I am using SQL on Azure.

[This answer](http://stackoverflow.com/a/1570121/1048425) could be of some use to you. — GarethD, Mar 10 '14 at 16:31

score 0 · Answer 1 · answered Mar 10 '14 at 15:56

0

No, it is not - unless the SP Uses then dynamic SQL to execute some SQL String you constructed in the SP.

This happens via the sp_executesql stored procedure

http://technet.microsoft.com/en-us/library/ms188001.aspx

has more information.

answered Mar 10 '14 at 15:56

TomTom

61,059
10
88
148

score 0 · Answer 2 · answered Mar 10 '14 at 16:28

Microsoft has a few undocumented procedures that perform "foreach" operations on tables (sp_msforeachtable) and databases (sp_msforeachdb). Both of these rely on another undocumented proc called sp_msforeachworker which you might be able to exploit to create a foreachschema type of routine. Theres an article (reg required) here that demonstrates this approach.

That said, its unlikely Azure supports anything of these, so you might have to fashion your own using a crude loop:

declare @schemas table (i int identity(1,1), name sysname);
insert into @schemas
    select name from sys.schemas where name like 's[0-9]%';

declare @i int, @name sysname, @cmd nvarchar(max);
select @i = min(i) from @schemas;

while @i is not null
begin

    select @name = name from @schemas where i = @i;

    set @cmd = replace(N'select count(*) from [{0}].[Logs];', '{0}', @name);
    print @cmd;

    --exec(@cmd);
    select @i = min(i) from @schemas where i > @i;
end

Dynamic change schema in SQL procedure

2 Answers2