List of number of Triggers in a database

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'

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

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

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