Adam Machanic : Scripts

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

Adam Machanic — Mon, 05 Apr 2010 02:48:00 GMT

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)

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

Adam Machanic — Thu, 03 Dec 2009 19:59:00 GMT

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)

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

Adam Machanic — Thu, 20 Aug 2009 15:03:00 GMT

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)

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

Adam Machanic — Thu, 18 Jun 2009 16:05:00 GMT

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)

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

Adam Machanic — Mon, 30 Mar 2009 16:41:00 GMT

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. The new version, v8.40, is faster, more robust, and includes a few exciting features.

Click here to download Who is Active? v8.40

Following is a list of some of the things I've done with it, in no particular order, along with descriptions where applicable.

Added a collection interval option, @DELTA_INTERVAL. This option causes the script to collect numeric metrics -- such as reads, writes, and context switches -- twice. The script will wait between collections, based on the duration passed into the parameter (in seconds). The script will then calculate the differences (deltas), between the first and second collections, for any requests that were active for each collection. These deltas will be output in a new set of columns, reads_delta, writes_delta, context_switches_delta, etc.

For example, to use the default options and wait two seconds between collections, you would use the following:

EXEC sp_WhoIsActive @DELTA_INTERVAL = 2;

If you played with Who is Active? in the past you know that the script outputs a lot of data in a lot of different columns. I started feeling that the column list was getting unwieldy, so I implemented dynamic column lists as a fix. There are two changes here. First of all, the default column list will only contain columns associated with those features you have turned on. So for example, if you don't have @GET_LOCKS enabled, the locks column won't appear.

The second part of this fix is a new feature to make things even more dynamic: custom column lists. I sent a slightly earlier test version to Jimmy May, and his response was something along the lines of, "it's pretty good, but I really don't like the column order." To make Jimmy happy, I implemented the @OUTPUT_COLUMN_LIST option. To use this option, simply pass in whatever columns you would like to see, in whatever order you would like to see them, and the script will do the rest. Note that the output will be the intersection of whatever options are enabled and whatever columns are found in the list.

Here's an example I've been using in my own work recently:

EXEC sp_WhoIsActive
    @OUTPUT_COLUMN_LIST =
        '[login_name],[dd hh:mm:ss.mss],[sql_text],[reads],[writes],
        [context_switches],[physical_io],[wait_info],[blocking_session_id],
        [tempdb_writes],[tran_log_writes],[query_plan],[session_id]';

Another feature I added that makes things a bit more dynamic and customizable is the ability to sort the output based on whatever column and direction you would like. Interested in seeing the requests that are currently blocked? No need to scroll, just ask for them to sort high:

EXEC sp_WhoIsActive
@SORT_COLUMN = '[blocking_session_id]',
@SORT_COLUMN_DIRECTION = 'DESC';

While viewing the results on the screen in SSMS is great, sometimes I like to capture the results to a table for later analysis. So I added a few features in this most recent version to support automated collection.

To begin with, I realized that the default, nicely-formatted output of the script, while great for on-screen viewing, isn't good if you want to do your own analysis later. So I added a new option, @FORMAT_OUTPUT, that lets you control whether the output is or is not formatted. The default value for the parameter is 1, keeping with the previous behavior.

I also figured that if you want to do some later analysis it might be nice to know what time period you're working with. So I added a column called collection_time, which outputs a datetime instance representing the time that the script finished running.

Taking things one step further, I decided that with all of the dynamic column options it would be a pain to put together a table matching the output schema if you start playing with different settings. So I made things easy; no need to figure out what the output looks like; the script will write a CREATE TABLE statement for you. Simply enable the @RETURN_SCHEMA option and collect the output value from the @SCHEMA parameter.

Finally, due to nested INSERT/EXEC limitations, it's impossible to insert the result into a table when calling the stored procedure. No worries, the script will insert its output into a table for you. Just tell it where to send the data, via the @DESTINATION_TABLE option.

Here's an example of asking for the unformatted output schema, creating a destination table based on the current day, and collecting data 10 times, waiting 15 seconds between each collection:

DECLARE @destination_table VARCHAR(4000);
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112);

DECLARE @schema VARCHAR(4000);
EXEC sp_WhoIsActive
    @FORMAT_OUTPUT = 0,
    @RETURN_SCHEMA = 1,
    @SCHEMA = @schema OUTPUT;

SET @schema =
    REPLACE
    (
        @schema,
        '<table_name>',
        @destination_table
    );

EXEC(@schema);

DECLARE @i INT;
SET @i = 0;

WHILE @i < 10
BEGIN;
    EXEC sp_WhoIsActive
        @FORMAT_OUTPUT = 0,
        @DESTINATION_TABLE = @destination_table;

    SET @i = @i + 1;

    WAITFOR DELAY '00:00:15'
END;
GO

One of the key reasons I created the original version of Who is Active? was to help identify blocked and blocking SPIDs. And I thought it worked pretty well for that purpose until recently, when I discovered that sys.dm_exec_requests fails to properly identify blocking when queries go parallel. Oops. Fixed in the new version; it now uses the waiting tasks DMV for this purpose, instead of the requests DMV.

