4. Basic SQL Server Administration | PowerShell for SQL Server Essentials

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...

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PowerShell for SQL Server Essentials

By : Donabel Santos

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Listing databases and tables

PowerShell for SQL Server Essentials

By : Donabel Santos

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Listing databases and tables

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access