CodeKeep SQL Feed

Configuring Email in Our Database

Wed, 16 Jan 2013 01:10:29 GMT

Description: Configuring Email in Our Sql Server Database

Link: http://www.codekeep.net/snippets/bbfdebb0-1fba-43a5-8207-4c196de6e867.aspx

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Rebuild or reorganize indexes (with configuration)

Thu, 04 Oct 2012 07:21:40 GMT

Description: http://www.sqlmusings.com adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online (http://msdn.microsoft.com/en-us/library/ms188917.aspx)

Link: http://www.codekeep.net/snippets/ecc0ee74-ae47-45e0-9d65-39a69e9c205a.aspx

-- http://www.sqlmusings.com
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON

-- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online 
-- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)
 
-- =======================================================
-- || Configuration variables:
-- || - 10 is an arbitrary decision point at which to
-- || reorganize indexes.
-- || - 30 is an arbitrary decision point at which to
-- || switch from reorganizing, to rebuilding.
-- || - 0 is the default fill factor. Set this to a
-- || a value from 1 to 99, if needed.
-- =======================================================
DECLARE @reorg_frag_thresh   float		SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float		SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint	SET @fill_factor         = 80
DECLARE @report_only         bit		SET @report_only         = 1

-- added (DS) : page_count_thresh is used to check how many pages the current table uses
DECLARE @page_count_thresh	 smallint	SET @page_count_thresh   = 1000
 
-- Variables required for processing.
DECLARE @objectid       int
DECLARE @indexid        int
DECLARE @partitioncount bigint
DECLARE @schemaname     nvarchar(130) 
DECLARE @objectname     nvarchar(130) 
DECLARE @indexname      nvarchar(130) 
DECLARE @partitionnum   bigint
DECLARE @partitions     bigint
DECLARE @frag           float
DECLARE @page_count     int
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      TABLE(
                          objectid     int,
                          indexid      int,
                          partitionnum int,
                          frag         float,
								  page_count   int
                        )
 
-- Conditionally select tables and indexes from the
-- sys.dm_db_index_physical_stats function and
-- convert object and index IDs to names.
INSERT INTO
    @table_var
SELECT
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
	[page_count]				   AS page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
	AND
	page_count > @page_count_thresh
	AND
    index_id > 0
	
 
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
    SELECT * FROM @table_var
 
-- Open the cursor.
OPEN partitions
 
-- Loop through the partitions.
WHILE (1=1) BEGIN
    FETCH NEXT
        FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag, @page_count
 
    IF @@FETCH_STATUS < 0 BREAK
 
    SELECT
        @objectname = QUOTENAME(o.[name]),
        @schemaname = QUOTENAME(s.[name])
    FROM
        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas as s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
    WHERE
        o.[object_id] = @objectid
 
    SELECT
        @indexname = QUOTENAME([name])
    FROM
        sys.indexes WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid
 
    SELECT
        @partitioncount = count (*)
    FROM
        sys.partitions WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid
 
    -- Build the required statement dynamically based on options and index stats.
    SET @intentions =
        @schemaname + N'.' +
        @objectname + N'.' +
        @indexname + N':' + CHAR(13) + CHAR(10)
    SET @intentions =
        REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
        @intentions
    SET @intentions = @intentions +
        N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
        N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
 
    IF @frag < @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' + @objectname +
            N' REORGANIZE; ' + 
            N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + 
            N' ' + @indexname + ';'

    END
    IF @frag >= @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' +     @objectname +
            N' REBUILD'
    END
    IF @partitioncount > 1 BEGIN
        SET @intentions = @intentions +
            N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
    END
    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
        SET @intentions = @intentions +
            N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
    END
 
    -- Execute determined operation, or report intentions
    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N' EXECUTING: ' + @command
        PRINT @intentions	    
        EXEC (@command)
    END ELSE BEGIN
        PRINT @intentions
    END
	PRINT @command

END
 