As part of this process I discovered that the joins to the tasks DMVs were slightly flawed in v7.30, so I fixed them up. And I was able to play some games to improve performance--enough that I decided to eliminate the @TASK_INFO option, making this the default behavior. This means that you'll always get correct blocker identification from Who is Active? v.8.40, along with a few bonus task-based metrics including physical I/O stats and the number of context switches.

Interested in information about transactions? So are most DBAs, which is why I promoted the transaction start time, which was previously embedded in the tran_log_writes column, to a top-level column of its own. This will still populate only with transactions that have actually done a write, but from what my testers tell me this is okay behavior. Let me know if you disagree. While I was in there I played even more performance games, and greatly improved the speed of the query when the @GET_TRANSACTION_INFO option is enabled.

A few smaller fixes are also worth mentioning here. First of all, I tested the script against a case-sensitive instance, and fixed a few issues there. So it should work for you no matter what collation you're using. Second, thanks to a discussion I had with Roman Nowak, I was able to finally solve the entitization problem that plagued the "clickable" query output produced by the script. Third, I put on my security hat and added some code to verify input arguments, as well as did a review of all of the dynamic SQL used by the script to make sure that it is not injectable.

Thank you to everyone who sent me feedback and/or feature requests! If you have an idea for th script, please make sure to send it my way. I have quite a bit of momentum at the moment and want to keep going and see just how far I can take this thing. I can only do this with your help.

Enjoy!

Click here to download Who is Active? v8.40

Who is Active? v7.30

Adam Machanic — Wed, 18 Feb 2009 15:34:00 GMT

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, depending on your needs when you happen to be using the script. Today I'm happy to present a new version, v7.30, which includes a few bug fixes an a new feature.

Click here to download Who is Active? v7.30

Now that I've covered that (for people who haven't been paying attention to this blog as of late), a bit of commentary. After posting Who is Active? v7.22 on December 31, I expected to receive a huge amount of feedback from the community. Alas, there were a few issues:

It turns out that December 31 is not a great day for a blog post that you would like to get lots of hits.
Apparently many readers were confused about where to actually download the script.
I was told that I need to document the thing better.

Luckily, some users did manage to download it, and I even received some feedback. Great feedback, primarily from a reader named Bill Kan. Thanks for all of your time, Bill! I also discovered a couple of issues on my own, and added a new feature that I thought of while watching Grant Fritchey do an excellent talk at New England Data Camp v1.0.

So now to address those three issues. I'm posting this post on a Wednesday, it's not a holiday, and it will hit midday, so that covers issue #1. For issue #2, see above and see below. I think the download links are painfully obvious at this point. And as for issue #3, well, I'm sorry to say that today is not the day for that one. I've done a fairly good job of documenting the script in the script (download it and take a look), and I think most readers will be able to feel their way around well enough. If you have any questions, post them here and I will be happy to answer. And eventually I will write up a comprehensive document on the thing. But until then, two out of three isn't bad...

Click here to download Who is Active? v7.30

Please give it a try and let me know what you think. I've run out of new feature ideas for the moment and could certainly use some input. If you are using it, please let me know what features you've found to be helpful and how you're integrating it into your work. I'm hoping to identify a number of use cases as talking points for the forthcoming document on the script and actual user feedback will be key.

And, most importantly, enjoy! As I mentioned in December, this script has become a vital part of my toolkit; I have to say that it's made certain aspects of monitoring a lot easier and more interesting for me and I hope it does the same for you.

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

Adam Machanic — Wed, 31 Dec 2008 14:49:00 GMT

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 last year I estimate that I have invested at least a couple of hundred hours in the script. The result of all of this time is attached, and I hope that this script is as useful for others as it has been for me; I've been using it pretty much nonstop while modifying it and it has become an indispensable part of my toolkit.

Alas, while I had planned to write a comprehensive blog post on the script for publication today, I simply don't have time to do it correctly at the moment--but I wanted to get the script out on the 31st in order to create a kind of tradition. So instead of telling you everything it can do, I will ask that you instead take a look inside the script, where I've done a fairly good job documenting both the parameters and the outputs. I'll do a followup post soon (I hope) and do a better job of telling you about the various options. In the meantime, I'll leave you with a list of the script's major features:

Wait stats collection, if you enable the GET_WAITS switch. This mode aggregates the various resources that the tasks associated with the session are waiting on, and lets you know how long the tasks have been waiting.
Collection of lock information, if you enable the GET_LOCKS switch. A lot of effort went into this particular feature--all of the locks for each session are aggregated in an XML format, and work is done to map the various identifiers associated with each lock to actual object names so that you don't have to do the footwork yourself.
Information about sleeping SPIDs holding open transactions, if you enable the GET_TRANSACTION_INFO switch. If you're being blocked by a SPID that is not active, you should be able to find out about it. This option lets you do it. As a bonus, it also pulls information about transaction log writes in any database in which the transaction has done a write.
Collection of the "outer" command/batch, if you enable the GET_OUTER_COMMAND switch. One of the comments on the script I posted last year asked why, if you issue SQL like "ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL", you'll end up with statement text that looks like "UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]". This is an internal representation of what's actually happening, but in many cases is not esepecially useful for end-users. So if you want to see what was actually run, now you can.
Every expensive or potentially expensive option is now parameterized. I spent a lot of time tweaking performance and with no options running the thing is pretty fast. Enable some options--especially lock collection--and it will slow down considerably, so use these advanced features with care.

