Databases
StackOverflow2013
Postsdbo
POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmattmc3
    1. USmattmc3
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
      1. USmattmc3
      1. VTBountyStart
    3. VO
      singulars
      1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe `information_schema` views are created locally in each database. Also, as a general rule, the Master DB should be left alone. Create a "Utilities" database to hold things like this instead.
      singulars
      1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
      1. USJoe Stefanelli
    2. COYou can see this for yourself. In SSMS, drill down through Databases -> YourDatabase -> Views -> System Views.
      singulars
      1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
      1. USJoe Stefanelli
    3. COThat won't work. The UDF in Utilities would just access the sys.objects in Utilities instead of the calling database. Ignore the master part if that's your hangup... I need the sys.objects from the caller, not the db where the UDF is hosted.
      singulars
      1. POHow to create a UDF or View in another database that references the correct sys.objects table in the caller?
      1. USmattmc3
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload