1  
   2  -- Gets the date part of a datetime input
   3  -- Created 08/05/04 by Oskar Austegard
   4  ALTER FUNCTION dbo.fnDateOnly
   5  (
   6  	@DateTime datetime --The input date whose date part we want
   7  )
   8  RETURNS datetime
   9  AS
  10  BEGIN
  11  	RETURN (CONVERT(datetime, CONVERT(varchar(10), @DateTime, 101)))
  12  END

   1  
   2  ----------------------------------------------------------------------------
   3  --Purpose: Checks that the input string is really an integer of the specified type.  
   4  -- To be used in place of the ISNUMERIC function, as it can't be trusted.
   5  -- See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10194
   6  --Inspired by: http://www.aspfaq.com/show.asp?id=2390 
   7  --Created: 10/20/05 by Oskar Austegard.
   8  --Updated: 11/16/05 by Oskar Austegard - fixed bugs
   9  ----------------------------------------------------------------------------
  10  ALTER FUNCTION dbo.IsReallyInteger
  11  (  
  12    @Num varchar(64), --Input string to be checked
  13    @Type varchar(8) --Type of integer: bigint, int, smallint, tinyint
  14  )  
  15  RETURNS BIT  
  16  BEGIN  
  17    --Get the absolute value of the number by removing a leading - or +
  18    DECLARE @AbsNum varchar(64), @Length tinyint
  19    SET @AbsNum = CASE WHEN LEFT(@Num, 1) IN ('-', '+') THEN SUBSTRING(@Num, 2, LEN(@Num)) ELSE @Num END
  20  
  21    --Remove leading zeros
  22    WHILE LEN(@AbsNum) > 1 AND LEFT(@AbsNum, 1) = '0'
  23      SET @AbsNum = SUBSTRING(@AbsNum, 2, LEN(@AbsNum))
  24  
  25    SET @Length = LEN(@AbsNum)  
  26    --Reinsert the - in negative numbers
  27    SET @Num = CASE WHEN LEFT(@Num, 1) = '-' THEN '-' + @AbsNum ELSE @AbsNum END
  28  
  29    --Check for empty string or non-digits
  30    IF @AbsNum = '' OR PATINDEX('%[^0-9]%', @AbsNum) > 0
  31      RETURN 0
  32  
  33    --Check limits by type
  34    IF (@Type = 'bigint' AND (@Length < 19 OR (@Length = 19 AND (@AbsNum < '9223372036854775807' OR @Num = '-9223372036854775808'))))
  35      OR (@Type = 'int' AND (@Length < 10 OR (@Length = 10 AND (@AbsNum < '2147483648' OR @Num = '-2147483648'))))
  36      OR (@Type = 'smallint' AND (@Length < 5 OR (@Length = 5 AND (@AbsNum < '32768' OR @Num = '-32768'))))
  37      OR (@Type = 'tinyint' AND LEFT(@Num, 1) <> '-' AND (@Length < 3 OR (@Length = 3 AND @AbsNum < '256')))
  38      RETURN 1 --Success
  39    --Else
  40    RETURN 0 --Failure
  41  END  

   1  
   2  --Creates a table of sequential numbers, useful for all sorts of things
   3  --Created 08/26/05 by Oskar Austegard from article at 
   4  --http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp
   5  --Limits: @Min and @Max must be between -2147483647 and 2147483647, including.
   6  --If @Max <= @Min, only a single record with @Min is created
   7  ALTER FUNCTION dbo.NumberTable (@Min int, @Max int)
   8  RETURNS @T TABLE (Number int NOT NULL PRIMARY KEY)
   9  AS
  10  BEGIN
  11    -- Seed the table with the min value
  12    INSERT @T VALUES (@Min)
  13    --Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc)
  14    WHILE @@ROWCOUNT > 0
  15  	BEGIN
  16  	  INSERT @T 
  17  	  --Get the next values by adding the current max - start value + 1 to each existing number
  18  	  --need to calculate increment value first to avoid arithmetic overflow near limits of int
  19  	  SELECT t.Number + (x.MaxNumber - @Min + 1)
  20  	  FROM @T t
  21  	    CROSS JOIN (SELECT MaxNumber = MAX(Number) FROM @T) x --Current max
  22  	  WHERE
  23  	    --Do not exceed the Max - shift the increment to the right side to take advantage of index
  24  	    t.Number <= @Max - (x.MaxNumber - @Min + 1)
  25  	END
  26    RETURN
  27  END

   1  
   2  --Fast(er) Split function using a sequence table (provided by the UDF dbo.NumberTable)
   3  --Created 08/29/05 by Oskar Austegard from Erland Sommarskog's code at
   4  --http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
   5  ALTER FUNCTION Split (
   6    @List varchar(7998), --The delimited list
   7    @Del char(1) = ',' --The delimiter
   8  ) 
   9  RETURNS @T TABLE (ListID int IDENTITY, Item varchar(7998))
  10  AS
  11  BEGIN
  12    DECLARE @WrappedList varchar(8000)
  13    SELECT @WrappedList = @Del + @List + @Del
  14  
  15    INSERT INTO @T (Item)
  16    SELECT SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)
  17    FROM dbo.Numbers n
  18    WHERE n.Number <= LEN(@WrappedList) - 1
  19      AND SUBSTRING(@WrappedList, n.Number, 1) = @Del
  20      AND LEN(SUBSTRING(@WrappedList, n.Number + 1, CHARINDEX(@Del, @WrappedList, n.Number + 1) - n.Number - 1)) > 0
  21    RETURN
  22  END

   1  
   2  --Adds a sql_variant element to the end of a sql_variant list, after first inserting a delimiter (nvarchar)
   3  --If both element and list are nvarchars, using dbo.fnAddToList will be faster
   4  ALTER FUNCTION dbo.fnAddVarToList (@VarList sql_variant, @VarNew sql_variant, @Del nvarchar(10))
   5  RETURNS nvarchar(4000)
   6  AS  
   7  BEGIN 
   8  	DECLARE @List nvarchar(4000), @New nvarchar(4000)
   9  	SELECT @List = NULLIF(CONVERT(nvarchar(4000), @VarList), ''), 
  10  		@New = NULLIF(CONVERT(nvarchar(4000), @VarNew), '')
  11    --First try the concatened string, if null then just the list, 
  12  	--if it too is null, just the new element
  13  	RETURN COALESCE(@List + @Del + @New, @List, @New)
  14  END

   1  
   2  --Adds an element (nvarchar) to the end of a list (nvarchar), after first inserting a delimiter (nvarchar)
   3  ALTER FUNCTION dbo.fnAddToList (@List nvarchar(4000), @New nvarchar(4000), @Del nvarchar(10))
   4  RETURNS nvarchar(4000)
   5  AS  
   6  BEGIN 
   7  	--Treat ''s as NULLs
   8  	SELECT @List = NULLIF(@List, ''), @Del = NULLIF(@Del, ''), @New = NULLIF(@New, '')
   9    --First try the concatened string, if null then just the list, 
  10  	--if it too is null, just the new element
  11  	RETURN COALESCE(@List + @Del + @New, @List, @New)
  12  END

   1  
   2  --Checks if a string is a valid smalldatetime
   3  --Updated 04/03/06 by Oskar Austegard after bug find by Mounir Ben Hamed
   4  CREATE FUNCTION dbo.IsSmallDate
   5  (
   6    @SmallDateString varchar(20) --The input string to check
   7  )
   8  RETURNS BIT
   9  AS
  10  BEGIN
  11    DECLARE @Result bit
  12    SET @SmallDateString = LTRIM(RTRIM(@SmallDateString))
  13  
  14    IF ISDATE(@SmallDateString) = 1
  15     AND CONVERT(datetime, @SmallDateString) BETWEEN '1900-01-01' AND '2079-06-06'
  16      SET @Result = 1
  17    ELSE
  18      SET @Result = 0
  19  
  20    RETURN @Result
  21  END

   1  
   2  /*
   3  * Creates a table out of a delimited list
   4  * Input:
   5  * 	@List nvarchar(4000) - delimited list to be split
   6  *		@Del nvarchar(10) - delimiter (trailing spaces are ignored)
   7  * Output: 2 Column table, with columns ListID int, and ListItem nvarchar(200)
   8  * Usage: 
   9  * 	SELECT * FROM Foo 
  10  * 	WHERE FooBar IN (SELECT ListItem FROM dbo.fnSplit('Foo','Bar','FooBar'))
  11  * Updated 08/30/04 by Oskar Austegard
  12  */
  13  ALTER FUNCTION dbo.fnSplit
  14  (
  15  	@List nvarchar(4000), 
  16  	@Del nvarchar(10) = ','
  17  )
  18  RETURNS @ListTable TABLE 
  19  (
  20  	ListID int IDENTITY , 
  21  	Item nvarchar(200)
  22  )
  23  AS
  24  BEGIN
  25  	DECLARE @LenDel int
  26  	DECLARE @Pos int
  27  	DECLARE @Item nvarchar(200)
  28  	
  29  	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue
  30  	SET @LenDel = LEN(@Del + '|') - 1 
  31  
  32  	SET @Pos = CHARINDEX(@Del, @List)
  33  	WHILE @Pos > 0
  34  	BEGIN
  35  		--Get the item
  36  		SET @Item = SUBSTRING(@List, 1, @Pos-1)
  37  		--Add it to the table (if not empty string) 
  38  		IF LEN(LTRIM(@Item)) > 0
  39  			INSERT @ListTable (Item) VALUES (LTRIM(@Item))
  40  		--Remove the item from the list
  41  		SET @List = STUFF(@List, 1, @Pos+@LenDel-1, '')
  42  		--Get the position of the next delimiter
  43  		SET @Pos = CHARINDEX(@Del, @List)		
  44  	END
  45  	
  46  	--Add the last item to the table (if not empty string) 
  47  	IF LEN(LTRIM(@List)) > 0
  48  		INSERT @ListTable (Item) VALUES (LTRIM(@List))
  49  
  50  	RETURN 
  51  END
  52  

   1  
   2  EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
   3  RECONFIGURE WITH OVERRIDE
   4  USE master
   5  go
   6  CREATE FUNCTION system_function_schema.fn_datadictionary ()
   7  RETURNS @DataDictionary TABLE (
   8   object_id int,
   9   object_name nvarchar(128),
  10   object_type nvarchar(128),
  11   column_order int NULL,
  12   column_name varchar(60) NULL,
  13   column_datatype varchar(20) NULL,
  14   column_length int NULL,
  15   object_description varchar(500) NULL
  16  )
  17  AS
  18  BEGIN
  19  
  20   DECLARE @object_name nvarchar(128)
  21  -------------------------
  22  --Tables and Columns
  23  -------------------------
  24   DECLARE table_cursor CURSOR FOR 
  25    SELECT name FROM sysobjects 
  26    WHERE type IN ('U') AND status > 1 ORDER BY name
  27  
  28   OPEN table_cursor
  29   FETCH NEXT FROM table_cursor INTO @object_name
  30   WHILE @@FETCH_STATUS = 0
  31   BEGIN
  32    --Tables
  33    INSERT @DataDictionary 
  34    SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table',
  35     column_order = 0, column_name = NULL, 
  36     column_datatype = NULL, column_length = NULL,
  37      object_description = LTRIM(CAST(e.value AS varchar(500)))
  38    FROM sysobjects o 
  39     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, null, default) e 
  40      ON o.name = e.objname
  41    WHERE o.name = @object_name
  42  
  43    --Columns
  44     INSERT @DataDictionary 
  45    SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table Column',
  46      column_order = c.colorder, column_name = c.[name], 
  47     column_datatype = t.[name], column_length = c.[length],
  48      object_description = LTRIM(CAST(e.value AS varchar(500)))
  49    FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
  50     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, N'column', null) e 
  51      ON c.name = e.objname
  52    WHERE o.name = @object_name
  53    ORDER BY c.colorder
  54  
  55      FETCH NEXT FROM table_cursor INTO @object_name
  56   END
  57   CLOSE table_cursor
  58   DEALLOCATE table_cursor
  59  
  60  -------------------------
  61  --Views and Columns
  62  -------------------------
  63   DECLARE view_cursor CURSOR FOR 
  64    SELECT name FROM sysobjects 
  65    WHERE type IN ('V') AND status > 1 ORDER BY name
  66  
  67   OPEN view_cursor
  68   FETCH NEXT FROM view_cursor INTO @object_name
  69   WHILE @@FETCH_STATUS = 0
  70   BEGIN
  71    --Views
  72    INSERT @DataDictionary 
  73    SELECT object_id = o.[id], object_name = o.[name], object_type = 'View',
  74     column_order = 0, column_name = NULL, 
  75     column_datatype = NULL, column_length = NULL,
  76      object_description = LTRIM(CAST(e.value AS varchar(500)))
  77    FROM sysobjects o 
  78     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, null, default) e 
  79      ON o.name = e.objname
  80    WHERE o.name = @object_name
  81  
  82    --Columns
  83     INSERT @DataDictionary 
  84    SELECT object_id = o.[id], object_name = o.[name], object_type = 'View Column',
  85      column_order = c.colorder, column_name = c.[name], 
  86     column_datatype = t.[name], column_length = c.[length],
  87      object_description = LTRIM(CAST(e.value AS varchar(500)))
  88    FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
  89     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, N'column', null) e 
  90      ON c.name = e.objname
  91    WHERE o.name = @object_name
  92    ORDER BY c.colorder
  93  
  94      FETCH NEXT FROM view_cursor INTO @object_name
  95   END
  96   CLOSE view_cursor
  97   DEALLOCATE view_cursor
  98  
  99  
 100  ---------------------------
 101  --Procedures and Parameters
 102  ---------------------------
 103   DECLARE proc_cursor CURSOR FOR 
 104    SELECT name FROM sysobjects 
 105    WHERE type IN ('P') AND status > 1 ORDER BY name
 106  
 107   OPEN proc_cursor
 108   FETCH NEXT FROM proc_cursor INTO @object_name
 109   WHILE @@FETCH_STATUS = 0
 110   BEGIN
 111    --Procedures
 112    INSERT @DataDictionary 
 113    SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure',
 114     column_order = 0, column_name = NULL, 
 115     column_datatype = NULL, column_length = NULL,
 116      object_description = LTRIM(CAST(e.value AS varchar(500)))
 117    FROM sysobjects o 
 118     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, null, default) e 
 119      ON o.name = e.objname
 120    WHERE o.name = @object_name
 121  
 122    --Parameters
 123     INSERT @DataDictionary 
 124    SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure Parameter',
 125      column_order = c.colorder, column_name = c.[name], 
 126     column_datatype = t.[name], column_length = c.[length],
 127      object_description = LTRIM(CAST(e.value AS varchar(500)))
 128    FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
 129     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, N'parameter', null) e 
 130      ON c.name = e.objname
 131    WHERE o.name = @object_name
 132     AND t.name <> 'sysname'
 133    ORDER BY c.colorder
 134  
 135      FETCH NEXT FROM proc_cursor INTO @object_name
 136   END
 137   CLOSE proc_cursor
 138   DEALLOCATE proc_cursor
 139  
 140  
 141  
 142  ---------------------------
 143  --Functions and Parameters
 144  ---------------------------
 145   DECLARE func_cursor CURSOR FOR 
 146    SELECT name FROM sysobjects 
 147    WHERE type IN ('FN', 'TF', 'IF') AND status > 1 ORDER BY name
 148  
 149   OPEN func_cursor
 150   FETCH NEXT FROM func_cursor INTO @object_name
 151   WHILE @@FETCH_STATUS = 0
 152   BEGIN
 153    --Functions
 154    INSERT @DataDictionary 
 155    SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function',
 156     column_order = 0, column_name = NULL, 
 157     column_datatype = NULL, column_length = NULL,
 158      object_description = LTRIM(CAST(e.value AS varchar(500)))
 159    FROM sysobjects o 
 160     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, null, default) e 
 161      ON o.name = e.objname
 162    WHERE o.name = @object_name
 163  
 164    --Parameters
 165     INSERT @DataDictionary 
 166    SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function Parameter',
 167      column_order = c.colorder, 
 168     column_name = CASE WHEN c.[name] = '' THEN '' ELSE c.[name] END, 
 169     column_datatype = t.[name], column_length = c.[length],
 170      object_description = LTRIM(CAST(e.value AS varchar(500)))
 171    FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
 172     LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, N'parameter', null) e 
 173      ON c.name = e.objname
 174    WHERE o.name = @object_name
 175     AND t.name <> 'sysname'
 176    ORDER BY c.colorder
 177  
 178      FETCH NEXT FROM func_cursor INTO @object_name
 179   END
 180   CLOSE func_cursor
 181   DEALLOCATE func_cursor
 182   RETURN
 183  END
 184  GO
 185  EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
 186  RECONFIGURE WITH OVERRIDE
 187  GO