Enjoy, and please keep sending feedback! Let me know what else you would like to see, if you find bugs, etc. I'll post updated versions as major changes are made.

To finish I would like to thank a few of the people who had an impact on the evolution of this script over the past 12 months: Michael Condon, Erland Sommarskog, Aaron Bertrand, Louis Davidson, and Jonathan Kehayias.

Have a great 2009, everyone!

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

Adam Machanic — Mon, 31 Dec 2007 22:32:00 GMT

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 moment right on the cusp of the new year, and figured what better way to end the year than with a script that, at least for me, has been quite useful these last few months.

The driving force behind my writing this script is that I found myself endlessly calling sp_who2 'active' to see who was doing what on servers I needed to take a look at. Then I would have to sort through the results, and end up calling DBCC INPUTBUFFER to take a look at the SQL being used. This was a serious pain, and I finally caved a few months back and decided to end the madness once and for all with the help of some DMVs.

The following script primarily uses the sys.dm_exec_requests view, and finds all "active" requests -- i.e., those that are running, about to start running, or suspended. It also finds some other useful information, including the host name, login name, the start time of the batch, and whether or not the batch is currently blocked. In the outer query I use the sys.dm_exec_sql_text function to get the text of the SQL that all of the active requests are running, in addition to the SQL being run by the blocking sessions, if applicable. This way I don't have to do two lookups to chase down what's blocking what.

You'll notice that I use FOR XML PATH in the subqueries that pull the SQL text. This gives us a nice little bonus: instead of copying the text out of the cell in SSMS and pasting it somewhere else, you can simply click on it -- and it maintains whatever formatting, including white space and carriage returns, that it originally had. This is much, much nicer than getting the batch on a single line. The only problem is that certain characters, such as greater-than and less-than, get "entitized" when the text is converted to XML. This means that some queries won't be able to be run without a bit of editing. A small price to pay for nicer output, in my opinion. If anyone out there has a solution for the entitization, please let me know! The only way I know to solve it is to convert back to VARCHAR, and that defeats the whole purpose...

Anyway, thanks all for a great 2007. Here's to an even better 2008! Without further ado, the script:

SELECT
    x.session_id,
    x.host_name,
    x.login_name,
    x.start_time,
    x.totalReads,
    x.totalWrites,
    x.totalCPU,
    x.writes_in_tempdb,
    (
        SELECT
            text AS [text()]
        FROM sys.dm_exec_sql_text(x.sql_handle)
        FOR XML PATH(''), TYPE
    ) AS sql_text,
    COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
    (
        SELECT
            p.text
        FROM
        (
            SELECT
                MIN(sql_handle) AS sql_handle
            FROM sys.dm_exec_requests r2
            WHERE
                r2.session_id = x.blocking_session_id
        ) AS r_blocking
        CROSS APPLY
        (
            SELECT
                text AS [text()]
            FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
            FOR XML PATH(''), TYPE
        ) p (text)
    ) AS blocking_text
FROM
(
    SELECT
        r.session_id,
        s.host_name,
        s.login_name,
        r.start_time,
        r.sql_handle,
        r.blocking_session_id,
        SUM(r.reads) AS totalReads,
        SUM(r.writes) AS totalWrites,
        SUM(r.cpu_time) AS totalCPU,
        SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
    FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
    JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
    WHERE r.status IN ('running', 'runnable', 'suspended')
    GROUP BY
        r.session_id,
        s.host_name,
        s.login_name,
        r.start_time,
        r.sql_handle,
        r.blocking_session_id
) x

Enjoy!

Replacing xp_execresultset in SQL Server 2005

Adam Machanic — Thu, 19 Oct 2006 14:13:00 GMT

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 produce a valid query. @dbname was used to specify the database that both the initial query and the resultant queries would be executed in.

For example:

EXEC xp_execresultset 
    @cmd=N'SELECT ''SELECT 1''', 
    @dbname=N'tempdb'

In this case, tempdb would be used to execute the statement SELECT ''SELECT 1''. This statement produces a single row with the value SELECT 1, which is then executed in tempdb. The final output is a single result set containing the value 1.

Multi-row statements are also allowed:

EXEC xp_execresultset 
    @cmd=N'SELECT ''SELECT 1'' UNION ALL SELECT ''SELECT 2''', 
    @dbname=N'tempdb'

In this case, the initial SELECT produces two rows, each with its own statement. Then each row is processed as an individual query, thereby producing two single-row result sets, one with the value 1, and one with the value 2.

We can verify that the first statement is run in the specified database by using the DB_ID() function in the outer query:

USE master

EXEC xp_execresultset 
    @cmd=N'SELECT ''SELECT ''+CONVERT(NVARCHAR, DB_ID())', 
    @dbname=N'tempdb'

In this case the end result is 2, indicating that the outer query was run in tempdb rather than master.

So now that I've covered xp_execresultset's usage in SQL Server 2000, the bad news: this XP, although quite useful in many cases, has been removed in SQL Server 2005. This was pointed out to me by a post today in the MSDN forums by Marko B. Simic (thanks, Marko!)

