none
doesnt return row counts

    Question

  •  This script doesn't return the table with row counts.

    Param 
    ( 
         [Parameter(Mandatory = $true,position = 0)] [String]$ServerName, 
         [Parameter(Mandatory = $true,position = 1)] [String]$DatabaseName 
    ) 
     
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
    $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($ServerName) 
    $db = $srv.Databases.Item($DatabaseName) 
     
    foreach ($table in $db.Tables)  
    { 
       Write-Host $table.Name  "contains" $table.RowCount                "rows." 
    }

    I don't see the table name or rows.

    any help why?

    Thanks

    Thursday, July 12, 2018 8:11 PM

Answers

  • All you need to do is learn PowerShell and all of this would be easy.

    function Get-DBTables{
        Param( 
            [Parameter(Mandatory)]
            [String]$ServerName, 
            [Parameter(Mandatory)]
            [String]$DatabaseName
        ) 
    
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
        $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($ServerName) 
        $db = $srv.Databases.Item($DatabaseName)     
        $db.Tables
    }
    Get-DbTables server1 master | Where Name -like 'spt*' | select Name, RowCountAsDouble
    Get-DbTables server2 master | Where Name -like 'spt*' | select Name, RowCountAsDouble
    
    


    \_(ツ)_/

    • Marked as answer by hart60 Friday, July 13, 2018 1:44 AM
    Friday, July 13, 2018 1:22 AM
    Moderator

All replies

  • Works fine for me:

    function Get-TableRowCount{
        Param( 
            [Parameter(Mandatory)]
            [String]$ServerName, 
            [Parameter(Mandatory)]
            [String]$DatabaseName
        ) 
    
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
        $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($ServerName) 
        $db = $srv.Databases.Item($DatabaseName) 
         
        foreach ($table in $db.Tables){
           $msg = '{0} contains {1} rows.' -f $table.Name, $table.RowCount
           Write-Host $msg -fore green
        }
    }
    
    Get-TableRowCount alpha master
    
    "Position" is automatic by order declared.  "Mandatory" is true when present,.  This makes the definitions more readable.


    \_(ツ)_/

    Thursday, July 12, 2018 8:53 PM
    Moderator
  •  Yep its reporting the numbers now.  thanks.. would it be easy to turn this into
    a lookup against 2 different servers same database name and compare record counts
    on like tables names

     server1
     server2
    db:sales

     server1.sales.dbo.invoices 300 server2.sales.dbo.invoices 450

     Thanks.

    Friday, July 13, 2018 12:44 AM

  • Get-TableRowCount server1 master
    Get-TableRowCountserver2 master

    \_(ツ)_/

    Friday, July 13, 2018 12:46 AM
    Moderator
  • Was trying to create a small report from the script that shows table counts for
    like table names only without having to copying the data to xcel to verify.

     Thanks.
    Friday, July 13, 2018 1:10 AM
  • Was trying to create a small report from the script that shows table counts for
    like table names only without having to copying the data to xcel to verify.

     Thanks.

    ???? What does that mean?  Just output it using Export-Csv after editing the code to return objects.

    \_(ツ)_/

    Friday, July 13, 2018 1:13 AM
    Moderator
  • All you need to do is learn PowerShell and all of this would be easy.

    function Get-DBTables{
        Param( 
            [Parameter(Mandatory)]
            [String]$ServerName, 
            [Parameter(Mandatory)]
            [String]$DatabaseName
        ) 
    
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
        $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($ServerName) 
        $db = $srv.Databases.Item($DatabaseName)     
        $db.Tables
    }
    Get-DbTables server1 master | Where Name -like 'spt*' | select Name, RowCountAsDouble
    Get-DbTables server2 master | Where Name -like 'spt*' | select Name, RowCountAsDouble
    
    


    \_(ツ)_/

    • Marked as answer by hart60 Friday, July 13, 2018 1:44 AM
    Friday, July 13, 2018 1:22 AM
    Moderator
  •  Many thanks.

     

    Friday, July 13, 2018 1:44 AM
  • Or like this:

    $servers = 'Server1','server2','server3'
    $servers | 
        ForEach-Object{
            Get-DbTables $_ master
        } |
        Where Name -like 'spt*' |
        Select @{n='Server';e={$_.Parent.Parent.Name}},Name, RowCountAsDouble

    Output is:

    Server   Name             RowCountAsDouble
    ------   ----             ----------------
    server1  spt_fallback_db                 0
    server1  spt_fallback_dev                0
    server1  spt_fallback_usg                0
    server1  spt_monitor                     1
    server2  spt_fallback_db                 0
    server2  spt_fallback_dev                0
    server2  spt_fallback_usg                0
    server2  spt_monitor                     1
    server3  spt_fallback_db                 0
    server3  spt_fallback_dev                0
    server3  spt_fallback_usg                0
    server3  spt_monitor                     1


    \_(ツ)_/

    Friday, July 13, 2018 2:08 AM
    Moderator