Feeds:
Posts
Comments

Archive for February, 2012


Declare @FromDate Date = '2009/01/01',
		@ToDate Date = '2012/02/15'

	;WITH cteDate as
	(
		SELECT 
			DATEADD(QQ,DATEDIFF(QQ,0,@ToDate),0) MaxDate
	)
	, cteDerived as 
	(	SELECT
			1 AS RID, 
			DATEADD(QQ,DATEDIFF(QQ,0,@FromDate),0) MinDate
		UNION ALL 
		SELECT
			RID + 1,
			DATEADD(QQ,1,MinDate)
		FROM
			cteDerived
		WHERE
			DATEADD(QQ,1,MinDate) <= (Select MaxDate From cteDate)
	)
	Select 
		'Q' + CAST(RID as Varchar(3)) as [Quarter], 
		MinDate as FromDate, 
		DATEADD(QQ,1,MinDate)-1 as ToDate 
	FROM 
		cteDerived
	OPTION (MAXRECURSION 0) 

Read Full Post »

Below script is tested for SQL 2005/2008 R2

SELECT  
	Tab.[name] AS TableName,
	Ind.[name] AS IndexName,
          Ind.type_desc,
	SUBSTRING(( SELECT  ', ' + AC.name
				FROM    
					sys.[tables] AS T 
					INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
					INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id]
					INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
				WHERE   
					Ind.[object_id] = I.[object_id]
					AND Ind.index_id = I.index_id
					AND IC.is_included_column = 0
				ORDER BY 
					IC.key_ordinal
				FOR 
				XML PATH('')
				), 2, 8000) AS KeyCols,
	SUBSTRING(( SELECT  ', ' + AC.name
				 FROM    
					sys.[tables] AS T
					INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
					INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id]
					INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
				 WHERE   
					Ind.[object_id] = I.[object_id]
					AND Ind.index_id = I.index_id
					AND IC.is_included_column = 1
				 ORDER BY 
					IC.key_ordinal
				 FOR
				 XML PATH('')
				 ), 2, 8000) AS IncludeCols
 FROM    
	sys.[indexes] Ind
	INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
WHERE 
	Tab.name ='Tablename' --Comment where clause to get all indexes for particular database
ORDER BY 
	TableName

Happy learning..

Read Full Post »

The SET is executing in the EXEC-created scope which does not affect the environment calling EXECUTE.

Try the following:

CREATE TABLE Patient 
	(
		PatientID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
		DemographicInfo VARCHAR(Max)
	);
GO

INSERT INTO Patient Values ('Albert 42 Male'),('David 48 Male'),('Sonia 41 Female')

SELECT * FROM Patient

DELETE FROM Patient WHERE PatientID = 2

INSERT INTO Patient (PatientID, DemographicInfo) values (2, 'NEW Demographic Info'); 

It will not allow you to insert PatientID as it’s Identity_Insert is off and throw below exception

“Cannot insert explicit value for identity column in table ‘Patient’ when IDENTITY_INSERT is set to OFF.”

So you need to run below statement first

EXEC ('SET IDENTITY_INSERT Patient ON');
INSERT INTO Patient (PatientID, DemographicInfo) values (2, 'NEW Demographic Info'); 

But again it will throw same exception because each dynamic query has its own scope. So you need to execute all statements together.

exec ('SET IDENTITY_INSERT Patient ON; INSERT INTO Patient (PatientID, DemographicInfo) values (2, 'NEW Demographic Info');'); 
Go - This will work

Cheers,

Read Full Post »