30 Ocak 2012 Pazartesi

size and row count of top 100 tables

create table #t(d nvarchar(MAX),t nvarchar(MAX),r int,x nvarchar(100),s nvarchar(100),y nvarchar(100),z nvarchar(100))

declare @s nvarchar(MAX)

set @s=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!) exec [~].dbo.sp_msforeachtable "insert into #t(t, r,x,s,y,z) exec [~].dbo.sp_spaceused !?!"','!',char(39))

EXEC sp_MSForEachDB @command1=@s, @command2="update #t set d='~' where d is null", @replacechar='~'

select top(100) d as base, t as [table], s as size, r as rows from #t order by Cast(LEFT(s,len(s)-3) as int) desc

drop table #t

3 Ocak 2012 Salı

date range with recursive cte

WITH

DATE_RANGE as (

      SELECT

            [date]=GETDATE()

       UNION ALL

       SELECT DATEADD(day,1,[date])

       FROM DATE_RANGE

      WHERE DATEADD(day,1,[date])<=DATEADD(day,10,GETDATE())

)

SELECT * FROM DATE_RANGE

 

image