The solution, luckily, is pretty simple: We can re-create this XP as a stored procedure in SQL Server 2005, using a few tricks to make the job easier. Following is the replacement stored procedure I've come up with:

CREATE PROC ExecResultSet
    @cmd NVARCHAR(MAX),
    @dbname NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @x TABLE
    (
        sql NVARCHAR(MAX),
        num INT IDENTITY(1,1)
    )

    DECLARE @input_sql NVARCHAR(355)
    SET @input_sql = N'EXEC ' + @dbname + '..sp_executesql @stmt=@cmd'

    INSERT @x (sql)
    EXEC sp_executesql 
        @input_sql, 
        N'@cmd NVARCHAR(MAX)', 
        @cmd

    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql =
    (
        SELECT sql + ';' AS [data()]
        FROM @x
        ORDER BY num
        FOR XML PATH('')
    )

    EXEC sp_executesql 
        @input_sql, 
        N'@cmd NVARCHAR(MAX)', 
        @sql
END
GO

Note that I've tried to faithfully reproduce the original functionality of xp_execresultset. This means that the resultant rows are concatenated and executed as a single batch. This can be good in some cases -- for instance, your first row can contain some variable declarations that will be used by later rows. However, this can also cause problems if you need to declare a variable on a per-row basis.

This stored procedure is fairly simple: It executes the input @cmd, concatenates the resultant rows, and finally executes everything as a single batch. Its syntax is identical to the original XP. Modifying this stored procedure to make it a bit more flexible and execute each row as its own batch is a simple matter of using a cursor to take each row individually, rather than employing the FOR XML PATH concatenation trick. However, I'm going to leave it as-is for now so that it can be used directly in places where you would have used xp_executesql previously.

Looping over routines using sp_foreachroutine

Adam Machanic — Thu, 13 Jul 2006 01:45:00 GMT

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 in the current database, or each database on the current server, respectively. During this loop, the procedures perform whatever action(s) are specified by the user (in the parameters). For instance, what if you want to re-index every table in the database? Sure, you could write your own cursor, but why bother? Use the following T-SQL instead:

EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')'

Convenient, isn't it? But I won't get into any more detail on these. Gregory Larsen does a good job of that in the article linked above.

What I'd like to show instead is a very simple modification I've made to sp_MSforeachtable. It's great to loop over tables and databases, but sometimes we want to loop over routines (a collective term for procedures, functions, triggers, and views) instead. Perhaps you want to grant pemissions to a user. Or perhaps you want to roll out some TSQLMacro updates to every routine in the database instead of just one, as is supported by the current version of the framework... And now you know how it will be done in the next version.

Presenting sp_foreachroutine:

CREATE PROCEDURE dbo.sp_foreachroutine
	@command1 nvarchar(2000), 
	@replacechar nchar(1) = N'?', 
	@command2 nvarchar(2000) = null,
	@command3 nvarchar(2000) = null, 
	@whereand nvarchar(2000) = null,
	@precommand nvarchar(2000) = null, 
	@postcommand nvarchar(2000) = null,
	@routinetype nvarchar(20) = null
AS
BEGIN
	/* This proc returns one or more rows for each procedure (optionally, matching @where), 
		with each procedure defaulting to its own result set */
	/* @precommand and @postcommand may be used to force a single result set via a temp table. */

	/* Preprocessor won't replace within quotes so have to use str(). */
	declare @mscat nvarchar(12)
	select @mscat = ltrim(str(convert(int, 0x0002)))

	if (@precommand is not null)
		exec(@precommand)

	/* Create the select */

	declare @sql nvarchar(4000)
	set @sql =
		N'declare hCForEach cursor global for ' 
		 + N' select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ' 
			+ N' ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' ' 
		 + N' from dbo.sysobjects o '
	         + N' where OBJECTPROPERTY(o.id, N''IsMSShipped'') = 0 '
		 + 	CASE @routinetype
				WHEN 'procedure' THEN ' and OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
				WHEN 'function' THEN ' and (OBJECTPROPERTY(o.id, N''IsScalarFunction'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsTableFunction'') = 1) '
				WHEN 'view' THEN ' and OBJECTPROPERTY(o.id, N''IsView'') = 1 '
				WHEN 'trigger' THEN ' and OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 '
				ELSE ' and ( ' 
					+ ' OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsScalarFunction'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsTableFunction'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsView'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 '
					+ ' ) '
			END
	         + COALESCE(@whereand, '')

	exec(@sql)
	declare @retval int
	select @retval = @@error
	if (@retval = 0)
		exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

	if (@retval = 0 and @postcommand is not null)
		exec(@postcommand)

	return @retval
END
GO

Regular readers of this blog will note that the formatting isn't consistent with my usual standards. But since this was a port from an MS-written proc, I decided to keep things fairly consistent with what was already there. I've also added an additional parameter that wasn't present in sp_MSforeachtable: @routinetype, which lets the user select a specific type of routine to loop over. So, for instance, if you only want views, pass in 'view'. Same for functions ('function'), triggers ('trigger') and procedures ('procedure'). Pass in any other value -- or leave it NULL -- and you'll get all routines in the database.

