CODEWITHSUNDEEP
sql-serversql-server-2012smo
Roman Pokrovskij
Roman Pokrovskij

Reputation: 9776

How to get SMO object (e.g Table) from SQl SERVER object_id?

How to get SMO object (e.g Table) from sql server object_id?

I see that it is possible to pop up SMO object by URN (Server.GetSmoObject method). But then I got another question: how to convert object_id to URN?

Upvotes: 2

Views: 2181

Answers (1)

Jaques
Jaques

Reputation: 2287

I assume here that you are using C#, and with that your server variable is already connected etc.

If you know what object you are looking for (in the example a Table), try this.

Server.GetSmoObject(Server.Database["DBName"].Tables.ItemById(object_id).Urn);

Hope it helps

EDIT

Here is another way. It creates a Datatable which have 4 columns. ObjectsType, Schema, Name and Urn

DataTable table = server.Databases["DBName"].EnumObjects();
foreach (DataRow item in table.Rows)
{
    if(item[2].ToString() == "Companies" && item[0].ToString() == "Table")
    {
        server.GetSmoObject(item[3].ToString());
    }
}

But if you want to create a Urn in SQL Code you can do something like

Select '"Server[@Name=''' + @@SERVERNAME + ''']/Database[@Name=''' + DB_Name() + '''/Table[@Name=''' + OBJECT_NAME(117575457) + ''']"'

Upvotes: 4

Related Questions