Easiest way to run one stored procedures for all schemas in a database

Question

I have a database which contains several schema including default(dbo) for different company.
My question is, how I run one stored procedures for all schema by changing the schema name in "ALTER PROCEDURE" section.
Other wise I have to run the stored procedure separately for all schemas

Please help

Thanks in Advance

Answer 1

This is something you would orchestrate from outside SQL Server with Powershell, Python, Perl or whatever you fancy. The tool would have to read the file and add the schema name before sending it to SQL Server.

Answer 2

You can use a cursor to loop through / some schemas:

DECLARE @sp nvarchar(128) = 'uspName'  -- modify the name
DECLARE @schema nvarchar(128);
DECLARE @sql nvarchar(500);

DECLARE cur CURSOR LOCAL FOR
    SELECT QUOTENAME(name)
    FROM sys.schemas
    WHERE NOT name IN ('sys', 'guest', 'INFORMATION_SCHEMA', 'OrOthersToExclude')
          AND NOT LEFT(name, 3) = 'db_';  -- exclude database roles

OPEN cur;
FETCH NEXT FROM cur INTO @schema;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- For testing
    SET @sql = 'EXEC ' + @schema + '.' + @sp
    PRINT @sql;

    -- If fine, uncomment this
    --EXEC sp_executesql @sql;

    FETCH NEXT FROM cur INTO @schema;
END

CLOSE cur;
DEALLOCATE cur;

Answer 3

Having duplicate tables/procs per company is not a good way to handle multiple "tenants".

Each proc is unique to the schema. There is no way built in way to update every proc for every schema. You could write "dynamic sql" and change the schema name in your code.

Answer 4

DECLARE @updatedDefinition nvarchar(max) = N'';
SELECT @updatedDefinition = m.definition 
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o ON o.object_id = m.object_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE s.name = 'dbo' AND o.type = 'p' AND OBJECT_NAME(m.object_id) = 'UPDATED_SP';

DECLARE @definition nvarchar(max) = N'';
DECLARE @schema sysname;
DECLARE @replaceString varchar(128) = '';

DECLARE schema_cursor CURSOR FOR
SELECT name FROM sys.schemas WHERE name IN ('company1', 'company2', 'company3', ...);
OPEN schema_cursor;
FETCH NEXT FROM schema_cursor INTO @schema;

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @replaceString = 'ALTER PROCEDURE [' + @schema + ']';
    SET @definition = REPLACE(@updatedDefinition, 'CREATE PROCEDURE [dbo]', @replaceString);

    EXEC @definition;

    FETCH NEXT FROM schema_cursor INTO @schema;
END
CLOSE schema_cursor;
DEALLOCATE schema_cursor;

Answer 5

When you say "run this procedure", do you mean that you want to execute it (which is the normal meaning of "run"), or do you mean that you want to make the same modification in all schemas (which fits with that you have modified some logic).

If you only want to execute ItemEdit or wharever, Olaf's solution should do it for you.

If you want to modify the procedure in all schemas, you are asking the wrong question. In difference to Tom, I think one-schema-per-tenant is a viable multi-tenant solution. But as Tom points out, there is no built-in solution in SQL Server for this, but you need to cater for this yourself. I get a little worried that you appear to have embarked on such a solution with any such planning. Or did your co-workers just not tell you?

The correct way of working is that you change the stored procedure in source control. You develop and test on your sandbox. Then you should have tooling in place that deploys the changes from source to control to the production and QA databases, and these tools should know how to load the procedure to all company schemas. That is nothing you as an individual developer should have to care about.