Sql-server – Common TVF in SQL server to get results from differnt schema

schemasql serversql-server-2012

I have been using SQL Server for past one month and I need a suggestion from SQL Server folks to help me on this use case.

The tables below are just to explain about the idea that I am looking for.

I have tables in different schema like

MyDb.dbo.Festivals
MyDb.India.Festivals
MyDb.China.Festivals
MyDb.USA.Festivals

I am writing a table valued function without any schema prefixed in it like

CREATE FUNCTION getFestivals()
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM festivals
)

As I haven't applied any schema, it defaults to dbo and creates the TVF as dbo.getFestivals(). Now I have created synonyms for all other schemas

CREATE SYNONYM India.getFestivals  FOR dbo.getFestivals; 
CREATE SYNONYM USA.getFestivals  FOR dbo.getFestivals;

I tried to query like select * FROM MyDb.India.getFestivals() and still it returns the festivals from dbo.festivals and not india.festivals. I understand that though the synonyms were created it just executes the select query in the dbo schema context and not in india schema context.

I want suggestions on how to have a common table valued function that will query based on the schema prefixed, i.e. MyDB.India.getFestivals() should get festivals from India and MyDB.USA.getFestivals() should return festivals from USA.

Question

Is there a way I can have a table valued function that can query based on the schema context?

The only possible way I can think of is to create the same table valued function in all schemas.

Caveats

  1. I have to stick to table valued function only and the above use case is a sample scenario to explain my problem.

Best Answer

Is there a way I can have a table valued function that can query based on the schema context?

No. The TVF is bound to the table at the time of creation, not at execute time, so you won't be able to dynamically access different tables from a single function.

The only possible way I can think of is to create the same table valued function in all schemas.

That would be the cleanest method. Another approach is to create a UNION ALL view like the example below and pass the desired schema as a parameter. This will not perform as well since all the referenced tables will need to be scanned.

CREATE VIEW dbo.vw_Festivals
AS
          SELECT 'dbo' AS SchemaName, Col1, Col2 FROM MyDb.dbo.festivals
UNION ALL SELECT 'India' AS SchemaName, Col1, Col2 FROM MyDb.India.festivals
UNION ALL SELECT 'China' AS SchemaName, Col1, Col2 FROM MyDb.China.festivals
UNION ALL SELECT 'USA' AS SchemaName, Col1, Col2 FROM MyDb.USA.festivals;
GO

CREATE FUNCTION getFestivals(@SchemaName sysname)
RETURNS TABLE
AS
RETURN
(
    SELECT Col1, Col2
    FROM vw_Festivals
    WHERE SchemaName = @SchemaName
);
GO
Related Question