Example illustrating SMO enumeration needed.

I planned to write a VB.NET SMO app enumerating all tables in a given database, all fields in each table, the properties (length, NULL, PK, etc) of each field, and finally store the result in a table. I think that should be quite doable.

But I noticed that the ForEach Loop editor has a SMO enumerator. (I don't know if it can be used to do exactly what I want.) By clicking on the options in the ForEach Loop editor I can get the Enumerator as follows:

Database[@.Name='AdventureWorks']/Table[@.Name='AWBuildVersion' and @.Schema='dbo']/SMOEnumObj[@.Name='Columns']/SMOEnumType[@.Name='Names']

but it's not clear to me how to work with this thing! ( It must return a collection of items that I access through a variable.) Anyone know of an example illustrating SMO enumeration?

TIA,

Barkingdog

The SMO enumerator returns SMO objects, URNs or Names.

So you have the option of dealing directly with the SMO object through which you can do many useful things, the URN with which you can create a new object through SMO and work with it, or just get the name of the object, like for a table etc. that you can use in say, an SQL task to do certain types of processing for a table, database or server.

For what you want to do, yes, this would be quite simple.

Depending on how you want to do it, you could use a little script or get fancy and use multiple foreach loops.

Script route. Use the Tables SMO objects and then traverse each table object in script and retrieve the other information you want from the table object including column names etc.

Multiple foreach loops, enumerate the tables in the first loop, then in a subsequent or recursive (inside the first one) loop use the name property on the SMO table object to retrieve the other information.

This is exactly the kind of thing the SMO Enum was built for.

K