Listing databases and tables

Let's start out by listing the current databases. The SMO Server class has access to all the databases in that instance, so a server variable will have to be created first. To create one using Windows Authentication, you can use the following snippet:

Import-Module SQLPS -DisableNameChecking

#current server name
$servername = "ROGUE"

#below should be a single line of code
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername

If you need to use SQL Server Authentication, you can set the LoginSecure property to false, and prompt the user for the database credentials:

#with SQL authentication, we need
#to supply the SQL Login and password
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$server.ConnectionContext.set_Login($credential.UserName)
$server.ConnectionContext.set_SecurePassword($credential.Password)

Another way is to create a Microsoft.SqlServer.Management.Common.ServerConnection object and pass the database...