Description

This function will give you a list of all the tables from the selected database with their record and data space used counts. Also, it has a parameter “-ShowAllDatabases Y” you can use to only list all Databases in the selected SQL Server Instance name. There’s a help provided with this function and will load thru import, and later remove the SQLPS module.

Updated - 05/06/2011: Change the '-ShowDatabases' to be a type [Switch] so I won't use the "Y" as a value to get the dbs listed.

Updated - 05/12/2011: Add set-location go back to your "Home Drive"

 

To use this function:

PS Z:\YourScriptLocation>. .\Get-dbTableRecordCount.ps1

PS Z:\YourScriptLocation> Help Get-dbTableRecordCount -full


Requirements/Contribution:

1) PowerShell V2 - for loading the module at either the PowerShell Console, or usign the ISE.

2) Need SQL Server 2008 (or greater) installed.

3) Download the community SQLPS and/or SQLPSv2 modules from the following links:

         a) Download Chad Miller SQLPS module for SQL Server 2008 and 2008 R2:

                        http://sev17.com/2010/07/making-a-sqlps-module/

         b) Or, Download my version of SQLPSv2 for SQL Server ‘Denali’ CTP1:

                        http://www.maxtblog.com/2010/11/denali-get-your-sqlpsv2-module-set-to-go/

 

This is a fun function to study, and it has a lot of goodies in it.

Have fun scripting!!

Script

PowerShell
Edit|Remove
Function Get-dbTableRecordCount {  
    <#   
    .SYNOPSIS  
        List all tables record count, and DataSpaceUsed, of the database selected.   
      
    .Description   
        This function will give you the ability to get a list of all SQL Server table count for the   
        selected database.  Also, using the parameter ' -ListAllDatabases', will allow you to only list  
        all databases of the selected SQL Server.  It uses SQL Server 2008 (or above) SMO or the community   
        SQL Server 2008/Denali *SQLPS module installed in order to run. It also has the alias - 'gtrc'.  
          
    *Note: This function will load the SQLPS module, and remove it when done.  
      
    .Parameter  $SQLInstanceName  
        SQLInstanceName - [String].  All or "SQLInstanceName1,SQLInstanceName2,..." or "SQLInstanceName"  
  
    .Parameter $DatabaseName  
        DatabaseName - [String].  Enter Database name.  
      
    .Parameter $ListAllDatabases  
        ListAllDatabases - [Switch].  Use to list all databases only and it's ignore with other param.  
      
    .Example   
        get-dbTableRecordCount -SQLInstanceName 'SQLServerInstanceName' -DatabaseName 'Databasename'  
          
        Using the full function name with all paramaters.  
      
    .Example  
        get-dbTableRecordCount -SQLInstanceName 'SQLServerInstanceName' -ListAllDatabases   
          
        Use command to only list the Databases using the ListAllDatabases parameter.  
      
    .Example  
        gtrc -SQLInstanceName 'SQLServerInstanceName' -DatabaseName 'Databasename'   
          
        Using the full function name with all paramaters:  
    .Example  
        gtrc -SQLInstanceName 'SQLServerInstanceName' -ShowAllDatabases  
      
        or, use command to only list the Databases using the ShowAllDatabases parameter.  
      
    .Notes   
        NAME: get-dbTableRecordCount  
        Alias: gtrc  
        AUTHOR: Max Trinidad       
        Created:  04/14/2011 17:10:01 - Script Version 1 - This is an Advanced function.  
        Compatibility - Version 2  
        Requierement: SQL Server 2008 (or above) and have any SQLPS module installed.  
      
    .Link   
        about_functions   
        about_functions_advanced   
        about_functions_advanced_methods   
        about_functions_advanced_parameters   
      
    .Inputs  
        SQLInstanceName - [String] - All or "SQLServer" or "SQLServer\InstanceName".  
        DatabaseName - [String] - Enter Database name.  
        ShowAllDatabases - [Switch] - To only display a list of database name.  
      
    .Outputs  
        Return [Array] - System Array Type Object.  
      
    #>     
Param(  
        [Parameter(Mandatory=$True, Position=0)] [ValidateScript({$_ -ne $null})] [string] $SQLInstanceName,  
        [Parameter(Mandatory=$false, Position=1)] [ValidateScript({$_ -ne $null})][string] $DatabaseName,  
        [Parameter(Mandatory=$false, Position=2)] [switch$ShowAllDatabases  
)  
    ## - Here's the SQLPS import module      
    Import-Module SQLPSv2 -DisableNameChecking;   #for Denali or under  
    If($ShowAllDatabases.IsPresent -eq $false){  
        if($DatabaseName -ne $null){   
              If($SQLInstanceName -match '\\'){ 
                Write-Host "Processing SQLServer Instance: $SQLInstanceName, DatabaseName: $DatabaseName" ` 
                    -BackgroundColor Black -ForegroundColor Green       
                cd SQLSERVER:\SQL\$SQLInstanceName\databases\$DatabaseName;  
                (dir tables) | sort -desc dataspaceused | select Parent, Displayname, rowcount, dataspaceused | ` 
                sort DisplayName | ft -auto;  
            } else { 
                Write-Host "Processing SQLServer Instance: $SQLInstanceName, DatabaseName: $DatabaseName" ` 
                    -BackgroundColor Black -ForegroundColor Green           
                cd SQLSERVER:\SQL\$SQLInstanceName\Default\databases\$DatabaseName;  
                (dir tables) | sort -desc dataspaceused | select Parent, Displayname, rowcount, dataspaceused | ` 
                sort DisplayName | ft -auto;  
            }; 
        } else {   
            Write-Host "You need to provide a Database name!!" -ForegroundColor red -BackgroundColor black;   
        };  
    } else {  
        If($SQLInstanceName -match '\\'){ 
            Write-Host "Processing SQLServer Instance: $SQLInstanceName, DatabaseName: All" ` 
                -BackgroundColor Black -ForegroundColor Yellow 
            cd SQLSERVER:\SQL\$SQLInstanceName\databases;  
            dir | select @{Label=”SQLServerName”;Expression={$_.Parent};}, ` 
            @{Label=”DatabaseName”;Expression={$_.Name};}  | Format-Table -AutoSize; 
        } Else { 
            Write-Host "Processing SQLServer Instance: $SQLInstanceName, DatabaseName: ALL" ` 
                -BackgroundColor Black -ForegroundColor Yellow 
            cd SQLSERVER:\SQL\$SQLInstanceName\Default\databases;  
            dir | select @{Label=”SQLServerName”;Expression={$_.Parent};}, ` 
            @{Label=”DatabaseName”;Expression={$_.Name};} | Format-Table -AutoSize; 
        }; 
    }; 
    ## - Here's the Remove SQLPS module  
    Remove-Module SQLPSv2; #for Denali or under  
set-location $env:HomeDrive 
}; Set-Alias gtrc Get-dbTableRecordCount;