none
Automating Database Counts for All SQL Servers

    Question

  • Hi Folks,

    I need database counts for every SQL server instance (PROD/Non PROD) in an environment.

    If I logged into each and every SQL Server then it is very tedious task for me, so I need to automate it.

    Is there any t-sql or powershell script from which I can get consolidated report for database counts for all servers at one place.

    Tuesday, June 12, 2018 7:10 PM

Answers

  • Hi Folks,

    I need database counts for every SQL server instance (PROD/Non PROD) in an environment.

    If I logged into each and every SQL Server then it is very tedious task for me, so I need to automate it.

    Is there any t-sql or powershell script from which I can get consolidated report for database counts for all servers at one place.

    You will have to do some customizations. I can think of two ways:

    1. Configure Central Management Server and create a new group and add all SQL instances to that group. Run a select count(*) from sys.sysdatabases and it will run against all SQL instance.

    2. A second way would be to use PowerShell script. 

    The following script assumes a table named "##Instance_Info" with only one field "InstanceName".

    Note: This method requires you to insert all your SQL Servers' names to a table so the script can read from it. If you already have an inventory of your SQL Servers, you can tie it to an existing table that contains the server names.

    In the script below, replace the appropriate fields to suit your environment. You can also schedule this from SQL agent. You can modify the script to write to an output file and append it with the result from each instance.

    Import-Module SqlPs -DisableNameChecking
     
    # $databases grabs list of instance names the user has access to, from the temp table ##Instance_Info
    $databases = invoke-sqlcmd -ServerInstance <ServerName>\<InstanceName> -Database TempDB -Query "SELECT distinct InstanceName FROM ##Instance_Info order by InstanceName"
     
    If($databases.Count -gt 0)
    { 
    foreach ($InstanceName in $databases) 
    {
    # picks each instance ($inst)
    $Inst = $InstanceName.InstanceName #instance from the select query
     
    #connect to each instance and execute the desired script
    invoke-sqlcmd -ServerInstance ${Inst} -Database <DatabaseName> -InputFIle "C:\Whatever\Whatever.sql"
      
    } #end foreach loop
    }

    Hope this gives an idea.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, June 12, 2018 10:13 PM

All replies