Dropping All Tables From A SQL Database with PowerShell

This post could also have been titled confusion with foreach or For-EachObject

The scenario – Having created a blank database a number of users and permissions for an external consultant to create a test database for an application I got a phone call.

“Please can you drop all the tables from the database as we need to re-run the installer with some different parameters”

Sure, I thought. No problem. I will use PowerShell. A simple script is all I need


That ought to do it. Loop through the tables and drop each one. But when I ran it I got this error


What I did (which I should have done first up but time pressures hadn’t allowed) was drop the database and write a script to recreate it and all the users and permissions required using my Create Windows User Function and Add User to Database Role Function but it got me thinking.

So I went home and fired up my Azure VMs and had a play and found two ways of resolving it. But first lets understand what is happening here. I read this post which explains it quite well for his script.

We are going through a list collection and deleting any instance of our event receiver, in the “Foreach loop”. But once we delete an item we are modifying the current list collection. The “Foreach” loop looks to see what the current value is, before it moves on to the next item. But since we deleted the current item, we get the “Collection was modified; enumeration operation may not execute” error.

Now that understand what is going on, we can now look at a solution to correct the error.

The simplest way to avoid modifying the collection would be with a “For Loop”.  With a “For Loop”, no modifications are made that will interrupt the looping process.

So when PowerShell has dropped the table it returns to the tables collection to find the current table before moving on to the next table but as we have deleted the table it falls over.

So lets fix it.

First lets create a test database with PowerShell. A piece of code that is useful to keep for scenarios like this. If you are creating a database for something other than a quick demo or a test then go and explore the other properties of the database object that you will surely want to configure. But for this demo the following is fine, it will use default options. The same applies for the tables script below.


Now lets create some tables.


And check they have been created


Now following the advice from above we can do the following


First we count the number of tables and set it to a variable and then create a for loop. Note if you put $i –le $tables.Count then the script will only delete 4 tables! In the script block we are setting the $table variable to the first in the collection and then drops it. List the table names again to check or run $tables.Count and you will see that all the tables have been deleted.

This was the other solution I found. It makes use of the scripter object to script the Drop commands for the tables add them to a Query string and pass that to  Invoke-SQLCmd to run it.


Starting My Azure SQL Server VMs with PowerShell


The last post about Launching Azure VMs with PowerShell made someone ask me to explain how I start my Azure VMs normally so here goes.

When I decide to write a blog post or develop and test a script or run through demos from a presentation or blog post I fire up my Azure Virtual machines with PowerShell. This is how I do it

Open PowerShell and check that I am connected to my default subscription by running Get-AzureSubscription

Note – You must have installed Windows Azure PowerShell and installed the PublishSettingsFile or used Add-AzureAccount for your subscription following the steps here


Then I run the following three Cmdlets


Get-AzureVM shows me the VMs associated with that subscription.

I then pipe to Start-AzureVM as I want to start both machines. If I only wanted one I would check that

Get-AzureVM -name Fade2Black -ServiceName TheBestBeard


returned the correct machine and then pipe that to Start-AzureVM

Once the VMs have started I use Get-AzureRemoteDesktopFile giving a local path for the rdp file and specifying –launch to run the RDP session


and away we go 🙂

Once I have finished simply run


and my machines are stopped and no longer running my credit down.

What Runs on the SQL Server when you run a PowerShell script?–Question from #SQLRelay

Last week I ran a PowerShell lab at SQL Relay in Cardiff. There are still a few places available for SQL Relay week 2. Take a look here for more details and follow the twitter hashtag #SQLRelay for up to date information

The link for my slides and demos from the second part are here https://t.co/Fik2odyUMA

Whilst we were discussing Show-LastDatabaseBackup Kev Chant @KevChant asked where it was getting the information from and I answered that PowerShell was running SQL commands under the hood against the server and if you ran profiler that is what you would see. We didn’t have time to do that in Cardiff but I thought I would do it today to show what happens

A reminder of what Show-LastDatabaseBackup function does


If we start a trace with Profiler and run this function we get these results in PowerShell


In Profiler we see that it is running the following T-SQL for


