Is there a way to get object name

Question

Is there a way to get object name

Mark Shvarts 1

SSCertifiable

Points: 5702
More actions
November 1, 2007 at 9:40 am

#179773

For example i am working with sys.dm_exec_sql_text function. It returns, among others, dbid and object_id.
I can get object_name only if I am in a particular database now. But is there a way to get it accross all databases ? Maybe this function will be enhanced in 2008 ?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 1

The only option I can think of is to do a bunch of LEFT OUTER JOINS to db1.sys.objects, db2.sys.objects, etc.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

Dynamic SQL maybe? I would propably make a function that took the DBid and the ObjectID and return the name using it.

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 3

Yes, I am also trying to create a dynamic function, but how to return the value from dynamic execution to a variable which is "non-dynamic", declared in the functiuon, not in its dynamic script ?

Here is my code, just in case

if object_id ('dbo.all_object_id', 'fn') is not null

drop function dbo.all_object_id

go

create function dbo.all_object_id

(

@dbid int,

@object_id int

)

returns sysname

as

-- select dbo.all_object_id(25,1264827668)

begin

declare

@db_name sysname,

@stmt varchar(max),

@object_name sysname

--set @dbid=25

--set @object_id = 1264827668

select @db_name = db_name(@dbid)

--print @db_name

set @stmt =

'use ' + @db_name + ';

select object_name(' + cast(@object_id as varchar(max)) + ');'

--print @stmt

--execute(@stmt)

end

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

if you are only selecting objects in the current database, you can use object_name(object_id) in your select list

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me