On running "objSQLDMODatabase.StoredProcedures.ItemByID" statement, SQL-DMO executes this query against SQL database:
select o.name, user_name(o.uid), o.crdate, xtype=convert(nchar(2), o.xtype), o.id, OBJECTPROPERTY(o.id, N'ExecIsStartup'), OBJECTPROPERTY(o.id, N'ExecIsQuotedIdentOn'), OBJECTPROPERTY(o.id, N'ExecIsAnsiNullsOn'), OBJECTPROPERTY(o.id, N'IsMSShipped') from dbo.sysobjects o where (OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1) and o.name not like N'#%%' order by o.name
But this query returns _ALL_ database stored procedures! And on a large database it takes 43000 reads and 2 seconds to finish! What I suspect is that SQL-DMO gets a list of ALL database stored procedures and than it finds the row for a given ID. It's like I was using "SELECT * FROM Table", and than For Each row_in_resultset CheckIf ID = GivenID, instead of performing a SELECT * FROM Table WHERE ID = GivenID.
Is there any workaround to this...? As I stated before, I need to access a particular database object SQL-DMO representation for scripting, getting a list of avaliable properties etc. If there is no workaround, I would need to give up SQL-DMO and implement my own queries to do what I need...