DZone Snippets: udf code

DateOnly

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:53:13 GMT



-- Gets the date part of a datetime input

-- Created 08/05/04 by Oskar Austegard

ALTER FUNCTION dbo.fnDateOnly

(

	@DateTime datetime --The input date whose date part we want

)

RETURNS datetime

AS

BEGIN

	RETURN (CONVERT(datetime, CONVERT(varchar(10), @DateTime, 101)))

END

Oskar Austegard
http://mo.notono.us

IsReallyInteger

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:50:56 GMT



----------------------------------------------------------------------------

--Purpose: Checks that the input string is really an integer of the specified type.  

-- To be used in place of the ISNUMERIC function, as it can't be trusted.

-- See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10194

--Inspired by: http://www.aspfaq.com/show.asp?id=2390 

--Created: 10/20/05 by Oskar Austegard.

--Updated: 11/16/05 by Oskar Austegard - fixed bugs

----------------------------------------------------------------------------

ALTER FUNCTION dbo.IsReallyInteger

(  

  @Num varchar(64), --Input string to be checked

  @Type varchar(8) --Type of integer: bigint, int, smallint, tinyint

)  

RETURNS BIT  

BEGIN  

  --Get the absolute value of the number by removing a leading - or +

  DECLARE @AbsNum varchar(64), @Length tinyint

  SET @AbsNum = CASE WHEN LEFT(@Num, 1) IN ('-', '+') THEN SUBSTRING(@Num, 2, LEN(@Num)) ELSE @Num END



  --Remove leading zeros

  WHILE LEN(@AbsNum) > 1 AND LEFT(@AbsNum, 1) = '0'

    SET @AbsNum = SUBSTRING(@AbsNum, 2, LEN(@AbsNum))



  SET @Length = LEN(@AbsNum)  

  --Reinsert the - in negative numbers

  SET @Num = CASE WHEN LEFT(@Num, 1) = '-' THEN '-' + @AbsNum ELSE @AbsNum END



  --Check for empty string or non-digits

  IF @AbsNum = '' OR PATINDEX('%[^0-9]%', @AbsNum) > 0

    RETURN 0



  --Check limits by type

  IF (@Type = 'bigint' AND (@Length < 19 OR (@Length = 19 AND (@AbsNum < '9223372036854775807' OR @Num = '-9223372036854775808'))))

    OR (@Type = 'int' AND (@Length < 10 OR (@Length = 10 AND (@AbsNum < '2147483648' OR @Num = '-2147483648'))))

    OR (@Type = 'smallint' AND (@Length < 5 OR (@Length = 5 AND (@AbsNum < '32768' OR @Num = '-32768'))))

    OR (@Type = 'tinyint' AND LEFT(@Num, 1) <> '-' AND (@Length < 3 OR (@Length = 3 AND @AbsNum < '256')))

    RETURN 1 --Success

  --Else

  RETURN 0 --Failure

END

Oskar Austegard
http://mo.notono.us

Create a Number Table

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:47:15 GMT

Created 08/26/05 by Oskar Austegard (http://mo.notono.us) from article at
http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp
Can be used inline in functions, or to create a standalone Numbers table (as required by dbo.Split).



--Creates a table of sequential numbers, useful for all sorts of things

--Created 08/26/05 by Oskar Austegard from article at 

--http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp

--Limits: @Min and @Max must be between -2147483647 and 2147483647, including.

--If @Max <= @Min, only a single record with @Min is created

ALTER FUNCTION dbo.NumberTable (@Min int, @Max int)

RETURNS @T TABLE (Number int NOT NULL PRIMARY KEY)

AS

BEGIN

  -- Seed the table with the min value

  INSERT @T VALUES (@Min)

  --Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc)

  WHILE @@ROWCOUNT > 0

	BEGIN

	  INSERT @T 

	  --Get the next values by adding the current max - start value + 1 to each existing number

	  --need to calculate increment value first to avoid arithmetic overflow near limits of int

	  SELECT t.Number + (x.MaxNumber - @Min + 1)

	  FROM @T t

	    CROSS JOIN (SELECT MaxNumber = MAX(Number) FROM @T) x --Current max

	  WHERE

	    --Do not exceed the Max - shift the increment to the right side to take advantage of index

	    t.Number <= @Max - (x.MaxNumber - @Min + 1)

	END

  RETURN

END

Fast(er) Split Function

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:43:57 GMT

Created 08/29/05 by Oskar Austegard (http://mo.notono.us) from Erland Sommarskog's code at
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Requires the presence of a Numbers table - which can be created using the dbo.NumberTable function



--Fast(er) Split function using a sequence table (provided by the UDF dbo.NumberTable)

--Created 08/29/05 by Oskar Austegard from Erland Sommarskog's code at

--http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

ALTER FUNCTION Split (

  @List varchar(7998), --The delimited list

  @Del char(1) = ',' --The delimiter

) 

RETURNS @T TABLE (ListID int IDENTITY, Item varchar(7998))

AS

BEGIN

  DECLARE @WrappedList varchar(8000)

  SELECT @WrappedList = @Del + @List + @Del



  INSERT INTO @T (Item)

  SELECT SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)

  FROM dbo.Numbers n

  WHERE n.Number <= LEN(@WrappedList) - 1

    AND SUBSTRING(@WrappedList, n.Number, 1) = @Del

    AND LEN(SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)) > 0

  RETURN

END

Add Variant to List

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:39:34 GMT

If both element and list are nvarchars, using dbo.fnAddToList will be faster

Oskar Austegard
http://mo.notono.us



--Adds a sql_variant element to the end of a sql_variant list, after first inserting a delimiter (nvarchar)

--If both element and list are nvarchars, using dbo.fnAddToList will be faster

ALTER FUNCTION dbo.fnAddVarToList (@VarList sql_variant, @VarNew sql_variant, @Del nvarchar(10))

RETURNS nvarchar(4000)

AS  

BEGIN 

	DECLARE @List nvarchar(4000), @New nvarchar(4000)

	SELECT @List = NULLIF(CONVERT(nvarchar(4000), @VarList), ''), 

		@New = NULLIF(CONVERT(nvarchar(4000), @VarNew), '')

  --First try the concatened string, if null then just the list, 

	--if it too is null, just the new element

	RETURN COALESCE(@List + @Del + @New, @List, @New)

END

Add to List function

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:37:34 GMT



--Adds an element (nvarchar) to the end of a list (nvarchar), after first inserting a delimiter (nvarchar)

ALTER FUNCTION dbo.fnAddToList (@List nvarchar(4000), @New nvarchar(4000), @Del nvarchar(10))

RETURNS nvarchar(4000)

AS  

BEGIN 

	--Treat ''s as NULLs

	SELECT @List = NULLIF(@List, ''), @Del = NULLIF(@Del, ''), @New = NULLIF(@New, '')

  --First try the concatened string, if null then just the list, 

	--if it too is null, just the new element

	RETURN COALESCE(@List + @Del + @New, @List, @New)

END

Oskar Austegard
http://mo.notono.us

IsSmallDate Function - Corrected

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:34:32 GMT

Courtesy of Mounir BEN HAMED, the following is the corrected function.
Background Info: See SQL: ISDATE(@foo) and CAST(@foo AS smalldatetime) and the incorrect Updated IsSmallDate function

Oskar Austegard
http://mo.notono.us



--Checks if a string is a valid smalldatetime

--Updated 04/03/06 by Oskar Austegard after bug find by Mounir Ben Hamed

CREATE FUNCTION dbo.IsSmallDate

(

  @SmallDateString varchar(20) --The input string to check

)

RETURNS BIT

AS

BEGIN

  DECLARE @Result bit

  SET @SmallDateString = LTRIM(RTRIM(@SmallDateString))



  IF ISDATE(@SmallDateString) = 1

   AND CONVERT(datetime, @SmallDateString) BETWEEN '1900-01-01' AND '2079-06-06'

    SET @Result = 1

  ELSE

    SET @Result = 0



  RETURN @Result

END

Split a delimited list into a table

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:31:11 GMT



/*

* Creates a table out of a delimited list

* Input:

* 	@List nvarchar(4000) - delimited list to be split

*		@Del nvarchar(10) - delimiter (trailing spaces are ignored)

* Output: 2 Column table, with columns ListID int, and ListItem nvarchar(200)

* Usage: 

* 	SELECT * FROM Foo 

* 	WHERE FooBar IN (SELECT ListItem FROM dbo.fnSplit('Foo','Bar','FooBar'))

* Updated 08/30/04 by Oskar Austegard

*/

ALTER FUNCTION dbo.fnSplit

(

	@List nvarchar(4000), 

	@Del nvarchar(10) = ','

)

RETURNS @ListTable TABLE 

(

	ListID int IDENTITY , 

	Item nvarchar(200)

)

AS

BEGIN

	DECLARE @LenDel int

	DECLARE @Pos int

	DECLARE @Item nvarchar(200)

	

	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue

	SET @LenDel = LEN(@Del + '|') - 1 



	SET @Pos = CHARINDEX(@Del, @List)

	WHILE @Pos > 0

	BEGIN

		--Get the item

		SET @Item = SUBSTRING(@List, 1, @Pos-1)

		--Add it to the table (if not empty string) 

		IF LEN(LTRIM(@Item)) > 0

			INSERT @ListTable (Item) VALUES (LTRIM(@Item))

		--Remove the item from the list

		SET @List = STUFF(@List, 1, @Pos+@LenDel-1, '')

		--Get the position of the next delimiter

		SET @Pos = CHARINDEX(@Del, @List)		

	END

	

	--Add the last item to the table (if not empty string) 

	IF LEN(LTRIM(@List)) > 0

		INSERT @ListTable (Item) VALUES (LTRIM(@List))



	RETURN 

END

Oskar Austegard
http://mo.notono.us

system_function_schema.fn_datadictionary

austegard (Oskar Austegard) — Thu, 19 Jan 2006 02:19:01 GMT

The following lengthy SQL script will create a system function in SQL Server that returns a data dictionary for any database on the server when called like this:

select * from ::fn_datadictionary()

The output includes the Description of the objects.

Oskar Austegard
http://mo.notono.us



