title: How to create a UDF or View in another database that references the correct sys.objects table in the caller?
body: <p>Using SQL Server 2008, I'd like to create a UDF that gives me the create date of an object.  This is the code:</p>
  
  <pre><code>create function dbo.GetObjCreateDate(@objName sysname) returns datetime as
  begin
      declare @result datetime
      select @result = create_date from sys.objects where name = @objname
      return @result
  end
  go
  </code></pre>
  
  <p>I'd like to put this UDF in the master database or some other shared database so that it is accessible from anywhere, except that if I do that then the <code>sys.objects</code> reference pulls from the <code>master</code> database instead of the database that I'm initiating my query from.  I know you can do this as the <code>information_schema</code> views sit in <code>master</code> and just wrap calls to local instances of sys.objects, so I'm hoping there's a simple way to do that with my UDF as well.</p>