This procedure keeps the sp_ prefix on purpose; it's meant to be created in the master database, and makes use of the MS-shipped sp_MSforeach_worker stored procedure, which lets it do its work.

Using it is simple. ? is the default substitution character (this can be changed using the @replacechar parameter). So to print a list of all routines in the current database, use:

EXEC sp_foreachroutine 'print ''?'''

For just functions, use the optional @routinetype parameter:

EXEC sp_foreachroutine 'print ''?''', @routinetype = 'function'

Enjoy!

Tokenize UDF

Adam Machanic — Thu, 13 Jul 2006 01:34:00 GMT

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 is it, really? It's splitting up a string based on a delimiter -- in this case, a comma -- but being wary of substring delimiters. In this case, that's a pair of apostrophes, because that's what TSQL uses for strings.

I think this is best illustrated with an example string:

DECLARE @Tokens VARCHAR(50)

SET @Tokens = 'a, ''b'', ''''c'', ''d'', ''e'''', f, ''1,2,3,4'''

The basic split string function that you can find will produce the following output:

SELECT * 
FROM dbo.SplitString(@Tokens, ',')

OutParam
-------------
a
'b'
''c'
'd'
'e''
f
'1
2
3
4'

Well, that's wrong. Because what I want to do is maintain the substrings (or, "tokens," as I like to call them -- thus, Tokenization!)

The output I desire is:

Token
--------
a
'b'
''c', 'd', 'e''
f
'1,2,3,4'

Notice that substrings -- delimited with apostrophes -- should be maintained.

And here's how I've solved this problem...

CREATE FUNCTION dbo.Tokenize
(
	@Input NVARCHAR(2000)
)
RETURNS @Tokens TABLE 
	(
		TokenNum INT IDENTITY(1,1),
		Token NVARCHAR(2000)
	)
AS
BEGIN
	DECLARE @i INT SET @i = 0
	DECLARE @StartChar INT SET @StartChar = 1
	DECLARE @Quote INT SET @Quote = 0	

	DECLARE @Chars TABLE 
	(
		CharNum INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
		TheChar CHAR(1), 
		TheCount INT,
		StartChar INT
	)

	SET @Input = ' , ' + @Input + ' , '
	
	INSERT @Chars (TheChar)
	SELECT SUBSTRING(@Input, n.Number, 1)
	FROM Numbers n
	WHERE n.Number > 0 
		AND n.Number <= LEN(@Input)
	ORDER BY n.Number
	
	UPDATE Chars SET 
		@i = Chars.TheCount = 
			CASE 
				WHEN Chars1.TheChar = ',' 
					AND @Quote % 2 = 0 THEN 0 
				ELSE @i + 1 
			END,
		@Quote = 
			CASE  
				WHEN Chars1.TheChar = '''' THEN @Quote + 1 
				WHEN @i = 0 THEN 0 
				ELSE @Quote 
			END,
		@StartChar = Chars.StartChar =
			CASE
				WHEN @i = 1 THEN Chars1.CharNum - 1
				WHEN @i = 0 THEN @StartChar + 1
				ELSE @StartChar
			END
	FROM @Chars Chars
	JOIN @Chars Chars1 ON Chars1.CharNum = Chars.CharNum + 1

	INSERT @Tokens(Token)
	SELECT
		RTRIM(LTRIM(SUBSTRING(@Input, StartChar, CharNum - StartChar + 1)))
	FROM (
		SELECT StartChar, CharNum
		FROM @Chars
		WHERE TheCount = 0

		UNION ALL

		SELECT 
			MAX
			(
				CASE TheCount 
					WHEN 0 THEN CharNum 
					ELSE 0 
				END
			) + 1, 
			MAX(CharNum)
		FROM @Chars
	) x
	WHERE RTRIM(LTRIM(SUBSTRING(@Input, StartChar, CharNum - StartChar + 1))) NOT IN ('', ',')
	ORDER BY x.StartChar
	RETURN
END

A word of warning: This UDF uses the undocumented -- and unsupported -- "aggregate update" functionality. I've tested thoroughly in this case and believe it works perfectly (and it sure is handy!), but I would advise you to not use it in your own projects without extensive testing! MS doesn't support this one, so handle with care.

And by the way, you need a numbers table to use this thing. Of course.

As for using this thing, it's pretty easy:

DECLARE @Tokens VARCHAR(50)

SET @Tokens = 'a, ''b'', ''''c'', ''d'', ''e'''', f, ''1,2,3,4'''

SELECT Token
FROM dbo.Tokenize(@Tokens)


Token
--------
a
'b'
''c', 'd', 'e''
f
'1,2,3,4'

... and it even appears to work properly!

Enjoy... and application for this and other strange things I've been posting recently coming very, very soon.

"Reflect" a TSQL routine

Adam Machanic — Thu, 13 Jul 2006 01:33:00 GMT

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 strangely wrapped lines and other side-effects that aren't too nice. So I decided to roll my own custom UDF to do the job. The UDF returns a table containing two columns -- a line number (in increments of 100), and a line of text, with a maximum of 4000 characters. Yes, that's a limitation, but if you have stored procedures with line numbers longer than 4000 characters, you probably have much, much bigger problems than can be solved reading this blog.

