Book Image

PowerShell for SQL Server Essentials

By : Donabel Santos
Book Image

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Table of Contents (15 chapters)
PowerShell for SQL Server Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Implementing Reusability with Functions and Modules
Index

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