Connect to a remote SQL Server using SQLPS.

 

Displaying a list of tables with their rows count:

1. You need to have installed an instance of SQL Server 2008 (or 2008 R2) in order to use SQLPS.

2. To connect to another SQL Server remotely, you need to make sure the server(s) are properly registered in your SQL Management Studio (2008/2008-R2). Look in the *“Registered Servers” pane.

*Hint: You should be able to query SQL Server 2000, and 2005.

3. To start using SQLPS, there’s three ways to open it:

  • a. In the Windows Start/Search box, typing SQLPS and then Enter.

  • b. SQL Management Studio, right-click on any of the objects and look for “Start PowerShell”

4. Start SQLPS session:

5. Check your SQL Registered Servers:

6. Here’s a very useful one-liner cmdlet to get tables –rowcounts and generate some output:

 

# – Change directory to the database ‘RemoteSQL01’ – tables folder:

CD SQLSERVER:\sql\RemoteSQL01\default\databases\NewDB\tables

dir | Select name, rowcount | FT -auto

Name             RowCount

—-                       ——–

Table1                         0

Table2                        86

Table3                      107

#- or send the result to a *.csv file

dir | Select name, rowcount | Export-Csv -NoTypeInformation C:\temp\RowCount.csv

ii C:\temp\RowCount.csv #-To open file in Excel

The first line use the “CD” to change directory to the remote server tables folder. Then, use the “dir” to list all tables. If you need to find more information about that folder then use the “Get-Member” (or the alias “gm”) so you can see that properties and methods you have available at your finger tips.

Example, use the following approach to save the results on a variable and then use “Get-member”:

 

CD SQLSERVER:\sql\Mtrinidad-01\default\databases\developer\tab

# – Change directory to:

PS SQLSERVER:\sql\Mtrinidad-01\default\databases\developer\tables>

dir

Schema Name Created

—— —- ——-

dbo Accounts 4/15/2009 1:48 PM

dbo Accounts – Local 8/6/2009 1:10 PM

:

# – Create variable x to hold “dir” results

$x = dir

$x | gm | more

TypeName: Microsoft.SqlServer.Management.Smo.Table

Name MemberType Definition

—- ———- ———-

:

Alter Method System.Void Alter()

:

Create Method System.Void Create()

:

Drop Method System.Void Drop()

:

Name Property System.String Name {get;set;}

:

RowCount Property System.Int64 RowCount {get;}

 

Then, using the Cmdlet “Select” (short for “Select-Object”), you can work with the properties: “Name” and “RowCount” to be displayed in the PowerShell Console:

dir | Select name, rowcount

Results:

 

Have fun with SQLPS!!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s