A friend of mine felt that a parallel of some sort could be drawn between this UDF and .NET reflection, so I named it "ReflectRoutine". I admit, the connection is somewhat tenuous, but it sounds cool, so I'm leaving it. If you can think of a better name, feel free to rename it on your end!

I've commented this one quite heavily -- it uses a lot of string manipulation and can get very confusing. At least, it was incredibly confusing to write! So without further ado...

CREATE FUNCTION dbo.ReflectRoutine
(
	@RoutineName VARCHAR(200)
)
RETURNS @ReturnTbl TABLE 
	(
		LineNum INT IDENTITY(100, 100), 
		OutParam NVARCHAR(4000)
	)
AS
BEGIN
	/* n = current colid for the object */
	DECLARE @n TINYINT
	SET @n = 1

	/* current is the current line of text we're working on */
	DECLARE @Current NVARCHAR(4000)
	SET @Current = ''
	DECLARE @CurrentLen INT
	SET @CurrentLen = 0

	/* overflow and overflow2 will contain 
	characters too big for the current working set */
	DECLARE @Overflow NVARCHAR(4000)
	SET @Overflow = ''
	DECLARE @OverflowLen INT
	SET @OverflowLen = 0
	DECLARE @Overflow2 NVARCHAR(4000)
	SET @Overflow2 = ''

	/* delimiter is our delimiter for string splitting -- crlf */
	DECLARE @Delimiter NCHAR(2)
	SET @Delimiter = NCHAR(13)+NCHAR(10)

	/* how many rows of 4000 characters are we dealing with? */
	DECLARE @maxColId TINYINT
	SELECT 
		@maxColId = MAX(colid) + 1
	from dbo.syscomments
	WHERE id = OBJECT_ID(@RoutineName)

	WHILE @n <= @maxColId
	BEGIN
		/* get the next row of data */
		IF @n < @maxColId
		BEGIN
			SELECT @Current = [text]
			FROM dbo.syscomments
			WHERE id = OBJECT_ID(@RoutineName)
				AND ColId = @n
		END
		ELSE
		BEGIN
			/* Work only with the overflow */
			SELECT @Current = ''
		END

		SET @CurrentLen = DATALENGTH(@Current)/2

		/* The current overflow value will be pushed into the front
		of the current row -- we need to pull off enough characters from the 
		end to make room for the current overflow plus the current string
		and end up with at most 4000 characters.  Overflow2 is the characters
		that will be pulled from the end */

		SET @Overflow2 = 
			RIGHT(@Current,
				CASE
					WHEN (@CurrentLen + @OverflowLen) > 4000 THEN 
						(@CurrentLen + @OverflowLen) - 4000
					ELSE 0
				END)

		/* Now we push the previous overflow into the front of the value and
		get rid of the characters from the end that are now in overflow2 --
		leaving us with a current value that's at most 4000 characters (which 
		is the limit for nvarchar) */

		SET @Current = @Overflow + LEFT(@Current, @CurrentLen - DATALENGTH(@Overflow2)/2)
		SET @CurrentLen = DATALENGTH(@Current)/2

		/* Re-calculate the overflow value.  Find the last occurrence of the 
		delimiter (crlf) within the first 3996 characters of the string.  The
		rest of the string after that delimiter will be split on the next
		round (assume that there could be some concatenation needed with the
		next chunk, so we can't	split until that's done) 

		Also, surround the current value with two delimiters so that the split
		algorithm will work properly */

		SELECT @Overflow = RIGHT(@Current, 
			CASE 
				WHEN @CurrentLen - MAX(Number) - 1 < 0 THEN 0
				ELSE @CurrentLen - MAX(Number) - 1
			END) + @Overflow2,
			@Current = @Delimiter + LEFT(@Current, MAX(Number) - 1) + @Delimiter
		FROM dbo.Numbers
		WHERE (SUBSTRING(@Current, Number, 2) = @Delimiter
				OR Number = @CurrentLen + 1)
			AND Number <= 3996

		SET @CurrentLen = DATALENGTH(@Current)/2
		SET @OverflowLen = DATALENGTH(@Overflow)/2

		/* finally, insert the lines for this round... */
		INSERT	@ReturnTbl (OutParam)
		SELECT	SUBSTRING(@Current, Number + 2,
				CHARINDEX(@Delimiter, @Current, Number + 2) - Number - 2)
		FROM	dbo.Numbers
		WHERE 	Number <= @CurrentLen - 2
			AND SUBSTRING(@Current, Number, 2) = @Delimiter
			AND @CurrentLen > 4
		ORDER BY Number

		SET @n = @n + 1
	END

	RETURN
END

Note, it requires a numbers table -- but regular readers will probably ask, "what doesn't?"

Using it is quite easy... For instance, once it's created if you wish to reflect the UDF itself:

SELECT *
FROM dbo.ReflectRoutine('ReflectRoutine')

I recommend Query Analyzer's "Results in Text" mode with "maximum characters per column" set to a number greater than 4000 -- of course, none of my stored procedures have lines greater than about 150 characters long, so that's probably not a huge deal for most people. As I said, if your lines are that long, you have bigger problems!

