CodeKeep SQL Feed

Log Parser - Query IIS logs from multiple machines

Wed, 23 May 2012 15: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 13: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 14: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 14: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 14: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 22: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 14: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 09: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 16: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 11: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 18: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 15: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 19: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 17: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 14: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 23: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;

What SQL Statements Are Currently Executing Utility

Mon, 10 Oct 2011 11:03:52 GMT

Description: more info http://www.sqlservercentral.com/articles/dmv/64425/

Link: http://www.codekeep.net/snippets/0a4730e0-19f7-4d09-bb73-7b351851a397.aspx

 -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] = session_Id
	, ecid
	, [Database] = DB_NAME(sp.dbid)
	, [User] = nt_username
	, [Status] = er.status
	, [Wait] = wait_type
	, [Individual Query] = SUBSTRING (qt.text, 
             er.statement_start_offset/2,
	(CASE WHEN er.statement_end_offset = -1
	       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
		ELSE er.statement_end_offset END - 
                                er.statement_start_offset)/2)
	,[Parent Query] = qt.text
	, Program = program_name
	, Hostname
	, nt_domain
	, start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2

COPY DATA FROM TABLE TO TABLE

Wed, 05 Oct 2011 06:31:45 GMT

Description: COPY DATA FROM TABLE TO TABLE

Link: http://www.codekeep.net/snippets/69774736-33e8-4023-ab73-2f80e664158f.aspx

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[COPY_TABLE]    Script Date: 10/17/2011 11:44:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***
OBJET :		STORED PROCEDURE
AUTHOR :		ELAMEEN ELSAYED
DESC :		MOVE TABLE'S ENTIRE DATA TO ANOTHER TABLE
VERSION 2011/10/08 :		@PRINT VARIABLE TO PRINT QUERY,
@BASE VARIABLE TO SPECIFY FROM WHICH TABLE THE LIST OF COLUMNS ARE GOING TO BE ACQUIRED
VERSION 2011/10/16 :		CHECK IF THE TABLE HAS IDENTITY FIRST,
@APPEND VARIABLE TO APPEND ROWS


