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… Continue reading SQL Server Impact Analysis Query

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… Continue reading SQL Query: List All Indexes And Their Columns

Escaping From An Underscore In A SQL Server Wildcard / LIKE Search

There are two different ways to perform a wildcard (aka LIKE) search in SQL Server to find a word with an "_" underscore. Both options are outlined at the end of the article after a reference to the cult classic Mystery Science Theater 3000: Escape From The Bronx.

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… Continue reading Parse and sort SET STATISTICS IO output with Excel

Set DATA_COMPRESSION for all tables

Working on a quick little project to sync up two databases and I realized the data compression was set differently between the two of them. Apparently, my Google fu was not up to snuff because it took quite a bit of stumbling around to find a forum post matching what I wanted to do.  There… Continue reading Set DATA_COMPRESSION for all tables

Last Restore Date And Time – SQL Server Query

"How old is this data?" "When was this database restored?" "How old is this backup?" These are common question in development environments.  A fast little t-sql query can tell you the following: The date and time of the last restore. The date and time the backup which was restored was originally taken. The user who… Continue reading Last Restore Date And Time – SQL Server Query

Alter Table: Add Not Null Column – SQL Server

Listen up my people.  NOT NULL columns are your friend.  It may take a few extra brain cells to populate it initially but the long term rewards in data quality are well worth it. (You'll also force all your software developer cohorts to think specifically about the values they'll be inserting into a column which… Continue reading Alter Table: Add Not Null Column – SQL Server

SQL Script: Distinct Column Values Across All Tables

Sometimes I wonder if some of the more random scripts I post are useful to anyone but me so if this one is useful to you definitely drop a comment below! Check it out:  Our team was trying to confirm what our best practice was when creating flag columns in the data warehouse.  As usual… Continue reading SQL Script: Distinct Column Values Across All Tables

SQL: Create Schema If Not Exists

When deploying code I try to create my scripts so they can be rerun without an error being thrown (e.g. "object already exists").  There's some fancy word for this that I used to try and use so as to appear ostentatious but it turns out people found me ostentatious even when I didn't use the… Continue reading SQL: Create Schema If Not Exists

Compare a Row to the MAX of Multiple Columns and Multiple Rows: The Easy Way

Alright.  MAX on rows *and* columns.  This was a bit of a mind bender.  A fellow SQL Server Developer was wondering if it was possible to find the MAX and MIN values of multiple rows and columns and then compare it to a value on a single row.  Of course this could be done by… Continue reading Compare a Row to the MAX of Multiple Columns and Multiple Rows: The Easy Way