SELECT S2.[name] TableName, S1.[name] TriggerName,
CASE
WHEN S1.deltrig > 0 THEN 'Delete'
WHEN S1.instrig > 0 THEN 'Insert'
WHEN S1.updtrig > 0 THEN 'Update'
END 'TriggerType'
FROM sysobjects S1 JOIN sysobjects S2
ON S1.parent_obj = S2.[id]
WHERE S1.xtype='TR'
Blog comprises different TSQL solutons and useful ways to optimize our database and queries.
Monday, October 25, 2010
Function to return all dates consisting of n(th) week of the specific day
-- =============================================
-- Description: function to return date
-- Select * from dbo.GetAllDate(2009,5,5)
-- =============================================
CREATE FUNCTION [dbo].[GetAllDate]
(
@year INT, -- Year of Date
@number SMALLINT, -- (n)th Week of Date
@day SMALLINT -- Day of Date
)
RETURNS @ReturnTbl TABLE
(
Dates DATETIME
)
AS
BEGIN
/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/
IF @number > 5
BEGIN
SET @number = 5
END
DECLARE @start_date DATETIME, @retrunDate DATETIME, @month_counter TINYINT
SET @month_counter =1
WHILE (@month_counter < = 12)
BEGIN
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-01' As SmallDateTime)
IF(MONTH(DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))=@month_counter)
BEGIN
IF(MONTH(DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date)))=@month_counter)
SELECT @retrunDate = DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
ELSE
SELECT @retrunDate = DATEADD(WEEK,@number-2,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@number,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date)))=@month_counter)
SELECT @retrunDate = DATEADD(WEEK,@number,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
ELSE
SELECT @retrunDate = DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
END
INSERT INTO @ReturnTbl(Dates) SELECT (@retrunDate)
SET @month_counter = @month_counter + 1
END
RETURN;
END
-- Description: function to return date
-- Select * from dbo.GetAllDate(2009,5,5)
-- =============================================
CREATE FUNCTION [dbo].[GetAllDate]
(
@year INT, -- Year of Date
@number SMALLINT, -- (n)th Week of Date
@day SMALLINT -- Day of Date
)
RETURNS @ReturnTbl TABLE
(
Dates DATETIME
)
AS
BEGIN
/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/
IF @number > 5
BEGIN
SET @number = 5
END
DECLARE @start_date DATETIME, @retrunDate DATETIME, @month_counter TINYINT
SET @month_counter =1
WHILE (@month_counter < = 12)
BEGIN
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-01' As SmallDateTime)
IF(MONTH(DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))=@month_counter)
BEGIN
IF(MONTH(DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date)))=@month_counter)
SELECT @retrunDate = DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
ELSE
SELECT @retrunDate = DATEADD(WEEK,@number-2,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@number,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date)))=@month_counter)
SELECT @retrunDate = DATEADD(WEEK,@number,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
ELSE
SELECT @retrunDate = DATEADD(WEEK,@number-1,DATEADD(DAY,@day-DATEPART(DW,@start_date),@start_date))
END
INSERT INTO @ReturnTbl(Dates) SELECT (@retrunDate)
SET @month_counter = @month_counter + 1
END
RETURN;
END
Function to return all specific dates in a specific year
-- =============================================
-- Description: Function to return date for all month
-- Select * from dbo.GetDateOfAllMonth(1997,29)
-- =============================================
CREATE FUNCTION [dbo].[GetDateOfAllMonth]
(
@year INT, -- Year of Date
@number SMALLINT -- (n)th Date
)
RETURNS @ReturnTbl TABLE
(
Dates DATETIME
)
AS
BEGIN
DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT, @month_counter TINYINT
SET @month_counter =1
WHILE (@month_counter < = 12)
BEGIN
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-01' As SmallDateTime)
SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))
SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)
SET @counter =1
WHILE @start_date <= @end_date
BEGIN
IF(DATEPART(dd , @start_date )= @number) AND @counter <= @number
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dd , @start_date )
INSERT INTO @ReturnTbl(Dates) values (@start_date)
SET @counter = ISNULL(@counter,0)+1
END
SET @start_date = DATEADD(DAY,1,@start_date)
END
SET @month_counter = @month_counter + 1
END
--select * from @ReturnTbl
RETURN;
END
-- Description: Function to return date for all month
-- Select * from dbo.GetDateOfAllMonth(1997,29)
-- =============================================
CREATE FUNCTION [dbo].[GetDateOfAllMonth]
(
@year INT, -- Year of Date
@number SMALLINT -- (n)th Date
)
RETURNS @ReturnTbl TABLE
(
Dates DATETIME
)
AS
BEGIN
DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT, @month_counter TINYINT
SET @month_counter =1
WHILE (@month_counter < = 12)
BEGIN
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-01' As SmallDateTime)
SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))
SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month_counter AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)
SET @counter =1
WHILE @start_date <= @end_date
BEGIN
IF(DATEPART(dd , @start_date )= @number) AND @counter <= @number
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dd , @start_date )
INSERT INTO @ReturnTbl(Dates) values (@start_date)
SET @counter = ISNULL(@counter,0)+1
END
SET @start_date = DATEADD(DAY,1,@start_date)
END
SET @month_counter = @month_counter + 1
END
--select * from @ReturnTbl
RETURN;
END
Function to create a date from month, year, day, n(th) week of the date.
-- =============================================
-- Description: function to return date
-- Select dbo.findDate(2,2009,1,4)
-- Select dbo.findDate(2,2012,3,7)
-- =============================================
CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS SmallDateTime
AS
BEGIN
/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/
DECLARE @start_date SmallDateTime, @end_date SmallDateTime, @retrunDate SmallDateTime, @total_days TINYINT,@counter TINYINT
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)
SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))
SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)
SET @counter =1
WHILE @start_date <= @end_date
BEGIN
IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )
SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)
END
--SELECT @retrunDate
RETURN @retrunDate
END
-- Description: function to return date
-- Select dbo.findDate(2,2009,1,4)
-- Select dbo.findDate(2,2012,3,7)
-- =============================================
CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS SmallDateTime
AS
BEGIN
/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/
DECLARE @start_date SmallDateTime, @end_date SmallDateTime, @retrunDate SmallDateTime, @total_days TINYINT,@counter TINYINT
SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)
SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))
SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)
SET @counter =1
WHILE @start_date <= @end_date
BEGIN
IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )
SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)
END
--SELECT @retrunDate
RETURN @retrunDate
END
Friday, September 17, 2010
Give permission to create database diagram
EXEC sp_dbcmptlevel [DatabaseName], 90;
go
ALTER AUTHORIZATION ON DATABASE:: [DatabaseName] TO sa
go
use [DatabaseName]
go
EXECUTE AS USER = N'dbo' REVERT
go
ALTER AUTHORIZATION ON DATABASE:: [DatabaseName] TO sa
go
use [DatabaseName]
go
EXECUTE AS USER = N'dbo' REVERT
Thursday, July 29, 2010
Single update query for swaping one column of two different rows of a single table.
CREATE TABLE c_user (id INT, first_name VARCHAR(50))
INSERT INTO c_user VALUES (1,'Neeraj')
INSERT INTO c_user VALUES (2,'Vishal')
SELECT * FROM c_user WHERE id in (1,2)
UPDATE c_user
SET first_name = CASE
WHEN id = 1 THEN fn.f2
WHEN id = 2 THEN fn.f1
END
FROM c_user
OUTER APPLY
(
SELECT cu1.id cu1id, cu1.first_name f1, cu2.id cu2id, cu2.first_name f2
FROM c_user cu1,c_user cu2
WHERE cu1.id = 1 AND cu2.id = 2
) fn
WHERE c_user.id = fn.cu1id OR c_user.id = fn.cu2id
INSERT INTO c_user VALUES (1,'Neeraj')
INSERT INTO c_user VALUES (2,'Vishal')
SELECT * FROM c_user WHERE id in (1,2)
UPDATE c_user
SET first_name = CASE
WHEN id = 1 THEN fn.f2
WHEN id = 2 THEN fn.f1
END
FROM c_user
OUTER APPLY
(
SELECT cu1.id cu1id, cu1.first_name f1, cu2.id cu2id, cu2.first_name f2
FROM c_user cu1,c_user cu2
WHERE cu1.id = 1 AND cu2.id = 2
) fn
WHERE c_user.id = fn.cu1id OR c_user.id = fn.cu2id
Subscribe to:
Posts (Atom)