Drop FOREIGN KEY constraint from all table of database

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))

begin

       declare @sql nvarchar(2000)

       SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME

       + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')

       FROM information_schema.table_constraints

       WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

       exec (@sql)

end

Get week start date and week end date of a month in SQL server

#First week of particular month will be start from 1st day of month and last week of month will be end at month end date    

    with C(i) as

              ( select CONVERT(VARCHAR(10),CAST(@monthStart as datetime),111) i

                UNION ALL

                select CONVERT(VARCHAR(10),DATEADD (day,1,i),111) i from C

                     where DATEADD (day,1,i)

                             <DATEADD(month,1,@monthStart)

              ), C1 as

              (

              select DATEPART(WEEK,i)-DATEPART(WEEK,@monthStart)+1 WeekOfMonth,i from C

              )

              select WeekOfMonth,min(i) StartDate, max(i) EndDate from C1 group by WeekOfMonth

Get number of weekends between two dates using sql server

This function will return the total number of weekends in two dates

CREATE FUNCTION fnc_NumberOfWeekEnds(@dFrom DATETIME, @dTo   DATETIME)

RETURNS INT AS

BEGIN

   Declare @weekends int

   Set @weekends = 0

   While @dFrom <= @dTo Begin

     If ((datepart(dw, @dFrom) = 1) OR (datepart(dw, @dFrom) = 7))

                  Set @weekends = @weekends + 1

                  Set @dFrom = DateAdd(d, 1, @dFrom)

   End

   Return (@weekends)

END

Get datetime as per GMT in SQL Server

DECLARE @ToDay datetime

              SET  @ToDay =CAST(( SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30') ) AS SMALLDATETIME)

Check total number of tables, procedures in sql server

Execute the query to get the total count of table and stored procedures.    

  SELECT

    CASE TYPE

        WHEN 'U'

            THEN 'User Defined Tables'

        WHEN 'S'

            THEN 'System Tables'

        WHEN 'IT'

            THEN 'Internal Tables'

        WHEN 'P'

            THEN 'Stored Procedures'

        WHEN 'PC'

            THEN 'CLR Stored Procedures'

        WHEN 'X'

            THEN 'Extended Stored Procedures'

    END,

    COUNT(*)    

FROM SYS.OBJECTS

WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')

GROUP BY TYPE