I'm starting to use Powershell for all my administrative tasks, overnight backups & maintenance, and I'm wondering if anyone has scripted any DBCC functions in Powershell. From what I can see there is no method that corresponds to CheckDB, so I'm doing the following nightly

    $db.checkallocations(0)
    $db.checktables(0)
    $db.checkcatalog()

and the following weekly

    $db.updateindexstatistics()

I can't see anything around index refreshes.

Any suggestions or comments would be appreciated.

I have a function that has a number of SMO functions defined in it.  Maybe you use the same one.  One of the functions, Get-SqlDatabase, has a tables collection and for each table there is a method, RebuildIndexes.  So grab the tables collection, then iterate through that collection by:

foreach ($t in $dbt) {$t.RebuildIndexes(90)}

assuming that $dbt is your tables collection.  Works like a charm.

Let me know if you need more info.

David

 

Fantastic. Thanks. That's exactly what I was after. Do you know how to check the level of index fragmentation using Powershell?

Also, wondering if you have any insight into the following

When I run CheckTables(0) on one of my databases I get the following error:

Exception calling "CheckTables" with "1" argument(s): "Check tables failed for Database 'prd0Sylvan'. "
At line:1 char:16
+ $db.CheckTables( <<<< 0)

But if I run

   $tbls=$db.tables
    foreach ($tbl in $tbls) {$tbl.checktable()}

it works fine.