how can I get the object_name

Hi,
how can I get the object_name from a given dbid?
I think the only solution is to use dynamic sql with cursors because I can
not run dynamic sql inside a UDF...
can you help me, please?
create table dbo.objects (
dbid smallint
, objectid int
)
go
insert into dbo.objects select 1, 1
insert into dbo.objects select 1, 2
insert into dbo.objects select 1, 6
insert into dbo.objects select 1, 8
insert into dbo.objects select 1, 9
insert into dbo.objects select 4, 1
insert into dbo.objects select 4, 2
insert into dbo.objects select 4, 3
go
select
dbid, objectid,
db_name(dbid) as dbname
-- , object_name (dbid, objectid) as object_name
from dbo.objects
Thank you!
ejrejr
What does that mean OBJECT_NAME of dbid? Have you looked in BOL?
"ejr" <namrek1@.hotmail.com> wrote in message
news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> how can I get the object_name from a given dbid?
> I think the only solution is to use dynamic sql with cursors because I can
> not run dynamic sql inside a UDF...
> can you help me, please?
> create table dbo.objects (
> dbid smallint
> , objectid int
> )
> go
> insert into dbo.objects select 1, 1
> insert into dbo.objects select 1, 2
> insert into dbo.objects select 1, 6
> insert into dbo.objects select 1, 8
> insert into dbo.objects select 1, 9
> insert into dbo.objects select 4, 1
> insert into dbo.objects select 4, 2
> insert into dbo.objects select 4, 3
> go
> select
> dbid, objectid,
> db_name(dbid) as dbname
> -- , object_name (dbid, objectid) as object_name
> from dbo.objects
> Thank you!
> --
> ejr
>|||object_name has only one parameter.
So you should use
object_name(objectid)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ejr" <namrek1@.hotmail.com> wrote in message
news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> how can I get the object_name from a given dbid?
> I think the only solution is to use dynamic sql with cursors because I can
> not run dynamic sql inside a UDF...
> can you help me, please?
> create table dbo.objects (
> dbid smallint
> , objectid int
> )
> go
> insert into dbo.objects select 1, 1
> insert into dbo.objects select 1, 2
> insert into dbo.objects select 1, 6
> insert into dbo.objects select 1, 8
> insert into dbo.objects select 1, 9
> insert into dbo.objects select 4, 1
> insert into dbo.objects select 4, 2
> insert into dbo.objects select 4, 3
> go
> select
> dbid, objectid,
> db_name(dbid) as dbname
> -- , object_name (dbid, objectid) as object_name
> from dbo.objects
> Thank you!
> --
> ejr
>|||Use the Information Schema to retrieve metadata wherever possible
rather than create your own catalogue.
If you need to store metadata yourself for any reason then use the
object name. Never store the object ID. The ID is fragile and may
change if the schema changes.
David Portas
SQL Server MVP
--|||yes,
if object_name accept dbid there is no problem at all; I commented the line
object_name (dbid, objectid) to have an idea what I wanted to do
I wanted to know if is possible to get an object name of any database from
an user table with a query like that...
Thank you,
--
ejr
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:Om2v#kmGFHA.3088@.tk2msftngp13.phx.gbl...
> object_name has only one parameter.
> So you should use
> object_name(objectid)
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "ejr" <namrek1@.hotmail.com> wrote in message
> news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
can
>|||ejr
I thint it is something like this
Declare @.Sql varchar(100)
while @.@.rowcount=0
begin
select @.sql='Use '+object_name(objectid) from objects
exec @.sql
select
dbid, objectid,
db_name(dbid) as dbname
, object_name (objectid) as object_name
from dbo.objects
end
Madhivanan|||Sorry It should be
Declare @.Sql varchar(100)
while @.@.rowcount=0
begin
select @.sql='Use '+db_name(dbid) from objects
exec @.sql
end
select
dbid, objectid,
db_name(dbid) as dbname
, object_name (objectid) as object_name
from dbo.objects
end
Madhivanan|||Madhavan,
Have you tested that?
Use <DbName> has no effect without Go.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109255172.695465.89630@.g14g2000cwa.googlegroups.com...
> Sorry It should be
>
> Declare @.Sql varchar(100)
> while @.@.rowcount=0
> begin
> select @.sql='Use '+db_name(dbid) from objects
> exec @.sql
> end
> select
> dbid, objectid,
> db_name(dbid) as dbname
> , object_name (objectid) as object_name
> from dbo.objects
> end
> Madhivanan
>