March 11, 2009 at 4:00 pm
Is there anything like object_name() for SMO, that let's you retrieve an SMO object using a name or ID? Currently, the only way I know how to retrieve an object, is to iterate over all object (e.g. db.StoredProcedures + db.Views + db.Tables + etc) and check the Name. I've been searching through MSDN, but I can't find a better way.
March 11, 2009 at 4:17 pm
See if this helps. If it does please post back so others may benefit.
'Display all the partition functions and schemes on the database.
Dim pf As PartitionFunction
For Each pf In db.PartitionFunctions
Console.WriteLine(pf.Name)
Next
Dim ps As PartitionScheme
For Each ps In db.PartitionSchemes
Console.WriteLine(ps.Name)
Next
March 11, 2009 at 10:40 pm
cspencer (3/11/2009)
Is there anything like object_name() for SMO, that let's you retrieve an SMO object using a name or ID? Currently, the only way I know how to retrieve an object, is to iterate over all object (e.g. db.StoredProcedures + db.Views + db.Tables + etc) and check the Name. I've been searching through MSDN, but I can't find a better way.
What you are looking for is the URN and the GetByURN methods.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 7:15 am
Is there any documentation on GetByURN?
I did a Google search, but apparently the only page on the net that uses that term is this thread 🙂
March 12, 2009 at 7:27 am
I was going from memory and probably misremembered its name. Hang on, I'll look it up in my code...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 7:50 am
OK, it's "Server.GetSMOObject( URN )" to return any object in the SQL Server Instance by its URN. Details are in books online.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 7:52 am
And, as long as we are on the subject, here is a VB.net function that I wrote that generates URN strings for you. I find it very handy for this kind of thing:
Public Shared Function URNString(ByVal Server As String, ByVal DB As String _
, Optional ByVal Type As String = "", Optional ByVal Name As String = "" _
, Optional ByVal Schema As String = "dbo") As String
'Constructs a valid SqlServer URN based on the parameters, from which
' an(Smo.Urn) can be created.
'NOTE: Valid values for Types are(?), in dependency order:
'
' Logins
' Databases
' FileGroups
' DataFiles
' LogFiles
' Users *
' LinkedServers
' UserDefinedDataTypes *
' Tables *
' Columns
' Views *
' UserDefinedFunctions *
' ForeignKeys *
' StoredProcedures *
' Triggers *
' Jobs
' * - indicates a database-level object
Dim str As String = "Server[@Name='" & Server & "']/Database[@Name='" & DB & "']"
If Type <> "" And Name <> "" Then
If Left(Type, 4) = "SEC." Then
'security principals
'sql objects
Select Case Right(Trim(Type), 1)
Case "U", "S" : Type = "User"
Case "R" : Type = "Role"
End Select
str = str & "/" & Type & "[@Name='" & Name & "']"
Else
'sql objects
Select Case Trim(Type)
Case "U" : Type = "Table"
Case "V" : Type = "View"
Case "AF", "FN", "FS", "FT", "IF", "TF" : Type = "UserDefinedFunction"
Case "F" : Type = "ForeignKey"
Case "P", "PC" : Type = "StoredProcedure"
Case "TR", "TA" : Type = "Trigger"
'Case "SQ", "RF", "X", "IT" : Type = "QUIT_" & Type
Case "SN" : Type = "Synonym"
End Select
str = str & "/" & Type & "[@Name='" & Name & "' and @Schema='" & Schema & "']"
End If
End If
Return str
End Function
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 8:57 am
Thanks, that's an enormous help!
March 12, 2009 at 11:44 am
Is it possible to construct an URN that can query by name, regardless of type?
I tried these, for finding the MyProc, but none of them worked:
//[@Name='MyProc' and @Schema='dbo']
//.[@Name='MyProc' and @Schema='dbo']
//*[@Name='MyProc' and @Schema='dbo']
//node()[@Name='MyProc' and @Schema='dbo']
Regards,
Chris
March 12, 2009 at 1:09 pm
You didn't include the Server & DB names in your list of examples, I am pretty sure that you need to include those in every URN.
AFAIK, you cannot skip the TYPE in the URN specifier because there is no single Namespace in a SQL Server database. For instance a User, a UDT and a Table can all be named "Bob"
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2009 at 1:30 pm
Ah, ok, thanks for the clarification.
March 12, 2009 at 1:37 pm
If you need to browse for an object or get the objects type, then I would just check the sys.objects table. That's the easiest and most efficient way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2009 at 12:04 pm
So how did this work out for you?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 7:17 am
So so. While sysobjects does contain the name of most objects (the exception being indexes), it doesn't directly contain the parent object. I need to do another join using parent_object_id to get the parent's name and type, so I'll be able to construct a valid URN. It's very awkward, but it gets the job done.
March 30, 2009 at 8:11 am
cspencer (3/30/2009)
So so. While sysobjects does contain the name of most objects (the exception being indexes), it doesn't directly contain the parent object. I need to do another join using parent_object_id to get the parent's name and type, so I'll be able to construct a valid URN. It's very awkward, but it gets the job done.
Actually there are a lot of things that are not in sysobjects (security objects, service broker objects, etc.). So much so, that I eventually just made a different query for each different object type.
The parent thing I don't remember coming up. Can you give an example?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply