Adam Machanic : T-SQL, Scripts

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.

Counting occurrences of a substring within a string

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

I have absolutely no idea why anyone wants to do this, but I keep answering the same question in forums: "How do I count the occurrences of a substring [note: usually comma] within a string?"

In an effort to thwart carpal tunnel syndrome, I have created the Ultimate Substring Occurrence Counting UDF.

... And here it is:

CREATE FUNCTION dbo.GetSubstringCount
(
	@InputString TEXT, 
	@SubString VARCHAR(200),
	@NoisePattern VARCHAR(20)
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
	RETURN 
	(
		SELECT COUNT(*)
		FROM dbo.Numbers N
		WHERE
			SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString
			AND PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number + LEN(@SubString), 1)) = 0
			AND 0 = 
				CASE 
					WHEN @NoisePattern = '' THEN 0
					ELSE PATINDEX(@NoisePattern, SUBSTRING(@InputString, N.Number - 1, 1))
				END
	)
END

First note: You need (regular readers, you guessed it) a numbers table.

Okay, so what's it do? Simply put, it returns the number of times @SubString appears within @InputString. But wait! -- Act now and you will receive an additional bonus feature at no extra cost! Can you feel the love?

The @NoisePattern parameter allows the user to put the UDF into "exact match" mode.

For instance, let's say you have a big string containing some text about automobile manufacturers, and for some reason (again, I have no clue why people need this functionality -- fill me in if you do!) you want to count the number of occurrences of the word "auto", but not the number of occurrences of other forms of the word, e.g. "automobile" or .... some word that ends in "auto" (if such a word exists).

By specifying a pattern for @NoisePattern of characters that shouldn't be adjacent to your word, you're telling the UDF that any other characters are safe. Leaving the parameter empty means that all occurrences of the substring will be counted. Examples:

SELECT dbo.GetSubstringCount('We like the autos, the autos that go boom.', 'auto', '')
-- Returns 2

SELECT dbo.GetSubstringCount('Autos are fun.  I like to drive my auto.', 'auto', '')
-- Also returns 2

SELECT dbo.GetSubstringCount('Autos are fun.  I like to drive my auto.', 'auto', '%[a-z]%')
-- Only returns 1 -- The exact match must not have adjacent alphabetic characters

Splitting a string of unlimited length

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

There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited string and producing a table of the values), the best of which are encapsulated in Erland Sommarskog's famous article. My favorite of his string splitting techniques is adapted from a previous example that was created by Anith Sen.

Both of these resources are excellent, but sometimes you just need a little bit more. None of their solutions will split a string larger than 8000 characters in length. So I've produced my own modified version. If you're splitting smaller strings then by all means, use theirs! This is certainly slower, but I do believe it's the fastest way in SQL Server 2000 to split a very long string. You will require a numbers table, so make sure you have it on hand...

Anyway, the code:

CREATE FUNCTION dbo.SplitString
(
	@List TEXT,
	@Delimiter CHAR(1)
)
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 = 7997

	SELECT 
		@SplitEnd = MAX(Number)
	FROM dbo.Numbers
	WHERE 
		(
			REPLACE(SUBSTRING(@List, Number, 1), ' ', CHAR(255)) = 
				REPLACE(@Delimiter, ' ', CHAR(255))
			OR Number = DATALENGTH(@List) + 1
		)
		AND Number BETWEEN @SplitStart AND @SplitEnd

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

		INSERT @ReturnTbl 
		(
			OutParam
		)
		SELECT 
			LTRIM
			(
				RTRIM
				(
					SUBSTRING
					(
						@LeftSplit, 
						Number + 1,
	                    CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1
					)
				)
			) AS Value
		FROM dbo.Numbers
		WHERE  
			Number <= LEN(@LeftSplit) - 1
			AND REPLACE(SUBSTRING(@LeftSplit, Number, 1), ' ', CHAR(255)) = 
				REPLACE(@Delimiter, ' ', CHAR(255))
			AND '' <>
				SUBSTRING
				(
					@LeftSplit, 
					Number + 1, 
					CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1
				)

		SET @SplitStart = @SplitEnd + 1
		SET @SplitEnd = @SplitEnd + 7997

		SELECT 
			@SplitEnd = MAX(Number) + @SplitStart
		FROM dbo.Numbers
		WHERE 
			(
				REPLACE(SUBSTRING(@List, Number + @SplitStart, 1), ' ', CHAR(255)) = 
					REPLACE(@Delimiter, ' ', CHAR(255))
				OR Number + @SplitStart = DATALENGTH(@List) + 1
			)
			AND Number BETWEEN 1 AND @SplitEnd - @SplitStart
	END

	RETURN
END
GO

This code does nothing more than chunk up the string, using the delimiter as boundries, into small enough pieces (< 8000 characters) that CHARINDEX can be used on them. Then the same algorithm from Erland's split string is applied.

Note that this function does not gracefully handle situations such as passing in the wrong delimiter. So please carefully test your code before deploying this!

Using the function is quite simple:

SELECT * 
FROM dbo.SplitString('something, something else, etc, etc, etc...', ',')

Update, February 15, 2005: Fixed so that only 8000 numbers are needed in the Numbers table for this to work. Previously required as many numbers as were present in the string to be split.

Update, February 23, 2009: Four years since the last update and a new bug is discovered by the insightful Craig Hathaway! Fixed the function so that it now handles space as a delimiter. Thanks, Craig!

Pattern-based replacement UDF

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

As a personal challenge, I decided to write a UDF that will work just like T-SQL's REPLACE() function, but using patterns as input.

The first question: How does REPLACE() handle overlapping patterns?

SELECT REPLACE('babab', 'bab', 'c')

-------------------------------------------------- 
cab

(1 row(s) affected)


SELECT REPLACE('bababab', 'bab', 'c')

-------------------------------------------------- 
cac

(1 row(s) affected)

It appears that SQL Server parses the input string from left to right, replacing the first instance of the replacement string, and then continues parsing to the right.

Next question: How to do the replacement on a pattern? As it turns out, this is somewhat trickier than I initially thought. A replacement requires a starting point -- easy to find using PATINDEX -- and an end point. But there is no function for finding the last character of a pattern. So you'll see that the UDF loops character-by-character, testing PATINDEX, in order to find the end of the match. This is useful for situations like:

SELECT dbo.PatternReplace('baaa', 'ba%', 'c')

-- We know that the match starts at character 1... but where does it end?

Anyway, enough background, here's the code:

CREATE FUNCTION dbo.PatternReplace
(
   @InputString VARCHAR(4000),
   @Pattern VARCHAR(100),
   @ReplaceText VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
   DECLARE @Result VARCHAR(4000) SET @Result = ''
   -- First character in a match
   DECLARE @First INT
   -- Next character to start search on
   DECLARE @Next INT SET @Next = 1
   -- Length of the total string -- 8001 if @InputString is NULL
   DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString), 8001)
   -- End of a pattern
   DECLARE @EndPattern INT
 
   WHILE (@Next <= @Len) 
   BEGIN
      SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))
      IF COALESCE(@First, 0) = 0 --no match - return
      BEGIN
         SET @Result = @Result + 
            CASE --return NULL, just like REPLACE, if inputs are NULL
               WHEN  @InputString IS NULL
                     OR @Pattern IS NULL
                     OR @ReplaceText IS NULL THEN NULL
               ELSE SUBSTRING(@InputString, @Next, @Len)
            END
         BREAK
      END
      ELSE
      BEGIN
         -- Concatenate characters before the match to the result
         SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)
         SET @Next = @Next + @First - 1
 
         SET @EndPattern = 1
         -- Find start of end pattern range
         WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0
            SET @EndPattern = @EndPattern + 1
         -- Find end of pattern range
         WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0
               AND @Len >= (@Next + @EndPattern - 1)
            SET @EndPattern = @EndPattern + 1

         --Either at the end of the pattern or @Next + @EndPattern = @Len
         SET @Result = @Result + @ReplaceText
         SET @Next = @Next + @EndPattern - 1
      END
   END
   RETURN(@Result)
END

... And here's how you run it, with some sample outputs showing that it does, indeed, appear to work:

SELECT dbo.PatternReplace('babab', 'bab', 'c')
                                                   
-------------------------------------------------- 
cab

(1 row(s) affected)


SELECT dbo.PatternReplace('babab', 'b_b', 'c')
                                                   
-------------------------------------------------- 
cab

(1 row(s) affected)


SELECT dbo.PatternReplace('bababe', 'b%b', 'c')

                                                   
-------------------------------------------------- 
cabe

(1 row(s) affected)

Hopefully this will help someone, somewhere. I haven't found any use for it yet :)

Thanks to Steve Kass for posting some single-character replacement code which I based this UDF on.

Update, January 10, 2005: Thanks to Frank Kalis, I've tracked down some problems with the original UDF. The version posted here has been fixed and now should respond identically to the T-SQL REPLACE function when NULLs or non-pattern-based arguments are passed in. The following example pairs should return the same values (and do, at this point!)

SELECT dbo.PatternReplace(NULL, '', 'abc')
SELECT REPLACE(NULL, '', 'abc')

SELECT dbo.PatternReplace('abc', '', NULL)
SELECT REPLACE('abc', '', NULL)

SELECT dbo.PatternReplace('abc', NULL, '')
SELECT REPLACE('abc', NULL, '')

SELECT dbo.PatternReplace('abc', 'b', '')
SELECT REPLACE('abc', 'b', '')

SELECT dbo.PatternReplace('adc', 'b', '')
SELECT REPLACE('adc', 'b', '')