***/
ALTER PROC [dbo].[COPY_TABLE] (
@SRC_DB NVARCHAR(100),
@SRC_SCHMA NVARCHAR(100),
@SRC_TBL NVARCHAR(100),

@DSTN_DB NVARCHAR(100),
@DSTN_SCHMA NVARCHAR(100),
@DSTN_TBL NVARCHAR(100),
@PRINT BIT=1,
@BASE BIT=0,
@APPEND BIT=0
)
AS
BEGIN
		
		DECLARE @LQS NVARCHAR(MAX), @LQS2 NVARCHAR(MAX),@COLS_TABLE NVARCHAR(MAX),@DB_COLS_TABLE NVARCHAR(MAX)
		IF @BASE=1 
			SELECT @DB_COLS_TABLE=@DSTN_DB,@COLS_TABLE=@DSTN_TBL 
		ELSE
			SELECT @DB_COLS_TABLE=@SRC_DB,@COLS_TABLE=@SRC_TBL 
			
		SELECT @LQS='
		DECLARE @COLS_LST NVARCHAR(MAX),@CMD NVARCHAR(MAX) 
		SELECT @COLS_LST=STUFF((SELECT  '',''+COLUMN_NAME
		FROM   '+@DB_COLS_TABLE+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='''+@COLS_TABLE+'''
		FOR XML PATH('''')),1,1,'''');'+
		CASE WHEN @APPEND=0 THEN  +' DELETE FROM  '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+';' ELSE '' END +
		' SELECT @CMD=''SET IDENTITY_INSERT  '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' ON;INSERT INTO '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' (''+@COLS_LST+'')
		SELECT ''+@COLS_LST+'' FROM '+@SRC_DB+'.'+@SRC_SCHMA+'.'+@SRC_TBL+';SET IDENTITY_INSERT  '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' OFF;'';
		
		EXEC SP_EXECUTESQL @CMD;
		'
		--CHECK IF DOESN'T HAVE IDENTITY
		DECLARE @IDENT_FLG BIT;
		SELECT @LQS2 ='
		USE '+@DSTN_DB+';
		SELECT @IDENT_FLG=COALESCE(OBJECTPROPERTY(OBJECT_ID('''+@DSTN_TBL+''') , ''TableHasIdentity''),0);
		'
		EXEC SP_EXECUTESQL @LQS2,N'@IDENT_FLG BIT OUTPUT',@IDENT_FLG OUTPUT
					
					 
		IF @IDENT_FLG=0
			BEGIN
				--WITHOUT IDENTITY
				SELECT @LQS='
				DECLARE @COLS_LST NVARCHAR(MAX),@CMD NVARCHAR(MAX) 
				SELECT @COLS_LST=STUFF((SELECT  '',''+COLUMN_NAME
				FROM   '+@DB_COLS_TABLE+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='''+@COLS_TABLE+'''
				FOR XML PATH('''')),1,1,'''');'+
				CASE WHEN @APPEND=0 THEN  +' DELETE FROM  '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+';' ELSE '' END +
				'SELECT @CMD=''INSERT INTO '+@DSTN_DB+'.'+@DSTN_SCHMA+'.'+@DSTN_TBL+' (''+@COLS_LST+'')
				SELECT ''+@COLS_LST+'' FROM '+@SRC_DB+'.'+@SRC_SCHMA+'.'+@SRC_TBL+';'';
				
				EXEC SP_EXECUTESQL @CMD;
				'
			END
		IF @PRINT =1 
			PRINT  @LQS
		ELSE
			EXEC SP_EXECUTESQL @LQS
				
END

READ EXCELL

Wed, 05 Oct 2011 05:53:56 GMT

Description: Read Excell file into Sql Server

Link: http://www.codekeep.net/snippets/ab729219-61f0-4c4a-8573-bd69dde9c0f9.aspx

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
--insert into tbAll
SELECT *
FROM
OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Documents and Settings\administrator\Desktop\MRG1.xlsx;Extended Properties=Excel 12.0 XML;HDR=YES',
'SELECT * FROM [Sheet1$]')
--load 2008

DISPLAY HIERARCHY DATA

Tue, 04 Oct 2011 06:42:18 GMT

Description: DISPLAY HIERARCHY DATA IN A RECURSIVE CHILD PARENT COLUMNS

Link: http://www.codekeep.net/snippets/b0b84cdf-6c54-40ba-a496-70be353ceee2.aspx

declare @t table (id int, parent int)
insert @t (id, parent) 
SELECT  EmployeeID,ReportsTo
FROM NORTHWIND..Employees
; with cte as (
    select  id, parent
    ,       cast(RIGHT(REPLICATE(' ',12) + 
                 CONVERT(varchar(12),id),12) as varchar(max)) Path
    from    @t
    where   parent is null
    union all
    select  child.id, child.parent
    ,       parent.Path + RIGHT(REPLICATE(' ',12) + 
                                CONVERT(varchar(12),child.id),12) as Path
    from    @t child
    join    cte parent
    on      parent.id = child.parent
)
select  *
from    cte
order by        Path

ltrim rtrim UDF

Wed, 28 Sep 2011 16:26:17 GMT

Description: trims out given characters from left or right side of a given string

Link: http://www.codekeep.net/snippets/89b7e2d8-4172-47d5-959e-db5950df3760.aspx

create function [dbo].[rtrim]
/*
    Author: Sergey Nefedyev
    Date: 2011-09-27

    UDF Fuction that returns passed in @string value after removing characters in 
    @remove from right side of the string

    Sample Call
        SELECT dbo.rtrim('ABCD987*', '*789A')
    Output
	   ABCD
*/
(
 @string nvarchar(max), 
 @remove nvarchar(100)
)
returns nvarchar(max)
as
begin
    while right(@string, 1) like '[' + @remove + ']'
    begin
        set @string = substring(@string, 1, datalength(@string)/2-1)
    end
    return @string
end
go

create function [dbo].[ltrim]
/*
    Author: Sergey Nefedyev
    Date: 2011-09-27

    UDF Fuction that returns passed in @string value after removing characters in 
    @remove from left side of the string

    Sample Call
        SELECT dbo.ltrim('ABCD987*', 'DCBA*')
    Output
	   987*
*/
(
 @string nvarchar(max), 
 @remove nvarchar(100)
)
returns nvarchar(max)
as
begin
    while left(@string, 1) like '[' + @remove + ']'
    begin
        set @string = substring(@string, 2, datalength(@string)/2-1)
    end
    return @string
end
go

create function [dbo].[trim]
/*
    Author: Sergey Nefedyev
    Date: 2011-09-27

    UDF Fuction that returns passed in @string value after removing characters in 
    @remove from left and right sides of the string

    Sample Call
        SELECT dbo.trim('ABCD987*', 'D9BA7*')
    Output
	   CD98
*/
(
 @string nvarchar(max), 
 @remove nvarchar(100)
)
returns nvarchar(max)
as
begin
    return dbo.ltrim(dbo.rtrim(@string, @remove), @remove)
end
go

Specific word or phrase from all stored procedure, views, and triggers

Thu, 22 Sep 2011 08:24:56 GMT

Description: Find Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

Link: http://www.codekeep.net/snippets/6a59c226-f6b1-477b-b40c-05911456c9f5.aspx

SELECT DISTINCT so.name,sc.text
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.xtype in ('P','TR','V') and sc.TEXT LIKE '%fsu%'
order by name

Kill MSSQL DB connections

Tue, 13 Sep 2011 21:07:31 GMT

Description: This script will kill all current connections to a database in MSSQL. This is useful for detaching the database, restoring it or performing other tasks that require all connections dropped from the database.

Link: http://www.codekeep.net/snippets/78688de6-4801-42ad-abc3-fb7d28e2c3b2.aspx

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('<DATABASE_NAME>')

EXEC(@SQL)

SQL Server UPSERT Example

Thu, 25 Aug 2011 13:09:35 GMT

Description: A merge type statement for SQL Server prior to 2008

Link: http://www.codekeep.net/snippets/14b6d8ca-3796-48a5-8ba2-d67660f56374.aspx

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Stephen Smith
-- Create date: 24 August 2011
-- Description:	Saves or Updates a Saga state
-- =============================================
CREATE PROCEDURE SaveSaga 
	@id uniqueidentifier, 
	@name nvarchar(255),
	@state nvarchar(255),
	@xml text
AS
BEGIN
	BEGIN TRANSACTION Upsert
	
	update Saga with (serializable)
	set ID=@id, Name=@name, [State]=@state, [xml]=@xml, PersistedOn = GETDATE()
	where ID=@id
	
	if @@rowcount = 0
	
	insert into Saga (ID, Name, [State], [Xml], PersistedOn)
	values (@id, @name, @state, @xml, GETDATE()) 

	COMMIT TRANSACTION Upsert
END
GO

Add Trigger to Table Last Update Date

Thu, 25 Aug 2011 06:03:05 GMT

Description: Add a trigger to a table to update a date column with the current UTC Date Time value. This trigger will update all UPDATEs to the table.

Link: http://www.codekeep.net/snippets/dcc2724f-c438-4aee-89d9-799a0d49edc1.aspx

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER trgUpdateMYTABLE ON MYTABLE FOR UPDATE AS
BEGIN
	UPDATE MYTABLE
	SET LastModified = GETUTCDATE()
	FROM MYTABLE INNER JOIN deleted d
	ON Attachments.ID = d.ID
END
GO