Splitting an integer list in TSQL

from:http://snippets.dzone.com/posts/show/774

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitIntegerList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[uspSplitIntegerList]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitIntegerList
--
-- Description:
-- splits a comma separated list of integers and returns the integer list
--
-- Arguments:
-- @list_integers - list of integers
--
-- Notes:
-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
--
-- History:
-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
--
CREATE PROCEDURE uspSplitIntegerList
@list_integers text
AS

SET NOCOUNT ON

DECLARE @InputLen integer -- input text length
DECLARE @TextPos integer -- current position within input text
DECLARE @Chunk varchar(8000) -- chunk within input text
DECLARE @ChunkPos integer -- current position within chunk
DECLARE @DelimPos integer -- position of delimiter
DECLARE @ChunkLen integer -- chunk length
DECLARE @DelimLen integer -- delimiter length
DECLARE @Delimiter varchar(3) -- delimiter
DECLARE @ItemBegPos integer -- item starting position in text
DECLARE @ItemOrder integer -- item order in list

-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )

-- process list
IF @list_integers IS NOT NULL
BEGIN

-- initialize
-- notice that this loop assumes a delimiter length of 1
-- if the delimiter is longer we have to deal with stuff like delimiters straddling the chunk boundaries
SET @InputLen = DATALENGTH(@list_integers)
SET @TextPos = 1
SET @Delimiter = ','
SET @DelimLen = DATALENGTH(@Delimiter)
SET @ItemBegPos = 1
SET @ItemOrder = 1
SET @ChunkLen = 1

-- cycle through input processing chunks
WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
BEGIN

-- get current chunk
SET @Chunk = SUBSTRING(@list_integers, @TextPos, 8000)

-- setup initial variable values
SET @ChunkPos = 1
SET @ChunkLen = DATALENGTH(@Chunk)
SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)

-- loop over the chunk, until the last delimiter
WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
BEGIN

-- insert position
INSERT INTO #list_items (item_order, item_begpos, item_endpos)
VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)

-- adjust positions
SET @ItemOrder = @ItemOrder + 1
SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
SET @ChunkPos = @DelimPos + @DelimLen

-- find next delimiter
SET @DelimPos = CHARINDEX(@Delimiter, @Chunk, @ChunkPos)

END

-- adjust positions
SET @TextPos = @TextPos + @ChunkLen

END

-- handle last item
IF @ItemBegPos <= @InputLen
BEGIN

-- insert position
INSERT INTO #list_items (item_order, item_begpos, item_endpos)
VALUES (@ItemOrder, @ItemBegPos, @InputLen)

END

-- delete the bad items
DELETE FROM #list_items
WHERE item_endpos < item_begpos

-- return list items
SELECT CAST(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1)) AS integer) AS item_integer, item_order, item_begpos, item_endpos
FROM #list_items
WHERE ISNUMERIC(SUBSTRING(@list_integers, item_begpos, (item_endpos - item_begpos + 1))) = 1
ORDER BY item_order

END

DROP TABLE #list_items

RETURN

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Split Delimiter Separated Lists

From:http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-29-udf_Txt_SplitTAB.htm

Creating and parsing lists of items in a string is a frequent
request on SQL Server discussion groups. Storing multiple items
in a list runs counter to relational concepts such as
normalization. However, it's a compact format that most people
find easy to read and that many other programs use for data
exchange.

Here's an example of what I'm talking about. It's a comma-
separated list of the Three Stooges:

'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'

Why 6 stooges? Shemp didn't really like getting poked in the
eye and retired. He was replaced by Curly, Joe, and then
Curly-Joe. They kept on making Stooges shorts into the 1960s.