exec sp_executesql N' SELECT dtb.name AS [Name] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'RageAgainstTheMachine'



and then for


exec sp_executesql N' create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime) insert into #tempbackup select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = ''D'' or [type] = ''L'' or [type]=''I'' group by database_name, [type] SELECT (select backup_finish_date from #tempbackup where type = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'D',@_msparam_1=N'RageAgainstTheMachine'





exec sp_executesql N' create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime) insert into #tempbackup select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = ''D'' or [type] = ''L'' or [type]=''I'' group by database_name, [type] SELECT (select backup_finish_date from #tempbackup where type = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastDifferentialBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) <mailto:dtb.name=@_msparam_1)> drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'I',@_msparam_1=N'RageAgainstTheMachine'



And for


exec sp_executesql N' create table #tempbackup (database_name nvarchar(128), [type] char(1), backup_finish_date datetime) insert into #tempbackup select database_name, [type], max(backup_finish_date) from msdb..backupset where [type] = ''D'' or [type] = ''L'' or [type]=''I'' group by database_name, [type] SELECT (select backup_finish_date from #tempbackup where type = @_msparam_0 and db_id(database_name) = dtb.database_id) AS [LastLogBackupDate] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_1) <mailto:dtb.name=@_msparam_1)> drop table #tempbackup ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'L',@_msparam_1=N'RageAgainstTheMachine'



So the answer to your question Kev is

Yes it does get the information from the msdb database

#TSQL2sDay Why My Head is Always in The Cloud

Todays post is my first for the TSQL2sDay series. For those not familiar this is rotating blog party that was started by Adam Machanic (@AdamMachanic | blog) back in 2009. If you want to catch up on all the fun to date? Check out this nice archive (link) put together by Steve Jones (@way0utwest |blog). Thank you Steve!!!

Azure Ballon - Credit http://owenrichardson.com/

This one is hosted by Jorge Segarra @SQLChicken:  who said This month’s topic is all about the cloud. What’s your take on it? Have you used it? If so, let’s hear your experiences. Haven’t used it? Let’s hear why or why not? Do you like/dislike recent changes made to cloud services? It’s clear skies for writing! So let’s hear it folks, where do you stand with the cloud?

My wife would tell you that my head is always in the cloud and she’s right (she usually is) just not like that picture! I would love to float gracefully above the land and gaze upon the view but its the landing that bothers me and will always stop me from trying it

Credit http://owenrichardson.com/

She’s right, pedantically and literally too, because this year I have spent a lot of time with my head and my fingers and my thinking in Virtual Machines using Windows Azure. That is where I have learnt a lot of my SQL and Powershell this year. After SQL Saturday Exeter and SQL Bits in Nottingham this year I have needed a place to practice and learn, an environment to try things and break things and mend them again and experiment.

I learn just as well by doing things as I do reading about them. Stuart Moore  @napalmgram has a great post called Learning to Play with SQL Server and whist I haven’t been as rough with my Azure SQL instances as he suggests I have been able to practice at will without worry and thanks to my MSDN subscription without cost. I have taken examples from blog posts and demos from User Group Sessions and run them on my Windows Azure VMs

Every single blog post I have written this year that has examples has been written in Azure and screen shots from Azure. Whilst some of my Powershell scripts in the PowerShell Box of Tricks series had already been written to solve one particular problem or another at MyWork, every single one was refined and demo’d and all the screen shots were from Azure and several were developed on Azure too

My first ever session to the SQL South West user group was about Spinning up and Shutting Down VMS in Azure was about Azure and was an interesting experience in Murphys Law which meant I ended up having to deliver it  on Azure.

The second time I have talked was about the PowerShell Box of Tricks series to the Cardiff User Group. Having learnt my lesson from the first time I had bought a mini HDMI to VGA converter and I had tested it using a couple of monitors at home and it worked wonderfully. However, when I got to Cardiff my little Asus convertible didn’t provide enough grunt to power the funky presentation screen. Luckily thanks to Stuart Moore @napalmgram who was also there doing his excellent PowerShell Back Up and Restore Session who let me use his Mac I was able to deliver the session using Office Web App to run the PowerPoint from my SkyDrive whilst all the demos were on ………Yup you guessed it Windows Azure !!!

So I feel qualified to answer Jorge’s questions and take part in T-SQL Tuesday this time round.

I like Azure. I like the ease I can spin up and down machines or any PaaS services at will. I love that I can do it with PowerShell because I really enjoy using PowerShell in my day to day work and at home too. Living as I do in a beautifully convenient bungalow in the country, I still enjoy the frustration of watching that spinning ring as my videos buffer on our 1.8Mbs at best internet connection. Whilst that does have an impact on using Azure it is a damn sight better than waiting many days trying to download one single file. Something like an ISO file for the latest SQL Server CTP for example.

There is no way I would have got a look at SQL Server 2014 if it wasn’t for Azure. I was able to spin up a SQL Server 2014 machine in only a few minutes and log in and have a play and then delete it. I have done the same with Server 2012 and 2012 R2. It has enabled me to try setting up Availability Groups and other technologies not yet implemented at MyWork

I wouldn’t have been able to do any of that on my machines at home as I don’t have anything capable of running Hyper-V whilst this 8 year old desktop still keeps hanging on despite the odd noises. (Negotiations are currently in place to replace it with something shiny and new. Just need that lottery win now !!)

I have also transferred my Cricket Averages database to WASD and am talking with a friend of mine about developing an app that will use the mobile service as well.

The rate of change is much quicker in the cloud, things change and change quickly. As quickly as I had written my post about Spinning up and Shutting Down VMS in Azure Microsoft changed the rules and didn’t charge for machines that were turned off. New services appear all the time. New services move quickly through from Preview to release and as Grant Fritchey noticed this week new views have been added to to Windows Azure SQL Database under the covers. I think this is something we are just going to have to live with. The scale of the cloud means it is much easier to test improvements at large scale and that means they can be released quicker.  It makes it more challenging to keep up I admit but it’s a constant drip of new things rather than a big bang all at once.

Azure has brought me to where I am today and I think it will continue to be part of my future. If I remember to submit my PowerShell session for SQL Saturday Exeter (Submit yours here) and it gets chosen then you will be able to see me there (if you register here) using Azure to give back to the SQL Community

SQL Saturday Exeter–What’s the Point? My Experience of 2013 SQLSatExeter


Disclaimer – I am on the committee organising the next SQL Saturday Exeter. To be kept up to date about SQL Saturday #269 in the South West, follow @SQLSatExeter and#SQLSatExeter on twitter and see details at the bottom. This post is about my experience at this years event.

In March this year the SQL South West User Group hosted SQL Saturday #194 in Exeter. I was a new member to the User Group having finally been able to join them for the first time in January. At that meeting Chris Testa O’Neill presented a session and was very passionate about the SQL Community and the benefit of the SQL Saturdays and other events.  I am always keen to learn new things and find ways of developing my skills. As I haven’t won the lottery I also look out for good deals as well!!


It was relatively easy to persuade my bosses to pay for my pre-con. For £150 I was able to spend a whole day in a room with about a dozen people being trained in SQL Server Security by Denny Cherry @mrdenny. The conversation went along the lines of

“I want to go to this training session being delivered by this guy. Link to MVP page. It’s £150 and is in Exeter so no other costs required”

My boss – “OK”

Of course there was a little more fun and games to be had with the payment but it was easy for me to get training sorted and £150 is not going to break the training budget.

Looking back through my notes from the session today I realise quite how much I have taken from it into my role at work. I can’t really comment which and what though that wouldn’t be good security!!

I remember an enjoyable day with plenty of technical learning, a lot of questions and answers and plenty of laughs as well. But more than that was the opportunity to mix with other professionals and talk with them. During the breaks and at lunch there were plenty of opportunities to chew the fat, learn how others do things, make new friends and put faces to twitter handles. (NOTE : I do look pretty much like my twitter profile picture so if you see me at SQL Community events I expect you to come up and say hi, that’s part of the benefit of attending these events, having a good natter)

Take a look at the end of this post for details of 2014 Pre-Cons


SQL Saturdays are FREE

SQL Saturdays offer sessions from internationally renowned and local SQL speakers on subjects relevant to you and your job, your future career, your development plan or just to challenge yourself by learning about something outside of your comfort zone. For Nothing. Add in the networking opportunities, the prizes from the sponsors, (if you were at Exeter this year the beer and the pasty) and if you added it up its a sizeable investment in yourself, your career and your development (did I mention a free beer and pasty?)


To enable that, SQL Saturday organisers have to go out and talk sponsors into putting their hands into their pockets. They will only do that if it is worthwhile to them. You can make it easier for the organisers by going and spending time with the sponsors during the breaks, chatting with them and giving them your details. Also, if you choose to use one of their products please tell the sponsors you spoke to them at a SQL Saturday. They are (usually) data professionals who will record that and use that to make future decisions which will we hope include sponsoring SQL Saturdays.

This year on the Saturday I went to the following sessions

A temporary fix for a short term problem by Ian Meade
Advanced SQL Server 2012 HA and DR Architectures by Christian Bolton
Busting common T-SQL myths by Dave Morrison
Power View and the Cube by Régis Baccaro
Natural Born Killers, performance issues to avoid by Richard Douglas
Tracking server performance without slowing it down by Jonathan Allen which I also Room Monitored
Increasing Business and IT collaboration by Chris Testa-O’Neill

It was a really good day. I learnt so much from all those knowledgeable and talented people. It really kicked me on in my development at work. I was able to take from each of those sessions and use that knowledge to do my job better and I made new friends and new contacts. Just going back to my notes today has reminded me of something that I need to look into for work Smile Some of the conversations I have had at events this year have been fascinating – learning how other people do the same thing you do in a completely different but equally valid way,  problem-solving with a different set and type of minds than the ones at MyWork, laughing at the same things and moaning about similar frustrations. All have been both entertaining and rewarding and I think are worth mentioning as things I enjoyed about going to SQL Community events this year and play a part in the reason I shall continue to go to them (Just hope my boss doesn’t read this and think he won’t have to pay as I will go anyway!)

It’s busy and hectic, the sessions come along thick and fast and there are lots of people around to talk to. I wish I had made use of the SQL Saturday mobile phone app and I definitely recommend researching ahead of time and planning your day out.

This years sessions have not been decided yet but I have seen some of the submissions and there are some fabulous sessions there. You could also submit a session yourself. Choosing the sessions will be tough, but we want to offer the opportunity to speak to as many people as possible both new and experienced speakers.

You can submit your sessions at this link http://www.sqlsaturday.com/269/callforspeakers.aspx


For a newbie, as I was last time, SQL Saturday Exeter was a revelation.

An opportunity to learn without spending thousands of my own or MyWorks money to sit in a lecture room and listen to a trainer.

A chance to develop my understanding in a friendly environment amongst my peers where I could ask questions.

A place to meet new people and build relationships who have helped me with situations at work throughout the year. I reckon I’m in credit already

This year I have attended SQL Bits and SQL Saturday Cambridge and this month I shall be at SQL Relay in Cardiff and in Bristol. That all started with SQL Saturday 194 in Exeter 2013


Next years SQL Saturday in Exeter, SQL Saturday #269, will be held at the same place – Jury’s Inn Hotel Exeter on March 21/22nd 2014.

We had such amazing submissions for our pre-cons that we have had to find more rooms to be able to fit them all in.. You can see for yourself the quality of the sessions and speakers for SQL Saturday Exeter 2014 at the following link


What do you think? I want to split myself into 8 and go to every one!


I suggest that you should book Saturday 22nd March 2014 out in your calendar right this minute. Done that? Good.

Now go to this link


and register for FREE to attend and let us know @SQLSatExeter

Next make yourself a coffee (Other beverages are available) and head to the pre-con page


This bit is up to you, the choice is hard. I can’t tell you which one of our eight fabulous sessions you want to go to. It’s not for me to say which amazing speaker you want to spend a day with for a bargain price but if you need further info please get in touch and we will try and help. Unfortunately our human cloning experiment is not stable enough to allow you to go to more than one!

Then, let me know you have done so and come and say hi when you are here.