TSQL Clear All Database objects

use dbname;

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = ‘ALTER TABLE [‘ + tc2.TABLE_NAME + ‘] DROP [‘ + rc1.CONSTRAINT_NAME + ‘]’
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable ‘DROP TABLE ?’
GO

TSQL Min, Max, AVG and Last Value in single query

declare @MyTable table
(
SN INT
,[Month] INT
,Acc INT
,Bal INT
)
— GO

INSERT INTO @MyTable VALUES
(1, 7, 101, 1000),
(2, 7, 101, 800),
(3, 7, 101, 1700),
(4, 8, 101, 1200),
(5, 8, 101, 900),
(6, 9, 101, 2500)
— GO

select distinct Acc, [Month], MinBal, AvgBal, MaxBal, LastVal from
(
SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM @MyTable
) y
–group by Acc, [Month], MinBal, AvgBal, MaxBal, LastVal

SELECT Acc,[Month],MAX(MinBal)MinBal,MAX(AvgBal)AvgBal,MAX(MaxBal)MaxBal,MAX(lastVal)lastVal
FROM
(
SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM @MyTable
)u GROUP BY Acc,[Month]

 

Sweet one solutions suggested:

select *
from
( SELECT sn, Acc, [Month], Bal
, MIN(Bal) OVER(PARTITION BY Acc, [Month]) MinBal
, AVG(Bal) OVER(PARTITION BY Acc, [Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc, [Month]) MaxBal
, row_number() OVER(PARTITION BY Acc, [Month] ORDER BY SN DESC) as rn
from @MyTable
) tt
where rn = 1
ORDER By sn