Powershell - problems changing from foreach loop to foreach-object

Question

question

jas01101 asked Sep 4, '19

Powershell - problems changing from foreach loop to foreach-object

I have a script that has been running perfectly for five years. It scripts out database objects. Upon moving to a new server - from 2012 to 2016 - and from Powershell version 4 to version 5, it has begun to consume massive amounts of memory. This happens when it is scripting large numbers of tables or views. I am trying to convert the script from using the Foreach loop to using the Foreach-object.

The problem is that I cannot make the script statement work.

I can tell that with the foreach loop, $tbl returns a set of data and I can script it with this statement: $scrp.Script($tbl)

Schema Name Row Count Storage Index Created Space Used Space Used

dbo SomeTable 2508 152.00 KB 152.00 KB 4/2/2010 5:34 PM

With the foreach-object, I seem to need to specify each property I'll need later in my script:

$db.Tables | foreach-object {$tbl = $_.name; $scm =$_.Schema; $sys = $_.IsSystemObject; $crp = $_.IsEncrypted}...

$scrp.Script($tbl) no longer works because the $tbl variable only contains the name of the table. I've tried $scm + '.' + $tbl and that also doesn't work.

Can somebody tell me how to script out my tables?

Thanks in advance.

Judy

powershell programming foreach-loop

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-09-04T18:05:43Z

jas01101 answered Sep 4, '19

I will answer my own question. In the cold light of day, I realized that $_ (no dot) means the whole collection and that is what I need to supply to the script object. It is working now. Yet to discover whether it will improve memory usage. I understand it may run longer which isn't good but runaway memory usage isn't tolerable.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question