I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

I’ve been experimenting with scripting some tables, and I wanted to check to see if dbatools made that easier. I was amazed by the number of new cmdlets in the project, and after hunting around, I found a couple cmdlets: Get-DbaTable and Export-DbaScript. I decided to see how these work.

My first experiment was to run Get-DbaTable. This takes an instance and database as parameters and returns a set of table objects. I get space and size information and some metadata. As a short look, here’s one table.

I decided to use this as input to Export-DbaScript to see what happened. I used this command:

Get-DbaTable -SqlInstance Plato\SQL2016 -Database NBA | Export-DbaScript

This gave me some output. It wasn’t quite what I wanted, but it worked.

Note that all of the tables were exported to the same file. If I opened that file, here’s a snippet of the code. I get a header, and then each table’s code put together as single batch.

Not quite what I wanted.

I can determine the file, using the –Path parameter. This still gets me one file, but I can make this better. I’ll make a folder and change to that folder in my PoSh window. Now I can run this, using ForEach-Object to iterate over the tables, outputting each to a file.

Get-DbaTable -SqlInstance Plato\SQL2016 -Database NBA | ForEach-Object { Export-DbaScript -InputObject $_ -Path ($_.Name + “.sql”) }

When I do that, I get this:

Each table’s code is now in a separate file.

That’s nice, and with filtering, I could have this generate scripts for specific objects, or types of objects and update them from PoSh, likely a job or task, relatively easily.

Certainly the Generate Scripts process in SSMS is easier than writing this script, but that’s not programmable. This wraps around the SMO scripting objects and makes the process much easier.

If you haven’t tried dbatools, give it a go. You’ll appreciate the ease of scripting and might start to enjoy working with SQL Server in PoSh.