title: How to use dynamic schema in Select Query
body: <p>I have one function dbo.test. Please find the function below:</p>
  
  <pre><code>Alter Function dbo.Test  
  (  
  
  )  
  returns int  
  As  
  Begin  
  Declare  @a int  
  Select top 1 @a = Id from CustomFieldMaster 
  return @a  
  End
  </code></pre>
  
  <p>I am executeing the above function in my Database <strong>Test</strong>. The interesting thing is this that this database has schema <strong>Test</strong> not default <strong>dbo</strong> means the tables are like <strong>Test.CustomFieldMaster</strong>. </p>
  
  <p>When I call my above function inside <strong>Test</strong> db, it gives me below error:</p>
  
  <p><em>Invalid object name 'CustomFieldMaster'</em></p>
  
  <p>I know the above function give me this erorr because it is trying to find <strong>dbo.CustomFieldMaster</strong> table instead of <strong>Test.CustomFieldMaster</strong>.</p>
  
  <p>But I can't use Test schema as I have many databases with different schemas, so in future if I design a SP with many tables and want to execute that SP in every db than I have to manually change the schema name before executing that SP for every DB.</p>
  
  <p>I want some keyword that dynamically picks the defauly schema of the current login user or current user as my schema name is same as User Name.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <pre><code>Select Current_User
  </code></pre>
  
  <p>The above will give me the user name, but I don't want dynamic queries.</p>
  
  <p>Any suggestions please.</p>
  
  <p><strong>ANY QUICK HELP</strong></p>