EXEC SP_CONFIGURE 'ALLOW UPDATES', 1

RECONFIGURE WITH OVERRIDE

USE master

go

CREATE FUNCTION system_function_schema.fn_datadictionary ()

RETURNS @DataDictionary TABLE (

 object_id int,

 object_name nvarchar(128),

 object_type nvarchar(128),

 column_order int NULL,

 column_name varchar(60) NULL,

 column_datatype varchar(20) NULL,

 column_length int NULL,

 object_description varchar(500) NULL

)

AS

BEGIN



 DECLARE @object_name nvarchar(128)

-------------------------

--Tables and Columns

-------------------------

 DECLARE table_cursor CURSOR FOR 

  SELECT name FROM sysobjects 

  WHERE type IN ('U') AND status > 1 ORDER BY name



 OPEN table_cursor

 FETCH NEXT FROM table_cursor INTO @object_name

 WHILE @@FETCH_STATUS = 0

 BEGIN

  --Tables

  INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table',

   column_order = 0, column_name = NULL, 

   column_datatype = NULL, column_length = NULL,

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o 

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, null, default) e 

    ON o.name = e.objname

  WHERE o.name = @object_name



  --Columns

   INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table Column',

    column_order = c.colorder, column_name = c.[name], 

   column_datatype = t.[name], column_length = c.[length],

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, N'column', null) e 

    ON c.name = e.objname

  WHERE o.name = @object_name

  ORDER BY c.colorder



    FETCH NEXT FROM table_cursor INTO @object_name

 END

 CLOSE table_cursor

 DEALLOCATE table_cursor



-------------------------

--Views and Columns

-------------------------

 DECLARE view_cursor CURSOR FOR 

  SELECT name FROM sysobjects 

  WHERE type IN ('V') AND status > 1 ORDER BY name



 OPEN view_cursor

 FETCH NEXT FROM view_cursor INTO @object_name

 WHILE @@FETCH_STATUS = 0

 BEGIN

  --Views

  INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'View',

   column_order = 0, column_name = NULL, 

   column_datatype = NULL, column_length = NULL,

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o 

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, null, default) e 

    ON o.name = e.objname

  WHERE o.name = @object_name



  --Columns

   INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'View Column',

    column_order = c.colorder, column_name = c.[name], 

   column_datatype = t.[name], column_length = c.[length],

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, N'column', null) e 

    ON c.name = e.objname

  WHERE o.name = @object_name

  ORDER BY c.colorder



    FETCH NEXT FROM view_cursor INTO @object_name

 END

 CLOSE view_cursor

 DEALLOCATE view_cursor





---------------------------

--Procedures and Parameters

---------------------------

 DECLARE proc_cursor CURSOR FOR 

  SELECT name FROM sysobjects 

  WHERE type IN ('P') AND status > 1 ORDER BY name



 OPEN proc_cursor

 FETCH NEXT FROM proc_cursor INTO @object_name

 WHILE @@FETCH_STATUS = 0

 BEGIN

  --Procedures

  INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure',

   column_order = 0, column_name = NULL, 

   column_datatype = NULL, column_length = NULL,

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o 

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, null, default) e 

    ON o.name = e.objname

  WHERE o.name = @object_name



  --Parameters

   INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure Parameter',

    column_order = c.colorder, column_name = c.[name], 

   column_datatype = t.[name], column_length = c.[length],

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, N'parameter', null) e 

    ON c.name = e.objname

  WHERE o.name = @object_name

   AND t.name <> 'sysname'

  ORDER BY c.colorder



    FETCH NEXT FROM proc_cursor INTO @object_name

 END

 CLOSE proc_cursor

 DEALLOCATE proc_cursor







---------------------------

--Functions and Parameters

---------------------------

 DECLARE func_cursor CURSOR FOR 

  SELECT name FROM sysobjects 

  WHERE type IN ('FN', 'TF', 'IF') AND status > 1 ORDER BY name



 OPEN func_cursor

 FETCH NEXT FROM func_cursor INTO @object_name

 WHILE @@FETCH_STATUS = 0

 BEGIN

  --Functions

  INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function',

   column_order = 0, column_name = NULL, 

   column_datatype = NULL, column_length = NULL,

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o 

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, null, default) e 

    ON o.name = e.objname

  WHERE o.name = @object_name



  --Parameters

   INSERT @DataDictionary 

  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function Parameter',

    column_order = c.colorder, 

   column_name = CASE WHEN c.[name] = '' THEN '' ELSE c.[name] END, 

   column_datatype = t.[name], column_length = c.[length],

    object_description = LTRIM(CAST(e.value AS varchar(500)))

  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype

   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, N'parameter', null) e 

    ON c.name = e.objname

  WHERE o.name = @object_name

   AND t.name <> 'sysname'

  ORDER BY c.colorder



    FETCH NEXT FROM func_cursor INTO @object_name

 END

 CLOSE func_cursor

 DEALLOCATE func_cursor

 RETURN

END

GO

EXEC SP_CONFIGURE 'ALLOW UPDATES', 0

RECONFIGURE WITH OVERRIDE

GO