Archive | T-SQL

SQL Query To Find Expired Accounts In Active Directory: Knowledge Is Power And The Goodbye List

Alrighty. We’re all data people here so you know we all agree that knowledge is power. One thing that is absolutely helpful in a large corporate environment is knowing if someone has been let go/laid off/fired. Using linked server to query active directory you can fairly easily (especially if someone else wrote the query) see […]

Get The MAX Date From A Cube Using MDX

Here’s the goal: populate a sql variable with the max date from one of the dimensions in a cube. It takes a little bit of unusual code but it’s relatively simple so pay attention! It’s probably important to note this code is assuming you are using a smart key for your date dimension (e.g. the […]

SQL Job Steps And Run_Duration: The query that formats run_duration correctly and doesn’t make you want to shoot your eye out

Ever tried to figure out how long a step for a particular SQL Server job takes? You can of course get it from the Management Studio, but we’re database people! We want to see the trend over time not just a point in time! We want to write queries! Hooray for data! You do a […]

SQL Server Impact Analysis Query

Ugh. Impact analysis is one my least favorite tasks. And despite the fact it’s gotten easier over the years with the addition of being able to check object dependencies natively within SQL Server Management Studio, I still find myself resorting to wildcard searches of object definitions for various reasons. (For example: dependencies on objects accessed […]

SQL Query: List All Indexes And Their Columns

Looking to write a SQL query which will list out all the indexes in your database as well as the columns in the index? Well my friend you’ve come to the right place. The query below will give list out the following information: Schema Table Name (or view) Index Name Is Primary Key Is Unique […]

Parse and sort SET STATISTICS IO output with Excel

I wish I could say this was my idea. Somebody posted it on twitter awhile back and I did the classic “facepalm”. I have no idea how many times I manually picked apart the results of SQL Server’s SET STATISTICS IO ON without considering creating a formula to handle it for me. So, somebody came […]