-- 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

----------------------------------------------------------------------------
--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  

--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 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

--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

--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

--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

/*
* 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

ALTER PROC __FindText
	@query varchar(100)
AS
SELECT DISTINCT name, type 
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
WHERE text LIKE '%' + @query + '%'
ORDER BY name

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