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.
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.
- I have to stick to table valued function only and the above use case is a sample scenario to explain my problem.