년,월,일,시,분 단위로 그룹핑하여 보기 각종 통계시 활용

select DATEPART(YEAR, adddt) '년'
,DATEPART(MONTH, adddt) '월'
,DATEPART(DAY, adddt) '일'
,DATEPART(HOUR, adddt) '시'
-- ,(DATEPART(MINUTE, adddt) / 10)
,count(*) FRom 테이블명 with(nolock)
 GROUP BY
DATEPART(YEAR, adddt)
,DATEPART(MONTH, adddt) 
,DATEPART(DAY, adddt)
,DATEPART(HOUR, adddt)
--,(DATEPART(MINUTE, adddt) / 10)
order by 1,2,3,4


select TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
,count(*) FRom 테이블명 with(nolock) 
group by TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
order by 1

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

참고 URL : https://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server