Generating T-SQL Randomly with Powershell

I have a lab on my laptop running various servers so that I can problem solve and learn and recently I wanted to add several months of data into a database. I had created a stored procedure to take some parameters perform some logic and insert the data.

To execute the stored procedure in T-SQL I simply run this

EXECUTE [dbo].[usp_Insert_DriveSpace] 'Server1','C','2014-11-05','100','25'

which uses the server name, drive letter, date, capacity and free space to add the data

In my wisdom I decided to create some data that was more ‘real-life’ I was interested in storing drive space data and will be learning how to write reports on it. To do this I had pre-populated some tables in the database with 10 Server Names each with 5 drives so I needed 10*5*90 or 4500 statements

I wanted to populate this with about 3 months of data as if it had been gathered every day. I read this post about using CTEs to create sequences and I am sure it can be done this way but I don’t have the T-SQL skills to do so. If someone can (or has) done that please let me know as I am trying to improve my T-SQL skills and would be interested in how to approach and solve this problem with T-SQL

I solved it with Powershell in this way.

Created an array of Servers and an array of Drives to enable me to iterate though each.

$Servers = 'Server1','Server2','Server3','Server4','Server5','Server6','Server7','Server8','Server9','Server10'
$Drives = 'C','D','E','F','G'

Set the drive capacity for each drive. To make my life slightly easier I standardised my ‘servers’

$CDriveCapacity = 100
$DDriveCapacity = 50
$EDriveCapacity = 200
$FDriveCapacity = 200
$GDriveCapacity = 500

I needed to create a date. You can use Get-Date to get todays date and to get dates or times in the future or the past you can use the AddDays() function. You can also add ticks, milliseconds, seconds, minutes, hours, months or years

(Get-Date).AddDays(1)

I then needed to format the date. This is slightly confusing. If you just use Get-Date to get the current date (time) then you can use the format or uformat switch to format the output

Get-Date -Format yyyyMMdd
Get-Date -UFormat %Y%m%d

However this does not work once you have used the AddDays() method. You have to use the ToString() method

 $Date = (get-date).AddDays(-7).ToString('yyyy-MM-dd')

To replicate gathering data each day I decided to use a while loop. I set $x to –95 and pressed CTRL and J to bring up Snippets and typed w and picked the while loop. You can find out more about snippets in my previous post I started at –95 so that all the identity keys incremented in a real-life manner oldest to newest.

$x = -98
while ($x -le 0)
{
    $Date = (get-date).AddDays($x).ToString('yyyy-MM-dd')

    foreach($Server in $Servers)
    {
        foreach ($Drive in $Drives)
        {

I could then use the while loop to generate data for each day and loop through each server and each drive and generate the T-SQL but I wanted more!

I wanted to generate some random numbers for the free space available for each drive. I used the Get-Random cmdlet If you are going to use it make sure you read this post to make sure that you don’t get caught by the gotcha. I decided to set the free space for my OS,Data and Log Files to somewhere between 70 and 3 Gb free as in this imaginary scenario these drives are carefully monitored and the data and log file sizes under the control of a careful DBA but still able to go below thresholds.

if($Drive -eq 'C')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3

I set the TempDB drive to have either 4,7 or 11 Gb free so that i can try to colour code my reports depending on values and if one field only has three values it makes it simpler to verify.

I set the Backup Drive to somewhere between 50 and 0 so that I will hit 0 sometimes!!

Here is the full script. It generated 4500 T-SQL statements in just under 16 seconds

$Servers = 'Server1','Server2','Server3','Server4','Server5','Server6','Server7','Server8','Server9','Server10'
$Drives = 'C','D','E','F','G'
$CDriveCapacity = 100
$DDriveCapacity = 50
$EDriveCapacity = 200
$FDriveCapacity = 200
$GDriveCapacity = 500

$x = -98
while ($x -le 0)
{
    $Date = (get-date).AddDays($x).ToString('yyyy-MM-dd')

    foreach($Server in $Servers)
    {
        foreach ($Drive in $Drives)
        {
            if($Drive -eq 'C')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$CDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'D')
            {
            $Free = Get-Random -InputObject 4,7,11
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$DDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'E')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$EDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'F')
            {
            $Free = Get-Random -Maximum 70 -Minimum 3
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$FDriveCapacity','$Free'"
            }
            elseif($Drive -eq 'G')
            {
            $Free = Get-Random -Maximum 50 -Minimum 0
            Write-Host "EXECUTE [dbo].[usp_Insert_DriveSpace] '$Server','$Drive','$Date','$GDriveCapacity','$Free'"
            }
        }
    }
    $X++
}

Once it had run I simply copied the output into SSMS and was on my way

One thought on “Generating T-SQL Randomly with Powershell

  1. Nice post and level of explanation. I have been looking for examples to help become familiar with Powershell and this fits the bill nicely. In the interest of reciprocity here is one possible TSQL solution uning the recursive CTE from the reference in the article. I’ll be happy to expand on any part of the code if you are interested.

    Declare @todate datetime, @fromdate datetime
    Select @fromdate=getdatE()-89, @todate=getdate()

    ;With DateSequence( Date ) as
    (
    Select @fromdate as Date
    union all
    Select dateadd(day, 1, Date)
    from DateSequence
    where Date < @todate
    ),
    ServerName (name) as
    ( SELECT 'SERVER1'
    UNION ALL SELECT 'SERVER2'
    UNION ALL SELECT 'SERVER3'
    UNION ALL SELECT 'SERVER4'
    UNION ALL SELECT 'SERVER5'
    UNION ALL SELECT 'SERVER6'
    UNION ALL SELECT 'SERVER7'
    UNION ALL SELECT 'SERVER8'
    UNION ALL SELECT 'SERVER9'
    UNION ALL SELECT 'SERVER0'
    ),
    DriveName (drivename,capacity) as
    ( SELECT 'C',100
    UNION ALL SELECT 'D',50
    UNION ALL SELECT 'E',200
    UNION ALL SELECT 'F',200
    UNION ALL SELECT 'G',500
    )
    select *
    ,case WHEN DriveName.drivename IN ('C','E','F') THEN ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT) % 67)+3
    WHEN DriveName.drivename IN ('G') THEN ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT) % 50)
    WHEN DriveName.drivename IN ('D') THEN
    CASE ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 3+1 WHEN 1 THEN 4 WHEN 2 THEN 7 ELSE 11 END
    ELSE ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT) % 100)
    END
    as FreeSpace
    From DateSequence
    CROSS APPLY ServerName
    CROSS APPLY DriveName

Please feel free to comment on this post. All comments are moderated first before appearing on the site