Calculate no days in a particular month

DECLARE @today_date DATETIME
SET @today_date = '2009/02/01'

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@today_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@today_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

Use of CTE along with PIVOTING

;with cte
as
(
select cb.name as Branch, cee.entity_fk as entity, cee.id, cee.emp_code, cee.first_name +' '+ cee.last_name as Employee,
es.salary_component_fk as component, es.amount
from employee cee
inner join entity ee on ee.id = cee.entity_fk
inner join branch cb on cb.id = ee.branch_fk
inner join salary es on es.employee_fk= cee.id
where --cee.entity_fk = 3 and
cee.emp_code is not null
and cee.dol is null
and cee.active = 1
and es.active = 1
and es.amount > 0
)
select Branch,emp_code, Employee, Isnull([1],0) as [Basic],Isnull([2],0) as HRA,Isnull([3],0 ) as [TPL All],Isnull([4],0) as [SPL All],
(Isnull([1],0) + Isnull([2],0) + Isnull([3],0) + Isnull([4],0)) as Total

from (select Branch, entity, id, emp_code,Employee, component, amount from cte) AS vs
PIVOT (sum(amount) FOR component in([1], [2], [3], [4])) AS p

order by Branch, entity, CAST(SUBSTRING(emp_code,5,len(emp_code)) AS INT)

This is the way to insert OPENXML data into table without using view

INSERT into TableName
SELECT *
FROM OPENXML(@hdoc, @nodeName)
WITH
(
id INT '@id',
component_fk int '@component_fk',
amount float '@amount',
active bit '@active'
)

XML inSQL Query

We can use New way of selecting Nodes from XMl by using following:
Declare @xmlData xml
SET @xmlData='';
Declare @temp as table (id int, Name varchar(50))
Insert into @temp
SELECT
T.item.value('@id', 'int') as [id],
T.item.value('@first_name', 'varchar(50)') as [Name]
FROM @xmlData.nodes('/employee/user') AS T(item)
where T.item.value('@id', 'int')=57
So there is no need of using OPENXML() method.

OpenXML and xml Nodes

DECLARE @xmlData XMLIF (@xmldata.exist('//sd/member')=1)BEGIN
DECLARE @hdoc int Declare @nodeName as varchar(100)
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldata Set @nodeName = '//sd/member'-- Declare a temp table and insert from open xml having auto increment ID column-- This ID column is used to avoid cursor--INSERT into @tempSponsorNew SELECT * FROM OPENXML( @hdoc, @nodeName, 8 ) WITH ( id INT '@id' ,[sp_fk] INT '@sponsor_fk' ,[name] varchar(100) '@name')
EXEC sp_xml_removedocument @hDoc
DECLARE @sponserCounter INT
DECLARE @totalRowSponser INT -- avoid using cursorSET @sponserCounter=0 SELECT @totalRowSponser=count(1) FROM @tempSponsorNew While(@sponserCounter < @totalRowSponser) BEGIN SET @sponserCounter=@sponserCounter + 1 -- Do what ever you want to do with this temp table dataselect *FROM @tempSponsorNew ts WHERE ts.id < 0 and ts.active=1 and ts.[primary_id]=@sponserCounter END
END

rebuild all index of all database

DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INTSET @fillfactor = 90DECLARE DatabaseCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN -- SQL 2000 command --DBCC DBREINDEX(@Table,' ',@fillfactor) -- SQL 2005 command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor

Find a table column on SQL Server

For specific column name:

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )


For a part of column name:

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%PART_OF_NAME%' )