Once you have a list in a string, it's often necessary to split
it into the individual items. That's the purpose of this
week's UDF. Here's the CREATE FUNCTION script:
/---------- Start copying below this line ----------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.udf_Txt_SplitTAB (

@sInputList varchar(8000) -- List of delimited items
, @Delimiter char(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (Item varchar(8000))
WITH SCHEMABINDING
/*
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The
* strings are trimmed before being returned. Null items are not
* returned so if there are multiple separators between items,
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
select * FROM dbo.udf_Txt_SplitTAB('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count int, @Delim char(10), @Input varchar(128)
SELECT @Count = Count(*)
FROM dbo.udf_Txt_SplitTAB('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
, @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*)
FROM dbo.udf_Txt_SplitTAB(@Input, @Delim)
PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #29
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/
AS BEGIN

DECLARE @Item Varchar(8000)
DECLARE @Pos int -- Current Starting Position
, @NextPos int -- position of next delimiter
, @LenInput int -- length of input
, @LenNext int -- length of next item
, @DelimLen int -- length of the delimiter

SELECT @Pos = 1
, @DelimLen = LEN(@Delimiter) -- usually 1
, @LenInput = LEN(@sInputList)
, @NextPos = CharIndex(@Delimiter, @sInputList, 1)

-- Doesn't work for space as a delimiter
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END


-- loop over the input, until the last delimiter.
While @Pos <= @LenInput and @NextPos > 0 BEGIN

IF @NextPos > @Pos BEGIN -- another delimiter found
SET @LenNext = @NextPos - @Pos
Set @Item = LTrim(RTrim(
substring(@sInputList
, @Pos
, @LenNext)
)
)
IF LEN(@Item) > 0
Insert Into @List Select @Item
-- ENDIF

END -- IF

-- Position over the next item
SELECT @Pos = @NextPos + @DelimLen
, @NextPos = CharIndex(@Delimiter
, @sInputList
, @Pos)
END

-- Now there might be one more item left
SET @Item = LTrim(RTrim(
SUBSTRING(@sInputList
, @Pos
, @LenInput-@Pos + 1)
)
)

IF Len(@Item) > 0 -- Put the last item in, if found
INSERT INTO @List SELECT @Item

RETURN
END
GO

GRANT SELECT ON [dbo].[udf_Txt_SplitTAB] to PUBLIC
GO
\---------- Stop copying above this line -----------------------/

Now, lets break up the Stooges:
/---------- Start copying below this line ----------------------\
SELECT Item as [Stooge]
FROM udf_Txt_SplitTAB(
'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'
, ',')
\---------- Stop copying above this line -----------------------/
(Results)
Stooge
----------------------------------------
Moe
Larry
Shemp
Curly
Joe
Curly-Joe

Of course, you can use other separators if you like. However,
udf_Txt_SplitTAB can't use space as a delimiter. Spaces get
special treatment, they're stripped from the ends of the items.
This next statement illustrates:

/---------- Start copying below this line ----------------------\
SELECT '->' + Item + '<-' as [Item]
FROM udf_Txt_SplitTAB(
'1, 22 , 333, 4444 , , 55555'
, ',')
\---------- Stop copying above this line -----------------------/
(Results)
Item
----------------------------------------
->1<-
->22<-
->333<-
->4444<-
->55555<-

Next issue I'll show you how to pull of the reverse trick and
combine multiple strings into a comma separated list.

INFORMATION_SCHEMA: A Map to Your Database

From:http://www.devx.com/getHelpOn/10MinuteSolution/20561

If you've spent any time at all working with databases, you know that the time comes when you scratch your head and ask, "Now what was the name of that table (or some other object)?" Programming SQL Server effectively has always involved knowing how to find the metadata—that is, the information about the structure of your database. In previous versions of SQL Server, you could either use Enterprise Manager, some of the catalog stored procedures (such as sp_help) or even queried the system tables directly. (See my previous 10-Minute Solution, "Using Metadata to Solve Complex Problems," for an example of using the sysobjects table.)

Now in version 7, SQL Server provides another method for obtaining metadata: the INFORMATION_SCHEMA views. In this article, I introduce these views and show you how they can provide critical information about your database.

What Are the INFORMATION_SCHEMA Views?

The INFORMATION_SCHEMA is part of the SQL-92 standard, so you will be able to use your knowledge of these views in other database engines in the future. In SQL Server, these views exist in each database. However, they are not under the "dbo" user. Instead they are under the ownership of INFORMATION_SCHEMA. Therefore, to access any of the views, you need to preface the view name with "INFORMATION_SCHEMA"—that is, select * from INFORMATION_SCHEMA.TABLES. Each of the views in this collection exposes a different aspect of the metadata of the database. In all, there are 17 views.

Translation, Please

When looking at these views, you will find fields with names that don't readily translate into terms that we currently use in SQL Server. Fortunately, the Books Online has created a translation table for us (see Table 1).

Table 1. Equivalent SQL Server and SQL-92 Names
SQL Server Name	SQL-92 Name
database	Catalog
owner	Schema
object	Object
user-defined data type	Domain

The TABLES View

Let's take a closer look at the TABLES view. Table 2 shows the structure of this view.

Table 2. Structure of TABLES View
Column name	Data type	Description
TABLE_CATALOG	nvarchar(128)	Table qualifier
TABLE_SCHEMA	nvarchar(128)	Table owner
TABLE_NAME	sysname	Table name
TABLE_TYPE	varchar(10)	Type of table; can be VIEW or BASE TABLE

Try selecting from this view. The TABLE_CATALOG contains the name of the database. For most objects, the TABLE_SCHEMA contains "dbo." The INFORMATION_SCHEMA views themselves also appear in this view. However, the TABLE_SCHEMA value for them is INFORMATION_SCHEMA.

Although the name of this view is TABLES, views are also listed. They can be distinguished from tables by the last field, TABLE_TYPE.

SYSOBJECTS versus TABLES View

Of course, you can still obtain a list of all tables in the database by executing the following: select * from sysobjects where type = 'U'. However, there are some major differences between the TABLES view and sysobjects:

• The TABLES view only displays tables on which the user has permissions. It is a useful way of allowing users to see only what they are supposed to know about.
  
  
• The TABLES view is part of a standard. The sysobjects table is not guaranteed to stay the same.
  

However, this does not mean that you will never use the sysobjects table anymore. In fact, my previous select from sysobjects points out a crucial difference. By using the "where type = 'U'," I eliminate all system tables from the list. There is no way to differentiate between system tables and user tables in the TABLES view. (However, in the TABLES view, system tables such as sysobjects are listed with a TABLE_TYPE of view. Perhaps at some point system tables will be under a separate owner rather than dbo.)

The TABLES View in Action

Here is a simple example of how the TABLES view can be used to do much more than just list all the tables. When you look at a database for the first time, it is frequently useful to know how many rows there are in each table. The following code uses the TABLES view to build a cursor of each table name. For each table, it then does a select count(*):

if exists(select * from sysobjects where
   type = 'U' and name = 'my_table_totals')
   drop table my_table_totals
go
create table my_table_totals
(
   table_name varchar(50),
   totalrecs int null
)
go

declare  cr_table_names cursor
for
select table_name
from INFORMATION_SCHEMA.tables
where
TABLE_TYPE = 'BASE TABLE'

declare @table varchar(50) ,@line varchar(500)

open cr_table_names

FETCH NEXT FROM cr_table_names into @table

WHILE @@FETCH_STATUS = 0

BEGIN
   select @line ="insert into my_table_totals (table_name, totalrecs)
   (" + "select '" + @table + "'" + "," + "(select count(*)
   from " + @table + "))"
  
   select @line
   exec (@line)

    FETCH NEXT FROM cr_table_names into @table

END

CLOSE cr_table_names

DEALLOCATE cr_table_names

Changing Compatibility Mode of MSSQL2005

EXEC sp_dbcmptlevel Northwind, 80;

Changing the compatibility mode of database to the earlier version will prevent you from using the new features of MSSQL2005 such as ranking functions, recursive queries, and etc. In order to change back to MSSQL2005 do following:

EXEC sp_dbcmptlevel Northwind, 90;

Outer Join Logical Processing Phases

Outer Join performs

1. Cartesian Product
2. ON filter
3. Adding outer rows.

Outer rows added for rows from the preserved table with no match

Enabling/Disabling xp_cmdshell

In order to use xp_cmdshell in the QA, do the following steps:

1. sp_configure 'xp_cmdshell','1' -- Set it enabled
2. reconfigure -- apply the changes in db

Now you can run xp_cmdshell.

If you want to disable it, do followings:

1. sp_configure 'xp_cmdshell','0' -- Set it disabled
2. reconfigure -- apply the changes in db