While it might seem strange or even impossible to work with objects in SQL it is possible. I stumbled upon this recently while I was investigating a way to automatically drop and recreate indexes on a SQL 2000 server.
DECLARE @object INT
EXEC sp_OACreate 'SQLDMO.SQLServer', @object OUT
EXEC sp_OASetProperty @object, 'LoginSecure', TRUE
With this statement an object is created and the reference to it is stored as an integer in @object. We also set the LoginSecure property to true. We can get and set properties and call methods of the object with the sp_OA..... functions
SET @CmdStr = 'Connect('+@@SERVERNAME+')'
EXEC sp_OAMethod @object,@CmdStr
With this statement the Connect function on the object is called with the servername of the server you're running the script on as the parameter. After connecting it's possible to approach objects on the SQL server, this might not seem very usefull yet as SQL is the language to approach objects on a database server. However it is usefull in getting object definitions.
SET @CmdStr = 'Databases("testdatabase").Tables("table1").Indexes("index1").Script(5)'
EXEC sp_OAMethod @object, @CmdStr
Now it gets a little more interesting, we approach the object named index1 in the collection indexes in the collection of tables in the collection of databases and call the Script() method on it. The number 5 is a bitpattern that indicates the objects and the deletion of the object if it exists need to be scripted.
I wish I had some more time to dig into this, I am sure there are more gems to be found in this mechanism.