Introduction

In this chapter, we will discuss scripts and snippets of code that accomplish basic SQL Server tasks using PowerShell. We will start with simple tasks such as listing SQL Server instances and creating objects such as tables, indexes, stored procedures, and functions to get you comfortable with working with SQL Server programmatically.

You will find that many of the recipes can be accomplished using PowerShell and SQL Management Objects (SMO). SMO is a library that exposes SQL Server classes that allow programmatic manipulation and automation of many database tasks. For some recipes, we will also explore alternative ways of accomplishing the same tasks using different native PowerShell cmdlets.

Note

SMO is explained in more detail in Chapter 1, Getting Started with SQL Server and PowerShell.

However, even if we are exploring how to create some common database objects using PowerShell, keep in mind that PowerShell will not always be the best tool for the task. There will be tasks...