Copy big procedure between schemas

Question

I have a schema where I have defined procedures. This procedures can be modified and should be copied between all tenants (we have schema-based tenancy). The main idea is to have some sort of templated procedures which will be automatically updated through the other tenant's schemas after procedures had been modified. To write this 'copying' procedure I wrote something like this:

DECLARE 
    @proc_name NVARCHAR(150) = 'ProcedureToCopy',
    @proc_sql NVARCHAR(MAX),
    @schema_name NVARCHAR(MAX) = 'test';

SELECT 
    @proc_sql = REPLACE(ROUTINE_DEFINITION, '[templates].', '['+@schema_name+'].')
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
    ROUTINE_TYPE='PROCEDURE' AND
    ROUTINE_NAME = @proc_name AND
    ROUTINE_SCHEMA = 'templates'

PRINT(@proc_sql);

EXEC(@proc_sql);

But I have faced an issue with big procedures (more than 17k symbols). It can not be copied because NVARCHAR(MAX) is limited with 4k symbols, so my @proc_sql is trimmed and doesn't contain all required procedure definition.

Can anybody suggest a solution to my problem or maybe another approach?

nvarchar(max) can store up to 2GB of data (1 billion double byte characters). Your issue might be caused by an implicit type conversion somewhere. See https://stackoverflow.com/questions/12097670/how-to-store-a-string-var-greater-than-varcharmax — RSax, Aug 29 '19 at 19:01

score 5 · Accepted Answer · answered Aug 29 '19 at 18:53

You should be able to use this instead of INFORMATION_SCHEMA.ROUTINES.

SELECT o.name, definition, LEN(definition), o.type_desc 
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
    ON m.object_id = o.object_id

Using your example:

DECLARE @proc_name NVARCHAR(150) = 'doSomething',
    @proc_sql NVARCHAR(MAX),
    @schema_name NVARCHAR(MAX) = 'dbo';

SELECT  @proc_sql = REPLACE(m.definition, '[templates].', '[' + @schema_name + '].')
--select *
FROM    sys.sql_modules m
    INNER JOIN sys.objects o
        ON m.object_id = o.object_id
WHERE
    o.type_desc = 'sql_stored_Procedure'
    AND o.name = @proc_name
    AND SCHEMA_NAME(o.schema_id) = @schema_name

PRINT (@proc_sql);

EXEC (@proc_sql);

YES! It did a trick! Wonder what the difference between INFORMATION_SCHEMA and sys.sql_modules ... — dantey89, Aug 29 '19 at 19:02
I don't know specifically, but I have run in to this issue in the past. The INFORMATION_SCHEMA objects are just views against the raw tables. You can view the source code by running this: EXEC sp_helptext 'information_Schema.routines' — Jordan Ryder, Aug 29 '19 at 19:07

Luis Cazares · Answer 2 · 2019-08-29T19:07:05.203

The problem is not with NVARCHAR(MAX). NVARCHAR(MAX) will allow up to 2GB of information. That's probably more than any developer will code.

The problem is that the INFORMATION_SCHEMA view is limited to NVARCHAR(4000) and that's where the truncation occurs. You should try to use system views for all important queries.

DECLARE 
    @proc_name nvarchar(150) = 'ProcedureToCopy',
    @proc_sql nvarchar(MAX),
    @schema_name nvarchar(MAX) = 'test';

SELECT @proc_sql = REPLACE(m.definition, N'[templates].', QUOTENAME( @schema_name)+N'.')
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
    o.type_desc = 'SQL_STORED_PROCEDURE' AND
    o.name = @proc_name AND
    s.name = 'templates';

--PRINT(@proc_sql);

EXECUTE sp_executesql @proc_sql;

Copy big procedure between schemas

2 Answers2