Retrieve space used info…

Somebody on the net was looking for a way to retrieve info on all user tables in all databases in an instance.  Here’s another 30-minute solution, addressing this:

create table tempdb.dbo.t (
   serverName sysname null
  ,dbName sysname null
  ,schemaName sysname null
  ,name varchar(255)
  ,rows int not null
  ,reserved varchar(255) not null
  ,data varchar(255) not null
  ,index_size varchar(255) not null
  ,unused varchar(255) not null
  ,DateRun datetimeoffset not null default cast(current_timestamp as datetimeoffset)
)
go
exec master.dbo.sp_msforeachdb '
   if db_id(''?'') > 4 begin
      use [?];
      declare @cmd varchar(max), @schema sysname;
      declare c cursor local for
      select sName = schema_name(schema_id)
        ,cmd = ''use [?]; exec sp_spaceused ''''''
        +schema_name(schema_id) + ''.''
        +name + '''''', true'' from sys.objects
         where objectproperty(object_id, ''IsUserTable'') = 1
      open c;
      fetch next from c into @schema, @cmd
      while @@fetch_status = 0 begin
         insert tempdb.dbo.t (name, rows, reserved, data, index_size, unused) exec (@cmd);
         update tempdb.dbo.t set
            schemaName = @schema
           ,serverName = @@servername
           ,dbName = db_name()
            where serverName is null and dbName is null and schemaName is null
         fetch next from c into @schema, @cmd
      end;
      close c;
      deallocate c;
   end
'
select
   FQName = serverName + '.' + dbName + '.' + schemaName + '.' + name
  ,RowsCount = cast(rows as bigint)
  ,ReservedMB = cast(replace(reserved, ' KB', '') as float) / 1024
  ,DataMB = cast(replace(data, ' KB', '') as float) / 1024
  ,IndexSizeMB = cast(replace(index_size, ' KB', '') as float) / 1024
  ,UnusedMB = cast(replace(unused, ' KB', '') as float) / 1024
   from tempdb.dbo.t
go
select
   serverName
  ,dbName
  ,ReservedMB = sum(cast(replace(reserved, ' KB', '') as float) / 1024)
  ,DataMB = sum(cast(replace(data, ' KB', '') as float) / 1024)
  ,IndexSizeMB = sum(cast(replace(index_size, ' KB', '') as float) / 1024)
  ,UnusedMB = sum(cast(replace(unused, ' KB', '') as float) / 1024)
   from tempdb.dbo.t
   group by
      serverName
     ,dbName
go
drop table t
go

Check for existence and wait…

A friend of mine asked me how to implement this type of logic.  The task was to check for an “existence” of an entry in a table, and if the entry does not exist, – wait for specified time interval, then check again, and only then exit.  Of course the description does not include a positive outcome on either initial check for existence, or the final check.  But the solution must have this logic.  Here’s my 30-minute solution that I worked out for him:

create procedure dbo.sp_CheckForExistence (
   @flag int
  ,@val sysname                           )
as
   if exists (select * from sysusers where name = @val)
      return (0)
   else begin
      set @flag = @flag + 1
      return (@flag)
   end
go
create procedure dbo.sp_Parent (
   @val sysname                )
as
   declare @flag int; set @flag = 0
   declare @threshold int; set @threshold = 2
   while @flag >= 0 begin
      exec @flag = dbo.sp_CheckForExistence @flag, @val
      If @flag = 0 break; --This means that it exists
      If @flag = @threshold break; --We waited and re-tried, but existence is not satisfied, so we quit
      Waitfor delay '00:01:00'
   end
   if @flag = @threshold begin --we know that waiting didn’t help
      /* do something here */
      raiserror ('We waited for %d minutes, but nothing happened.', 0, 1, @threshold) with nowait
   end else if @flag = 0 begin -- no waiting was necessary, the record was there
      /* do something else here */
      raiserror ('We didn''t wait, the record was there.', 0, 1) with nowait
   end else if @flag < @threshold begin
      /* do some other things */
      raiserror ('We waited for %d minute(-s), and it was worth it.', 0, 1, @threshold) with nowait
   end
   return (0)
go
-- Then you execute it as:
exec dbo.sp_Parent N'Joe'

Financial Quarter Calculator

I saw a question on dbforums.com asking for how to determine the financial quarter value for a given date.  I think the solution below will give one a good start on that:

if object_id('dbo.fn__MonthWithOffset') is not null
   drop function dbo.fn__MonthWithOffset
go
create function dbo.fn__MonthWithOffset (
   @monthNumber tinyint
  ,@monthOffset tinyint
  ,@operation   char(1) /* + or - */    ) returns tinyint
as begin
      declare @result tinyint
      if @operation not in ('+','-')
         return (null)
      if @monthNumber not between 1 and 12
         return (null)
      if @operation = '+' begin
         if (cast(@monthNumber as int) + cast(@monthOffset as int)) > 12
            set @result = cast((cast(@monthNumber as int) + cast(@monthOffset as int)) % 12 as tinyint)
         else
            set @result = @monthNumber + @monthOffset
      end else if @operation = '-' begin
         if @monthOffset > 12
            set @monthOffset = cast((@monthOffset % 12) as tinyint)
         if (cast(@monthNumber as int) - @monthOffset) <= 0
            set @result = 12 - (@monthOffset - @monthNumber)
         else
            set @result = @monthNumber - @monthOffset
      end
      return (@result)
   end
go

if object_id('dbo.fn__FinancialQuarter') is not null
   drop function dbo.fn__FinancialQuarter
go
create function dbo.fn__FinancialQuarter (
   @date datetime
  ,@firstMonth tinyint                   ) returns tinyint
as begin
      return (
         select
            case datepart(month, @date)
               when dbo.fn__MonthWithOffset(@firstMonth, 0, '+')  then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 01, '+') then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 02, '+') then 1
               when dbo.fn__MonthWithOffset(@firstMonth, 03, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 04, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 05, '+') then 2
               when dbo.fn__MonthWithOffset(@firstMonth, 06, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 07, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 08, '+') then 3
               when dbo.fn__MonthWithOffset(@firstMonth, 09, '+') then 4
               when dbo.fn__MonthWithOffset(@firstMonth, 10, '+') then 4
               when dbo.fn__MonthWithOffset(@firstMonth, 11, '+') then 4
            end
      )
   end
go

select dbo.fn__FinancialQuarter ('11/01/2009', 11)