So does this thing have any application? Watch this space. More on that coming soon...

Update, December 1, 2005: Don't use LEN when you mean DATALENGTH! I used LEN in the original version of this function and it caused some strange truncations to occur when an overflow happened right at some white space (LEN does not count white space to the right). That bug is fixed in the version now posted. By the way, in case you're reading this for the first time, the application for this is TSQLMacro.

Script out PKs/UNIQUE constraints and referencing FKs

Adam Machanic — Thu, 13 Jul 2006 01:32:00 GMT

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, and then everything needs to be re-created. Scripting all of that stuff out can be very annoying.

With that annoyance in mind, I've written this stored procedure. Put Query Analyzer into Results in Text mode and run ScriptTableConstraints for a table. It will script out DROPs and CREATEs for all primary keys, unique keys, and any foreign keys that reference them -- in the right order. Foreign keys will be dropped first, then any non-clustered PK or UNIQUE constraints, then clustered constraints. Keys will be scripted for re-creation in the reverse order.

... And that's it. This should take some of the annoyance out of doing this kind of work. Here is the stored procedure:

CREATE PROCEDURE ScriptTableConstraints
	@TableName VARCHAR(100)
AS
BEGIN
	SET NOCOUNT ON

	SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+ 
		'DROP CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10) 
		AS [-- Drop Constraints]
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
	WHERE (TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND TC.TABLE_NAME = @TableName)
		OR (TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND FC.TABLE_NAME = @TableName)
	ORDER BY FC.TABLE_NAME DESC,
		INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') ASC
	
	
	SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+ 
		'ADD CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+
		'  ' + TC.CONSTRAINT_TYPE + 
			CASE INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') 
				WHEN 1 THEN ' CLUSTERED' 
				ELSE ' NONCLUSTERED' 
			END + CHAR(13)+CHAR(10)+
		'  (' +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 1 THEN '[' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
		')' + CHAR(13)+CHAR(10)+
		'WITH FILLFACTOR = ' + 
			CONVERT(varchar(3), --Replace 0 FILLFACTOR with 100
				ISNULL(NULLIF(
					INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IndexFillFactor'), 
				0), 100)
				) + CHAR(13)+CHAR(10)+
		'ON [' + sfg.groupname + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
		AS [-- Create PK/UNIQUE Constraints]
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
			AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
	LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
		AND s.id = object_id(TC.TABLE_NAME)
	LEFT JOIN sysfilegroups sfg ON sfg.groupid = s.groupid
	WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
		AND TC.TABLE_NAME = @TableName
	GROUP BY 
		TC.TABLE_SCHEMA,
		TC.TABLE_NAME,
		TC.CONSTRAINT_NAME,
		TC.CONSTRAINT_TYPE,
		sfg.groupname
	ORDER BY INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') DESC
	
	SELECT 'ALTER TABLE [' + FC.TABLE_SCHEMA + '].[' + FC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+ 
		'ADD CONSTRAINT [' + FC.CONSTRAINT_NAME + '] ' + FC.CONSTRAINT_TYPE + CHAR(13)+CHAR(10)+
		'  (' +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
		')' + CHAR(13)+CHAR(10)+
		'REFERENCES [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
		'  (' +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
		')' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
		AS [-- Create FK Constraints]
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_PK ON TC.TABLE_NAME = KCU_PK.TABLE_NAME
			AND TC.CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME
	JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = TC.CONSTRAINT_NAME
	JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK ON FC.TABLE_NAME = KCU_FK.TABLE_NAME
			AND FC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
	WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
		AND TC.TABLE_NAME = @TableName
	GROUP BY 
		FC.TABLE_SCHEMA,
		FC.TABLE_NAME,
		FC.CONSTRAINT_NAME,
		FC.CONSTRAINT_TYPE,
		TC.TABLE_SCHEMA,
		TC.TABLE_NAME
END

Let me know if this helps you or if there's some modification that should be made... Enjoy!

Pattern-based split string

Adam Machanic — Thu, 13 Jul 2006 01:31:00 GMT

"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 split functions, like this one that I wrote can handle only a single delimiter, e.g. a comma. But in the case of splitting for unique words you want all sorts of possible delimiters -- punctuation marks, white space including spaces, possibly numerics, etc.

I believe I've seen other, similar requests for getting unique words before, so I decided to solve the problem. I modified that split string function to accept a pattern of valid characters for the second argument. Anything NOT in the pattern will be treated as a delimiter. I figured that would be a bit more flexible than the other way around, so that people don't have to type in (or figure out) every single possible white space and/or punctuation character.

Anyway, I think the modification is pretty straightforward if you understand the previous function, so check it out and if you have any questions feel free to ask. Here is the function:

CREATE FUNCTION dbo.SplitStringPattern
(
	@List TEXT,
	@Pattern VARCHAR(50)
)
RETURNS @ReturnTbl TABLE (OutParam VARCHAR(20))
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @LeftSplit VARCHAR(7998)
	DECLARE @SplitStart INT SET @SplitStart = 0
	DECLARE @SplitEnd INT
	SET @SplitEnd = 7998

	SET @Pattern = '%' + '[^' + RIGHT(@Pattern, LEN(@Pattern) - 1) + '%'

	DECLARE @Delimiter CHAR(1)
	SELECT @Delimiter = CHAR(MAX(Number))
	FROM dbo.Numbers
	WHERE CHAR(Number) LIKE @Pattern

	SELECT @SplitEnd = MAX(Number)
	FROM dbo.Numbers
	WHERE (SUBSTRING(@List, Number, 1) LIKE @Pattern
			OR Number = DATALENGTH(@List) + 1)
		AND Number BETWEEN @SplitStart AND @SplitEnd

	WHILE @SplitStart < DATALENGTH(@List) - 1
	BEGIN
		SET @LeftSplit = @Delimiter + SUBSTRING(@List, @SplitStart, @SplitEnd - @SplitStart) + @Delimiter

		INSERT @ReturnTbl (OutParam)
		SELECT LTRIM(RTRIM(SUBSTRING(@LeftSplit, Number + 1,
	                    PATINDEX(@Pattern, SUBSTRING(@LeftSplit, Number + 1, LEN(@LeftSplit))) - 1)))
	                AS Value
		FROM   dbo.Numbers
		WHERE  Number <= LEN(@LeftSplit) - 1
			AND SUBSTRING(@LeftSplit, Number, 1) LIKE @Pattern
			AND SUBSTRING(@LeftSplit, Number + 1,
	                    	PATINDEX(@Pattern, SUBSTRING(@LeftSplit, Number + 1, LEN(@LeftSplit))) - 1) <> ''

		SET @SplitStart = @SplitEnd + 1
		SET @SplitEnd = @SplitEnd + 7998

		SELECT @SplitEnd = MAX(Number) + @SplitStart
		FROM dbo.Numbers
		WHERE (SUBSTRING(@List, Number + @SplitStart, 1) LIKE @Pattern
				OR Number+@SplitStart = DATALENGTH(@List) + 1)
			AND Number BETWEEN 1 AND @SplitEnd - @SplitStart
	END

	RETURN
END

A note on the pattern for input: The pattern should be single-character based, and must be delimited with [ ]. Also, you must type in the actual characters in the pattern. I do not recommend using ranges; ranges cause strange side-effects because they tend to include (depending on collation) characters that you wouldn't expect to show up (e.g. characters with umlauts over them). So unless that's what you really want, don't use ranges.

Here's a usage example:

SELECT OutParam
FROM dbo.SplitStringPattern('This is a test.', '[abcdefghijklmnopqrstuvwxyz]')


OutParam             
-------------
This
is
a
test

Enjoy!

Validate a URL from SQL Server

Adam Machanic — Thu, 13 Jul 2006 01:23:00 GMT

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 URL itself -- how can we find out if it's valid?

Thanks to the Object Automation extended stored procedures Microsoft includes with SQL Server, it's quite easy...

CREATE FUNCTION dbo.ValidateURL(@URL VARCHAR(300))
RETURNS BIT
AS
BEGIN
	DECLARE @Object INT
	DECLARE @Return TINYINT
	DECLARE @Valid BIT SET @Valid = 0 --default to false
	
	--create the XMLHTTP object
	EXEC @Return = sp_oacreate 'MSXML2.ServerXMLHTTP.3.0', @Object OUTPUT
	IF @Return = 0
	BEGIN
		DECLARE @Method VARCHAR(350)

		--define setTimeouts method
		--Resolve, Connect, Send, Receive
		SET @Method = 'setTimeouts(45000, 45000, 45000, 45000)'

		--set the timeouts
		EXEC @Return = sp_oamethod @Object, @Method

		IF @Return = 0
		BEGIN
			--define open method
			SET @Method = 'open("GET", "' + @URL + '", false)'
	
			--Open the connection
			EXEC @Return = sp_oamethod @Object, @Method
		END
	
		IF @Return = 0
		BEGIN
			--SEND the request
			EXEC @Return = sp_oamethod @Object, 'send()'
		END
	
		IF @Return = 0
		BEGIN
			DECLARE @Output INT
			EXEC @Return = sp_oamethod @Object, 'status', @Output OUTPUT
	
			IF @Output = 200
			BEGIN
				SET @Valid = 1
			END
		END
	END
	
	--destroy the object
	EXEC sp_oadestroy @Object

	RETURN (@Valid)
END

... And that is it ...

SELECT dbo.ValidateURL('http://www.microsoft.com/sql')

---
1


SELECT dbo.ValidateURL('http://www.XMLisNOTaMAGICbullet.com/')

---
0

Note, you don't want to run this thing against a big table. It runs synchronously and waits for the remote site to respond. That can definitely hold locks open a lot longer than you might want.

UPDATE: Thanks to Marcus Tucker for pointing out that Microsoft.XMLHTTP (the XMLHTTP client object) was not the right choice here. I've updated the UDF to use MSXML2.ServerXMLHTTP.3.0, the XMLHTTP server object, instead.

ANOTHER UPDATE: Added a call to the setTimeouts method, as I discovered that this wasn't behaving the same on differente servers -- apparently there is some default timeout set somewhere; I have no idea where, though. Anyway, the four timeout types have all been set to 45 seconds (45000 ms). Tweak them if you need to.