Towering Queries

 I recently ran across a case where a time value was stored as an Int, with the idea that the first two numbers are the hours, second two the minutes, and third two the seconds. This is all well and good if the hours are double digits (e.g. 144415), as you can just split the string every two characters. If the hours are single digit (e.g. 93251) doing that won't give you the right result. You'll wind up splitting out "93", "25", 1" when what you really want is "09", "32", "51". You also run into this stuff sometimes when you're trying to left-pad a string for a year, month, or date to a pretty format. None of these cases are particularly compelling, and there are probably other solutions for these sorts of issues, but what I DO want to share with you, is a couple ways you can left-pad a string with characters.  FWIW, I've tried doing left-padding with the FORMAT function, and maybe I just suck with it, but I haven&#

 By default, powershell scripts failing in a SQL agent job, won't stop the agent job. In otherwords, things will go wrong in your script, and SQL Agent will happily move on to the next step in the job. That is not so good. The options at your disposal would seem to be: Exit 1 Write-Error $ErrorActionPreference="Stop" throw Stuff that doesn't work The first two options might come to mind, but they don't work. Details below. Exit exit 1  (or any code != 0) would seem to give the calling application (Agent) the info that the task failed, by it's return code. However Agent doesn't respect this. So that's not an option. Write-Error Write-Error stops execution when you run a powershell script through a powershell window, but again, for SQL Agent, it doesn't pick up on that. Yes, it will stop the script, but Agent will happily continue to the next step Stuff that Works To get Agent to recognize a powershell script failure, and actually fail the agent job,

So I like the CommandLineParser library for C# command line applications. One thing that tripped me up however was the handling of boolean inputs. Most people are aware of "flags" in shell scripting. For example, in bash, "-a" is an argument for the command "ls". "-a" is conceptually a boolean flag whose presence informs the application "do not ignore entries starting with .". This is in comparison to a parameter with a value, as in "-Path" in "Get-ChildItem -Path C:\" where "C:\" is the value of the parameter "-Path". Flags   are always true if present and assumed to be false if absent . In the CommandLineParser library, you can do Flag-like parameters with the bool  datatype. You can also give them a default value of either true or false (using the DefaultValue argument on the Option attribute). The problem is, if you make the default value true, you might as well not even add it as a parameter

Sometimes I get emails from dbas saying our database is filling up and I want to know where the biggest problems are. I'm sure there are some DBA tools to help with that, but I find a really quick easy way is to execute sp_executesql for every table in every database. Here's a script you can run to  get all table sizes on all databases . It basically loops over all database (except those you don't care about, e.g. master, msdn, etc.) and gets the table sizes for each table. At the end, you can then aggregate things by database, or drill into a specific database for table-level details. set nocount, xact_abort on declare @ExclDatabases nvarchar( max ) = 'master|msdb|tempdb|ssisdb|model' , @Schemas nvarchar( max ) = null drop table if exists #AllTabs create table #AllTabs ( DbName nvarchar(128), SchemaName nvarchar(128), TableName nvarchar(128), ObjectId int, RowCt bigint, ReservedKB bigint, DataKB bigint, IndexSizeKB

 So you want to automate export some data from SQL to CSV files?  For one-off things, there are a couple approaches you can use SSMS for, however there are some major drawbacks of doing things there, most notably, it's all manual; you can't script any of this stuff out. Still, I'll discuss a few methods in SSMS before the cool stuff which is with BCP or Powershell SSMS These methods work fine for one-time exports that you don't need to automate and you don't need a high level of control of the output. As a Result Set One simple way is to set the output to either text (CTRL-T) or to file (CTRL-F). If you set it to text, copy/paste the results into a text file, or if you're using results to file, just name the file anything .csv  (instead of .rpt) . Make sure to clean up any header (like column names, and the big row of hyphens)  and footer (like the rowcount, if you didn't set nocount on). You can adjust some of the settings those methods use to generate the

I think SQL Extended Properties are awesome. In a nutshell, they allow you to attach arbitrary key-value pairs to SQL objects of almost any kind. I've used them for things like simple documentation, to enabling special functionality for maintenance procs, or marking all tables serving a particular function in a system. They're just cool. The biggest pain in the butt IMHO is just managing them; creating them, updating them, and to a lesser extent, viewing them. The biggest problem is scripting them. SQL gives three procs for managing extended properties: sp_addextendedproperty sp_updateextendedproperty sp_dropextendedproperty Each of those does exactly what it says on the tin. The pain is that they will fail if you start in the wrong state. I.e.  Add will fail if the property already exists Update will fail if the property does not already exist Drop will fail if the property does not already exist Which means if you want to add XP management to any re-runnable script, you have