I am 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 value function without any schema prefixed in it like

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

As I havent’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 the select * FROM MyDb.India.getFestivals() and still it returns the festivals from dbo.festivals and not india.festivals. I undestand that though the synonymys 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 value 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

1. Is there a way I can have a table value function that can query based on the schema context.
2. the only possible way I can think of is to create the same TableValue function in all schemas

Caveats

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