-- Close and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
 
GO

Sql CLR

Wed, 29 Aug 2012 05:03:07 GMT

Description: Sql clr sample

Link: http://www.codekeep.net/snippets/39448622-0400-48f1-bb9b-1c5efc9f878b.aspx

--create dll
public partial class SqlTransformer
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SqlToXmlFile(SqlXml xmlData, SqlString fileName)
    {
        XmlDocument xmlDoc = new XmlDocument();

        try
        {
            xmlDoc.LoadXml(xmlData.Value);
            xmlDoc.Save(fileName.Value);
        }
        catch (Exception ex)
        {
            xmlDoc.InnerXml = String.Format("<Error>{0}</Error>", ex.Message);
        }
    }


--import assembly into SQL
ALTER DATABASE TradarEX SET trustworthy ON

CREATE ASSEMBLY SqlTransform 
from '\\thunderdev\TradarLicence\Helper.SQLCLR.SqlTransformer.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

--create proc from imported dll
CREATE PROC SqlToXmlFile
@xmlData XML,
@fileName nvarchar(2000)
as
EXTERNAL NAME SqlTransform.SqlTransformer.SqlToXmlFile


--test CLR proc
Declare @testXML XML
SET @testXML = 

(SELECT RecId, ValuationDate, PB
 FROM tblCFDRec
 FOR XML PATH('Recs'), TYPE)
EXEC SqlToXmlFile @testXML, '\\thunderdev\TradarLicence\test.xml'

--xml query
SELECT 
( 
	SELECT 'White' AS Color1,
	'Blue' AS Color2,
	'Black' AS Color3,
	'Light' AS 'Color4/@Special',
	'Green' AS Color4,
	'Red' AS Color5
	FOR
	XML PATH('Colors'),
	TYPE
),
( 
	SELECT 'Apple' AS Fruits1,
	'Pineapple' AS Fruits2,
	'Grapes' AS Fruits3,
	'Melon' AS Fruits4
	FOR
	XML PATH('Fruits'),
	TYPE
)
FOR XML PATH(''),
ROOT('SampleXML')

DECLARE @xmlFile as XML
SET @xmlFile = '<Jobs><Job><Description>Barclays Derivatives Export</Description><ShortName Id="2824">BCLY_DRV_EXPORT</ShortName></Job><Job><Description>Onboarding process for introducing new secs,funds, and counterparties</Description><ShortName Id="6354">BCLY_DRV_DEX_ONBOARD</ShortName></Job></Jobs>'
SELECT 	paramValues.s.value('(.)[1]', 'varchar(50)')
from	@xmlFile.nodes('/Jobs/Job/ShortName/@Id') as paramValues(s)

Get last inserted Identity

Fri, 17 Aug 2012 00:12:39 GMT

Description: Example usage of @@Identity, SCOPE_IDENTITY() and IDENT_CURRENT()

Link: http://www.codekeep.net/snippets/873962b8-b68d-4f4f-9ad4-a3ffcdfdc5cd.aspx

SELECT @@IDENTITY

-- returns the last IDENTITY value produced on a connection, limited to current session but not limited to scope

SELECT SCOPE_IDENTITY()

-- returns the last IDENTITY value produced limited to current scope (current batch of SQL or stored proc)

SELECT IDENT_CURRENT('tablename')

--  returns the identity value generated for a specific table in any session and any scope

Get GUID in TSQL

Thu, 16 Aug 2012 00:12:56 GMT

Description: Describes how to get a GUID in TSQL

Link: http://www.codekeep.net/snippets/bf532bfc-5f6a-43ef-8b6e-23883a5bdcfc.aspx

SELECT newid()

How to shrink the transaction log file on SQL 2008

Fri, 20 Jul 2012 08:20:42 GMT

Description: The truncate log option has been removed so for a database in Simple mode this is an alternative

Link: http://www.codekeep.net/snippets/4beea9cc-62d6-4c92-a2d8-642fdf6793c1.aspx

DBCC SHRINKFILE(DB_log)

(db in simple mode only)

SQL UPDATE Multiple Rows from table

Tue, 10 Jul 2012 09:58:53 GMT

Description: SQL UPDATE Multiple Rows from table will update multiple rows in one table using values in another table.

Link: http://www.codekeep.net/snippets/18634516-4d4f-42c0-9ea4-6e946eae3580.aspx

UPDATE suppliers
SET supplier_name =
( SELECT customers.name
 FROM customers
 WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
   ( SELECT customers.name
     FROM customers
     WHERE customers.customer_id = suppliers.supplier_id);

CSV Split SQL

Wed, 06 Jun 2012 04:02:35 GMT

Description: returns temp table with rows from csv

Link: http://www.codekeep.net/snippets/c0f2b5c9-16c5-4858-bb59-3ef4504225a9.aspx

create FUNCTION [dbo].[SplitCSV] (@CSVString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (id int identity(1,1),items VARCHAR(8000))
AS
BEGIN
DECLARE @pos INT;
DECLARE @slice VARCHAR(8000);
SELECT @pos = 1;
IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;
WHILE @pos!= 0
BEGIN
SET @pos = CHARINDEX(@Delimiter,@CSVString);
IF @pos != 0
SET @slice = LEFT(@CSVString, @pos - 1);
ELSE
SET @slice = @CSVString;
IF( LEN(@slice) > 0)
INSERT INTO @temptable(Items) VALUES (@slice);
SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);
IF LEN(@CSVString) = 0 BREAK;
END
RETURN
END

Delete all tables in SQL Server

Fri, 01 Jun 2012 02:32:24 GMT

Description: Delete all tables in SQL Server

Link: http://www.codekeep.net/snippets/98cb2296-2e0e-4810-9416-3a18fd5f7829.aspx

CREATE PROCEDURE sp_DeleteAllData
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO

Log Parser - Query IIS logs from multiple machines

Wed, 23 May 2012 10:14:16 GMT

Description: Log Parser - Query IIS logs from multiple machines

Link: http://www.codekeep.net/snippets/5c728b89-367c-40e9-9350-652281e41263.aspx

SELECT * FROM \\machine1\c$\inetpub\logs\LogFiles\W3SVC1\u_ex*.log, \\machine1\c$\inetpub\logs\LogFiles\W3SVC1\u_*.log
WHERE s-port = 80

Query Sproc contents

Tue, 08 May 2012 08:33:38 GMT

Description: Query Sproc contents

Link: http://www.codekeep.net/snippets/b1b31f29-f70c-4615-a977-d7ab7947942f.aspx

select SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_DEFINITION from information_schema.routines
where routine_type='PROCEDURE'
--and SPECIFIC_SCHEMA like 'cms%' 
and ROUTINE_DEFINITION like '%meta%'
order by SPECIFIC_SCHEMA

Find all sql server running processes

Mon, 07 May 2012 09:56:40 GMT

Description: Find all sql server running processes and transations

Link: http://www.codekeep.net/snippets/474e8e98-0fcc-4d55-9360-2ed150ad4148.aspx

sp_who2 

Select Distinct object_name(l.rsc_objid), l.req_spid, p.loginame
from master.dbo.syslockinfo l (nolock) join
master.dbo.sysprocesses p (nolock) on l.req_spid=p.spid
where object_name(l.rsc_objid) is not null

kill @spid

Check and reclaim disk space

Wed, 28 Mar 2012 09:26:38 GMT

Description: check free space and recycle errorlog to claim space back

Link: http://www.codekeep.net/snippets/749fce14-0310-4dfc-8e44-40bf222b3747.aspx

-- check free space
EXEC master..xp_fixeddrives
--drive MB free
------- -----------
--C 0 <------------- HELP !!!!!
--F 115360
--G 253473 
-- lets recycle the SQL Errorlog a few times to claim some space back
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog WAITFOR DELAY '00:00:02'
GO
EXEC sp_cycle_errorlog
GO

-- check free space again
EXEC master..xp_fixeddrives
--drive MB free
------- -----------
--C 2500
--F 115360
--G 253477

Tablespace.sql

Wed, 28 Mar 2012 09:23:59 GMT

Description: Show Size, Space Used, Unused Space, Type, and Name of all database files

Link: http://www.codekeep.net/snippets/72c83002-abca-4890-942c-c7bf0377013a.aspx

set nocount on


print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
	[FileSizeMB]	=
		convert(numeric(10,2),sum(round(a.size/128.,2))),
        [UsedSpaceMB]	=
		convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
        [UnusedSpaceMB]	=
		convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
	[Type] =
		case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
	[DBFileName]	= isnull(a.name,'*** Total for all files ***')
from
	sysfiles a
group by
	groupid,
	a.name
	with rollup
having
	a.groupid is null or
	a.name is not null
order by
	case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
	a.groupid,
	case when a.name is null then 99 else 0 end,
	a.name




create table #TABLE_SPACE_WORK
(
	TABLE_NAME 	sysname		not null ,
	TABLE_ROWS 	numeric(18,0)	not null ,
	RESERVED 	varchar(50) 	not null ,
	DATA 		varchar(50) 	not null ,
	INDEX_SIZE 	varchar(50) 	not null ,
	UNUSED 		varchar(50) 	not null ,
)

create table #TABLE_SPACE_USED
(
	Seq		int		not null	
	identity(1,1)	primary key clustered,
	TABLE_NAME 	sysname		not null ,
	TABLE_ROWS 	numeric(18,0)	not null ,
	RESERVED 	varchar(50) 	not null ,
	DATA 		varchar(50) 	not null ,
	INDEX_SIZE 	varchar(50) 	not null ,
	UNUSED 		varchar(50) 	not null ,
)

create table #TABLE_SPACE
(
	Seq		int		not null
	identity(1,1)	primary key clustered,
	TABLE_NAME 	SYSNAME 	not null ,
	TABLE_ROWS 	int	 	not null ,
	RESERVED 	int	 	not null ,
	DATA 		int	 	not null ,
	INDEX_SIZE 	int	 	not null ,
	UNUSED 		int	 	not null ,
	USED_MB				numeric(18,4)	not null,
	USED_GB				numeric(18,4)	not null,
	AVERAGE_BYTES_PER_ROW		numeric(18,5)	null,
	AVERAGE_DATA_BYTES_PER_ROW	numeric(18,5)	null,
	AVERAGE_INDEX_BYTES_PER_ROW	numeric(18,5)	null,
	AVERAGE_UNUSED_BYTES_PER_ROW	numeric(18,5)	null,
)

declare @fetch_status int

declare @proc 	varchar(200)
select	@proc	= rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
	TABLE_NAME	= 
	rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
	INFORMATION_SCHEMA.TABLES 
where
	TABLE_TYPE	= 'BASE TABLE'
order by
	1

open Cur_Cursor

declare @TABLE_NAME 	varchar(200)

select @fetch_status = 0

while @fetch_status = 0
	begin

	fetch next from Cur_Cursor
	into
		@TABLE_NAME

	select @fetch_status = @@fetch_status

	if @fetch_status <> 0
		begin
		continue
		end

	truncate table #TABLE_SPACE_WORK

	insert into #TABLE_SPACE_WORK
		(
		TABLE_NAME,
		TABLE_ROWS,
		RESERVED,
		DATA,
		INDEX_SIZE,
		UNUSED
		)
	exec @proc @objname = 
		@TABLE_NAME ,@updateusage = 'true'


	-- Needed to work with SQL 7
	update #TABLE_SPACE_WORK
	set
		TABLE_NAME = @TABLE_NAME

	insert into #TABLE_SPACE_USED
		(
		TABLE_NAME,
		TABLE_ROWS,
		RESERVED,
		DATA,
		INDEX_SIZE,
		UNUSED
		)
	select
		TABLE_NAME,
		TABLE_ROWS,
		RESERVED,
		DATA,
		INDEX_SIZE,
		UNUSED
	from
		#TABLE_SPACE_WORK

	end 	--While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
	(
	TABLE_NAME,
	TABLE_ROWS,
	RESERVED,
	DATA,
	INDEX_SIZE,
	UNUSED,
	USED_MB,
	USED_GB,
	AVERAGE_BYTES_PER_ROW,
	AVERAGE_DATA_BYTES_PER_ROW,
	AVERAGE_INDEX_BYTES_PER_ROW,
	AVERAGE_UNUSED_BYTES_PER_ROW

	)
select
	TABLE_NAME,
	TABLE_ROWS,
	RESERVED,
	DATA,
	INDEX_SIZE,
	UNUSED,
	USED_MB			=
		round(convert(numeric(25,10),RESERVED)/
		convert(numeric(25,10),1024),4),
	USED_GB			=
		round(convert(numeric(25,10),RESERVED)/
		convert(numeric(25,10),1024*1024),4),
	AVERAGE_BYTES_PER_ROW	=
		case
		when TABLE_ROWS <> 0
		then round(
		(1024.000000*convert(numeric(25,10),RESERVED))/
		convert(numeric(25,10),TABLE_ROWS),5)
		else null
		end,
	AVERAGE_DATA_BYTES_PER_ROW	=
		case
		when TABLE_ROWS <> 0
		then round(
		(1024.000000*convert(numeric(25,10),DATA))/
		convert(numeric(25,10),TABLE_ROWS),5)
		else null
		end,
	AVERAGE_INDEX_BYTES_PER_ROW	=
		case
		when TABLE_ROWS <> 0
		then round(
		(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
		convert(numeric(25,10),TABLE_ROWS),5)
		else null
		end,
	AVERAGE_UNUSED_BYTES_PER_ROW	=
		case
		when TABLE_ROWS <> 0
		then round(
		(1024.000000*convert(numeric(25,10),UNUSED))/
		convert(numeric(25,10),TABLE_ROWS),5)
		else null
		end
from
	(
	select
		TABLE_NAME,
		TABLE_ROWS,
		RESERVED	= 
		convert(int,rtrim(replace(RESERVED,'KB',''))),
		DATA		= 
		convert(int,rtrim(replace(DATA,'KB',''))),
		INDEX_SIZE	= 
		convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
		UNUSED		= 
		convert(int,rtrim(replace(UNUSED,'KB','')))
	from
		#TABLE_SPACE_USED aa
	) a
order by
	TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED 
drop table #TABLE_SPACE

Parse a list of integers usf_ParseIntegerArray

Thu, 26 Jan 2012 16:35:03 GMT

Description: Takes a comma delimited list of integers as its input, returns a table listing those integers in a single column.

Link: http://www.codekeep.net/snippets/ea182584-42d0-45e4-b91c-210a3712c5b0.aspx

CREATE FUNCTION dbo.usf_ParseIntegerArray 
(
@IntArray VARCHAR(MAX)
)
RETURNS 
@c TABLE (Col INT NOT NULL PRIMARY KEY)

AS
BEGIN

--remove whitespace
SET @IntArray = ','+@IntArray+','
SET @IntArray = REPLACE(@IntArray,CHAR(13),'')
SET @IntArray = REPLACE(@IntArray,CHAR(10),'')
SET @IntArray = REPLACE(@IntArray,CHAR(9) ,'')
SET @IntArray = REPLACE(@IntArray,CHAR(9) ,'')
SET @IntArray = REPLACE(@IntArray,   ' '  ,'')




DECLARE
	  @x INT
	, @e INT
	, @int INT
	, @CurrentCommaPos INT
	, @NextCommaPos INT
	, @intLength INT
	
SET @x = 1	
SET @e = dbo.usf_CountChar(@IntArray,',') - 1
SET @CurrentCommaPos = 1
SET @NextCommaPos = 2


WHILE @x <= @e
	BEGIN
		
		SET @NextCommaPos = CHARINDEX(',',@IntArray,@CurrentCommaPos+1)
		
		SET @intLength = @NextCommaPos - (@CurrentCommaPos+1)
		SET @int =
					CAST
					(
					SUBSTRING
						(
						 @IntArray
						,@CurrentCommaPos+1
						,@intLength
						)
					AS INT
					)
		IF @int NOT IN (SELECT Col FROM @c) AND @int IS NOT NULL INSERT INTO @c VALUES (@int)
		
		SET @CurrentCommaPos = @NextCommaPos
		
	SET @x = @x + 1
	END

RETURN 
END
;
GO

Hints for moving tables between SQL Schemas

Wed, 25 Jan 2012 08:06:51 GMT

Description: How to move tables between different schemas within SQL Server

Link: http://www.codekeep.net/snippets/3deab9ab-e6f7-4528-949b-b2fe91babbf4.aspx

ALTER SCHEMA bnk TRANSFER dbo.test
              ^              ^
              |              |
           to schema      from schema

List alter statements

SELECT 'ALTER SCHEMA newSchema TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'

recover db from suspect state

Fri, 16 Dec 2011 03:34:11 GMT

Description: recover db from suspect state

Link: http://www.codekeep.net/snippets/d98d1fbc-5bfd-43fd-9bb2-3a482b7af278.aspx

EXEC sp_resetstatus 'dbname';
ALTER DATABASE dbname SET EMERGENCY
DBCC checkdb('dbname')
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbname SET MULTI_USER

convert ((int)year,(int)month,(int)day) to Datetime

Thu, 01 Dec 2011 10:09:35 GMT

Description: convert ((int)year,(int)month,(int)day) to Datetime

Link: http://www.codekeep.net/snippets/ccc0d9a6-0d18-4f2d-adbf-266027bc254c.aspx

SELECT
   CAST(
      CAST(year AS VARCHAR(4)) +
      RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
      RIGHT('0' + CAST(day AS VARCHAR(2)), 2) 
   AS DATETIME)

Creating Random SQL Server Test Data

Thu, 17 Nov 2011 05:11:59 GMT

Description: http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/249/creating-random-sql-server-test-data.aspx

Link: http://www.codekeep.net/snippets/7189e2d4-6e9b-4a78-9a5a-0754fbcf4689.aspx

CREATE TABLE dbo.TestTableSize
(
	MyKeyField VARCHAR(10) NOT NULL,
	MyDate1 DATETIME NOT NULL,
	MyDate2 DATETIME NOT NULL,
	MyDate3 DATETIME NOT NULL,
	MyDate4 DATETIME NOT NULL,
	MyDate5 DATETIME NOT NULL
)

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME

SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0

WHILE @RowCount < 3000000
BEGIN
	SET @RowString = CAST(@RowCount AS VARCHAR(10))
	SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
	SET @InsertDate = DATEADD(dd, @Random, GETDATE())
	
	INSERT INTO TestTableSize
		(MyKeyField
		,MyDate1
		,MyDate2
		,MyDate3
		,MyDate4
		,MyDate5)
	VALUES
		(REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
		, @InsertDate
		,DATEADD(dd, 1, @InsertDate)
		,DATEADD(dd, 2, @InsertDate)
		,DATEADD(dd, 3, @InsertDate)
		,DATEADD(dd, 4, @InsertDate))

	SET @RowCount = @RowCount + 1
END

SQL Collate Multiple Rows Into One

Thu, 27 Oct 2011 13:23:53 GMT

Description: SQL Collate and combine multiple rows into single row with comma between rows.

Link: http://www.codekeep.net/snippets/a54c07cd-5eb5-4bf6-a43e-306c245c946a.aspx

DECLARE @List VARCHAR(8000)  
SELECT @List = COALESCE(@List + ', ', '') + (ServiceCode)
FROM  Services
WHERE ServiceID < 10 

PRINT @List

----------------- ALT METHOD ----------------

SELECT 
ROW_NUMBER() OVER(ORDER BY C.CaseID) AS ID,
CaseID,
		REPLACE(REPLACE((SELECT SVC.ServiceCode	 
			FROM  Services SVC (NOLOCK)
				INNER JOIN dbo.CaseServices CS
					ON SVC.ServiceID = CS.ServiceID
				WHERE CS.CaseID = C.CaseID
			FOR XML PATH('')),'<ServiceCode>',''),'</ServiceCode>',',')
	FROM Cases C (NOLOCK) 
	WHERE OrderID = 36911

SQL Temp Table Variable

Thu, 27 Oct 2011 10:54:42 GMT

Description: SQL Temp Table Variable. Example shows how to insert list of database names into a temp table.

Link: http://www.codekeep.net/snippets/3f4da5ab-7b44-4a1a-adaf-8f75af3cbd3e.aspx

DECLARE @TVar TABLE (DBName VARCHAR(50) NOT NULL)

INSERT INTO @TVar (DBName) 
SELECT dbo.sysdatabases.name AS DBname
FROM dbo.sysaltfiles INNER JOIN dbo.sysdatabases ON 
            dbo.sysaltfiles.dbid = dbo.sysdatabases.dbid 
WHERE dbo.sysdatabases.name 
            NOT IN ( 
					'Northwind', 
					'pubs', 
					'tempdb', 
					'master', 
					'model', 
					'msdb',
					'ReportServerTempDB',
					'GTTemp',
					'RMinder'
					)
	AND dbo.sysaltfiles.name NOT LIKE '%log'
	AND dbo.sysaltfiles.name NOT LIKE '%Restore'

SQL Date Only

Wed, 26 Oct 2011 14:06:53 GMT

Description: SQL DateOnly, StartofWeek, EndOfWeek. Removes time from the Date object.

Link: http://www.codekeep.net/snippets/34ab2dab-db10-482e-a4d0-0252329ce13f.aspx

-- Date Only (Strips time)
SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0) AS DATEONLY

-- First Date in the week
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS START_OF_WEEK 

-- Last Date in the week
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 6) AS END_OF_WEEK

SQL Restore

Mon, 17 Oct 2011 12:30:47 GMT

Description: SQL Restore

Link: http://www.codekeep.net/snippets/42a7e663-f844-45bd-ad62-11fc9e1973aa.aspx

RESTORE DATABASE [MydbName] FROM  DISK = N'D:\Restores\MydbName.bak' WITH  FILE = 1,  MOVE N'MydbName' TO N'D:\Database\MSSQL.1\MSSQL\Data\MydbName.mdf',  MOVE N'MydbName_log' TO N'D:\Database\MSSQL.1\MSSQL\Data\MydbName.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

SQL Email Set up

Mon, 17 Oct 2011 09:09:37 GMT

Description: This enables email on the SQL Server.

Link: http://www.codekeep.net/snippets/5c1f6265-dc74-443b-90ab-3a355a9afe80.aspx

sp_configure 'show advanced options' , 1
GO

RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'Database Mail XPs', 1
GO

RECONFIGURE WITH OVERRIDE;
GO

-- Next expand the Management Node
-- Right click over DATABASE MAIL
-- Select CONFIGURE DATABSE MAIL
-- In last step of Wizard be sure to check PUBLIC and set a DEFAULT to YES.

sqlplus alter session for date

Fri, 14 Oct 2011 18:51:26 GMT

Description: Alter session to set date format in sql plus.

Link: http://www.codekeep.net/snippets/472e9886-aa1b-4f19-861b-22f6a1a6a0eb.aspx

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
select sysdate from dual;