Voice of the DBA

Category: Blog

Scripting Tables with dbatools

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.

March 23, 2018
I learned about the order of logical operations #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had logic in a CS curriculum many years ago and I’ve worked with AND and OR statements for years. I’ve sometimes confused myself, but I usually ensure I have parenthesis included to clarify the code. Not just for me, but for anyone that might glance at the code later.

As a side note, I also try to format code so a quick glance can reveal what happens.

However, I learned something new this week. I saw a question about the order of logical operations in this form: a or b and c.

I had somewhat assumed, like math, we’d use a left to right evaluation. However, that’s not correct. Look at this snippet:

If we went left to write, we’d have two rows from the OR (n=1, n=2) and then an AND that produces no rows. So no results?

That’s not correct. According to BOL for OR, the AND operations occur first. So n=2 AND n > 3 occurs, with 0 rows. Then the OR with n=1 is evaluated to return 1 row.

Fascinating.

At least to me. I’ve never thought because I’d write
```
WHERE (n = 1 OR n = 2) AND n > 3
```
or
```
WHERE n = 1 OR (n = 2 AND n > 3)
```
and be sure that what I wanted to occur would occur.

A quick lesson. While it’s good to know what the order or evaluation is for your platform, don’t count on this. If there is a chance for confusion or unintended consequences, use parenthesis. It’s simpler and easier, and I might argue, more elegant.
March 22, 2018
The 700 Milestone

I’ve watched various SQL Saturday milestones go by. I missed 100 in Brazil, but did manage to get to 200 in Philadelphia and 300 in Kansas City. 400 was in Torino, and I am sad about not making the effort to get there. 500 was Boston, but I had a conflict. 600 Chicago, but again, other plans.

Colorado Springs was awarded 700, and I had to submit ASAP to that one. I’ve got a morning commitment for volleyball, but I’ll be racing down to get to the event and attend the after party.

I’ve got a repeat presentation, Branding Yourself for a Dream Job, but there are some other great talks. PowerBI from TJay Belt and Meagan Longoria, Optimizer secrets from Brian Hansen, Data Science from Reeves Smith and more.

Plus there are two great pre-cons on Friday. The Midnight DBAs talking Enterprise Maintenance and Kevin Kline giving you 50 things you need to know.

If you’re nearby, get a free day of training and say hi in the afternoon. If you can convince the boss, I’d say you should take advantage of one of these pre-cons as well.

March 15, 2018
The SQL Privacy Summit

This May 18th, Redgate is putting on a SQL Privacy Summit for people that are looking for solutions to help them comply with the GDPR regulations. I’ll be heading over to participate, and I’m looking forward to hearing from customers and attendees about the challenges they’re facing.

The Details

Friday May 18th 2018

The Grange Tower Bridge Hotel, 45 Prescot Street, London E1 8GP

8:15am – 5:30pm (GMT – convert)

The schedule is out and I’ll be doing a variation of a talk I’ve delivered before on how the GDPR is really asking for solid data practices that we’d all like to implement. There are some panel sessions and lots of networking time built in.

Registration

This is an all day conference, though early bird rates continue through this week. You can purchase a ticket for the event from the event announcement. If you’re a customer, contact sales, and you may be able to get a set of discounted tickets.

Hopefully I’ll see you there and we’ll get the chance to talk about how we can all do a better job securing and protecting our sensitive data.

March 14, 2018