Referencing a system function from a DB with a lower compatibility level

Question

I need to be able to parse a string array with STRING_SPLIT on a SQL 2016 instance. The vendor has required the one user DB we have access to be set with a compatibility level of 100 while the system DBs are at 130. Can STRING_SPLIT and other system functions be referenced when the context is set to the 100-level DB?

Answer 1

Since master has the correct compatibility level, you can always execute STRING_SPLIT there.

So we can create inline TVF functions to execute it, then mark them as system functions so you can access them anywhere.

USE master;
GO

CREATE FUNCTION STRING_SPLITV
(@string varchar(max), @separator char(1))
RETURNS TABLE AS RETURN

SELECT value FROM STRING_SPLIT(@string, @separator);
GO

CREATE FUNCTION STRING_SPLITN
(@string nvarchar(max), @separator nchar(1))
RETURNS TABLE AS RETURN

SELECT value FROM STRING_SPLIT(@string, @separator);
GO

EXEC sp_ms_marksystemobject 'STRING_SPLITV';
EXEC sp_ms_marksystemobject 'STRING_SPLITN';

Note that sp_ms_marksystemobject is an undocumented system procedure, use at your own risk.

You can now use these functions in every database without schema-qualifying

USE YourDB;
GO

SELECT * FROM STRING_SPLITV('a,b,c', ',');

If you do not use sp_ms_marksystemobject you will still have access, but must qualify it with database and schema. If you have no access to master on a regular basis, perhaps create it in model, then it will appear in tempdb on restart.

Note: This is a copy of a similar question and answer by me from Stack Overflow