November 18, 2004 at 6:43 pm
Hi everyone, when I run the below query, I get different results based on which database I run it from. It returns results from the database where it is run from and not from the 'databaseX' database. Why is that?
select distinct object_name(id), objectproperty(id, 'ownerid')
from databaseX.dbo.sysobjects
WHERE xtype in ('u', 'fn', 'if', 'p', 'tf', 'tr', 'v', 'x')
order by 1
November 18, 2004 at 6:53 pm
The functions you are using (object_name and Objectproperty) always look in the current database. They are not aware of the database name in the FROM. You could try the following:
declare @sql Varchar (1000)
set @sql = 'use databaseX select distinct object_name(id), objectproperty(id, ''ownerid'')
from dbo.sysobjects
WHERE xtype in (''u'', ''fn'', ''if'', ''p'', ''tf'', ''tr'', ''v'', ''x'')
order by 1'
Exec (@SQL)
November 23, 2004 at 2:36 pm
Thanks Happy!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply