Browse by Tags

• activity
• administration
• blocking
• demos
• DMVs
• foreign keys
• indexes
• monitoring
• OVER clause
• Performance
• primary keys
• row_number
• splitting strings
• SQL Server 2005
• T-SQL
• unique constraints
• who is active

• Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys

  Once upon a time it was 2004, and I wrote what I have to say was a pretty cool little script . (Yes, I know the post is dated 2006, but that's because I dropped the ball and failed to back-date the posts when I moved them over here from my prior blog Read More... Posted Sunday, April 04, 2010 11:48 PM by Adam Machanic | 14 Comments Filed under: Scripts, primary keys, foreign keys, unique constraints, indexes Attachment(s): script_table_keys.sql

• Who is Active? v9.57: Fast, Comprehensive DMV Collection - What's Really Happening on Your Server?

  UPDATE, April 28 2011: Who is Active v9.57 is outdated. Please use v11.00 instead. Happy December, SQLblog readers! My gift to you, just in time for the holidays: The newest "official" release of your favorite SQL Server activity monitoring stored procedure Read More... Posted Thursday, December 03, 2009 3:59 PM by Adam Machanic | 38 Comments Filed under: Performance, Scripts, DMVs, who is active, monitoring

• Who is Active? v8.82 - Harder, Better, Faster, Stronger

  I've had a lot of great feedback since my last "official" Who is Active? release-- v8.40, back in March --and since then I've made 42 distinct changes, bringing us all the way to v8.82. My versioning scheme is quite simple: one change, one increment, Read More... Posted Thursday, August 20, 2009 12:03 PM by Adam Machanic | 14 Comments Filed under: Scripts, DMVs, who is active, monitoring Attachment(s): who_is_active_v8_82.zip

• Demos From Cape Cod .NET: T-SQL Power! Presentation

  On Tuesday night I was honored to present a new talk to the Cape Cod .NET Users Group that meets in Plymouth, MA. The talk is called "T-SQL Power! Learning to Harness the Under-Used OVER Clause". Following is the abstract for the talk: First introduced Read More... Posted Thursday, June 18, 2009 1:05 PM by Adam Machanic | 0 Comments Filed under: Scripts, demos, row_number, OVER clause Attachment(s): T_SQL_Power_Demos.zip

• Who is Active? v8.40 - Now With Delta Power!

  NOTE: v8.40 is outdated at this time. Please try v9.57, which you can find here . It has been only a month and a half since I posted the last version of Who is Active? ( v7.30 ), but in that time I've made a huge number of changes, fixes, and enhancements. Read More... Posted Monday, March 30, 2009 1:41 PM by Adam Machanic | 33 Comments Filed under: Scripts, DMVs, who is active, monitoring Attachment(s): who_is_active_v8_40.zip

• Who is Active? v7.30

  Who is Active? is a comprehensive DMV-based monitoring script, designed to tell you at a glance what processes are active on your SQL Server and what they're up to. It has a number of optional features so that you can get results quickly, or monitor deeply, Read More... Posted Wednesday, February 18, 2009 11:34 AM by Adam Machanic | 6 Comments Filed under: Scripts, DMVs, who is active, monitoring Attachment(s): who_is_active_v7_30.zip

• A Gift of Script for 2009: Who is Active, Redux

  Last year on December 31 I posted part of a larger monitoring script that I had been working on for a few months . I received lots of great feedback on the little script in both the comments and from people I was working with, and over the course of the Read More... Posted Wednesday, December 31, 2008 10:49 AM by Adam Machanic | 11 Comments Filed under: Performance, Scripts, DMVs, blocking, who is active, monitoring, activity Attachment(s): who_is_active_v7_22.zip

• A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

  As with all of the blog posts I keep meaning to write -- I keep a list and given the infrequency with which I've been posting lately, it's getting quite large -- this script has been on the queue for quite some time. So here I find myself with a spare Read More... Posted Monday, December 31, 2007 6:32 PM by Adam Machanic | 17 Comments Filed under: Scripts, DMVs, administration

• Replacing xp_execresultset in SQL Server 2005

  SQL Server 2000 included a very useful extended stored procedure called xp_execresultset . This XP had two parameters: @cmd and @dbname . @cmd was expected to be a SELECT statement that would produce a single column of output, each row of which would Read More... Posted Thursday, October 19, 2006 11:13 AM by Adam Machanic | 7 Comments Filed under: Scripts, SQL Server 2005

• Looping over routines using sp_foreachroutine

  Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb . These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table Read More... Posted Wednesday, July 12, 2006 10:45 PM by Adam Machanic | 5 Comments Filed under: T-SQL, Scripts

• Tokenize UDF

  Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting. This time we delve into a mysterious world that I call, "Tokenization." So what is Tokenization? It's a word I made up for this problem. But what Read More... Posted Wednesday, July 12, 2006 10:34 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

• "Reflect" a TSQL routine

  Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some way? sp_helptext works, sort of. But I really don't like the way it handles large procedures (> 4000 characters). They seem to end up with some Read More... Posted Wednesday, July 12, 2006 10:33 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

• Script out PKs/UNIQUE constraints and referencing FKs

  In the course of my work, I occasionally need to cluster a primary key that's nonclustered, or go the other way, or make some other modification to a primary key... But it's a hassle! All of the foreign keys need to be dropped, the PK needs to be dropped, Read More... Posted Wednesday, July 12, 2006 10:32 PM by Adam Machanic | 17 Comments Filed under: T-SQL, Scripts

• Pattern-based split string

  "hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion. Typical string Read More... Posted Wednesday, July 12, 2006 10:31 PM by Adam Machanic | 9 Comments Filed under: T-SQL, Scripts

• Validate a URL from SQL Server

  File this one in your folder of things you should probably never use -- but maybe, some day, in an emergency, you'll need this. I see posts requesting this functionality all the time. "How do I validate a URL in SQL Server?" Not just the string, but the Read More... Posted Wednesday, July 12, 2006 10:23 PM by Adam Machanic | 19 Comments Filed under: T-SQL, Scripts