SQL Split Function

Hey all,

I have used this little doozy for quite some time now. I normally use it to break up string arrays coming in from the middle ware or the front end and throw it into an easy to use table variable. Since it's a scalar function you will be able to examine each row in the "array" by value and/or index (1 based). I know you will find other genius ways to abuse this puppy...

CREATE Function fn_Split(@inString varchar(8000),@delim varchar(5))
Returns @arrSplit table(idx int Primary Key,value varchar(255))
AS
Begin
Declare @idx int,@value varchar(255)
Set @idx = 0
If len(@inString) > 0
Begin
while charindex(@delim,@inString) > 0
Begin
Set @value = Substring(@inString,1,Charindex(@delim,@inString)-1)
If ascii(@inString) <> ascii(@delim)
Begin
Insert @arrSplit(idx,value)
values(@idx,@value)
SET @idx = @idx + 1
End
Set @inString = right(@inString,(len(@inString) - (Charindex(@delim,@inString))))
End
If ascii(@inString) <> ascii(@delim)
Begin
insert @arrSplit(idx,value)
values(@idx,@inString)
End
END
Return
End

Enjoy.

How to know if your MS SQL DB is corrupted

In this article I will give you a procedure which will iterate through an entire servers databases and output whether or not the database is corrupted or not. If you are seeing "Torn Page" errors or Cyclic Redundancy Errors in your Windows or SQL event log, you should definately run this procedure.

Torn pages will look something like "I/O error (torn page) detected during read at offset 0x00000030fe0000...". A CRC error will look something like "I/O error 23(Data error (cyclic redundancy check).) detected during read at offset 0x00000178296000 in file..."

The procedure is;

Create Procedure isp_CheckServerForCorruption
AS
Begin
Declare @DBName sysname,@Message varchar(255)
Declare curDatabases cursor
for select [name] from master..sysdatabases
Open curDatabases
fetch next from curDatabases into @DBName
While @@FETCH_STATUS = 0
Begin Set @Message = '*****BEGIN Processing ' + @DBNAME + '********'
print @Message
declare @SQL varchar(255)
Set @SQL = 'DBCC CHECKDB(' + @DBName + ') with PHYSICAL_ONLY'
EXEC (@SQL)
Set @Message = '*****END Processing ' + @DBNAME + '********'
print @Message
fetch next from curDatabases into @DBName
End
Close curDatabases
Deallocate curDatabases
End

After running this procedure you will see two types of output, for databases that are ok and have no corruption you will get;

*****BEGIN Processing YOURDB********
DBCC results for 'YOURDB'.CHECKDB found 0 allocation errors and 0 consistency errors in database 'YOURDB'.DBCC execution completed.
If DBCC printed error messages, contact your system administrator.
*****END Processing YOURDB********

If you do get errors on a particular database you will get something similar to the following;

*****BEGIN Processing YOURDB********
Server: Msg 8904, Level 16, State 1,
Line 1 Extent (1:420048) in database ID 19 is allocated by more than one allocation object.
Server: Msg 8913, Level 16, State 1,
Line 1Extent (1:420048) is allocated to 'YOURTABLE' and at least one other object.
*****END Processing YOURDB********

Your error output won't look exactly like this since database have different table names and different problems. If you do get errors please refer to my other article, "Fixing a corrupt MS SQL database".

Fixing a corrupt MS SQL Database

I know a lot of you out there have had to deal with a corrupted database and have found there is no real easy way to call a single statement to terminate current users, put the database in single user mode, repair the database, then bring the database out of single user mode. So i have created a procedure that takes two arguments and does just that.

A sample call would look like;

EXEC isp_RepairDB @SearchDBName = 'SESSIONS',@DBCCOption = 'REPAIR_ALLOW_DATA_LOSS'

The first parameter is the name of the database that you want to repair. The second is the level of repair that you want DBCC to do, refer to book online for the three different settings.

Also, keep in mind this WILL take you database offline for the duration of the repair.

Here is the code that you will need to commit to any database on your server.

Create Procedure isp_RepairDB@SearchDBName sysname,@DBCCOption sysname
AS
Begin
Declare @SPID int,@DBName nvarchar(255)

Create table #SPIDS (SPID int,ecid int,status varchar(255),loginame varchar(255) ,hostname varchar(255),blk int,dbname nvarchar(255),cmd varchar(255))

Insert into #SPIDS
EXEC('sp_who') Print 'Killing Connections'

Declare curWho cursor
for Select dbname,spid From #SPIDS

Open curWho

Fetch Next From curWho into @DBName,@SPID
While @@FETCH_STATUS = 0
Begin
If @DBName = cast(@SearchDBName as varchar(255))
Begin
Declare @SQL varchar(255)
Set @SQL = 'Kill ' + cast(@SPID as varchar(5)) + ''
Print @SQL
Print @SearchDBName
EXEC(@SQL)
End
Fetch Next From curWho into @DBName,@SPID
End
Close curWho
Deallocate curWho
Drop Table #SPIDS
Print 'Connections Dead'
EXEC sp_dboption @dbname = @SearchDBName,@optname ='single user',@optvalue = 'true'
Print 'SINGLE USER MODE SET'
Declare @DBCCSQL as varchar(255)
Set @DBCCSQL = 'DBCC CHECKDB(' + @SearchDBName + ',' + @DBCCOption + ')'
Print @DBCCSQL
EXEC(@DBCCSQL)
EXEC sp_dboption @dbname = @SearchDBName,@optname ='single user',@optvalue = 'false'
Print 'SINGLE USER MODE UNSET'
End