show

create procedure show
@what varchar(20)       = NULL,
@how varchar(20)        = NULL
as
begin
declare @msg            varchar(1000)
declare @crlf           char(2)
declare @tab            char(1)
declare @max_len        int
declare @this_db_id     int

set nocount on
select @crlf = char(13) + char(10)
select @tab = char(9)

if @what = 'tables'
  begin

  select @max_len = max(datalength(name)) from sysobjects where type in ('U', 'V')

  /* attempt to reduce length of column (datalength() reports size*2 due to unicode) */

  if @max_len < 40
          select convert(varchar(20), o.name) +
                  case type when 'V' then ' (VIEW)' else '' end as 'table name',
                  convert(varchar(25), u.name) as 'table owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type in ('U', 'V')
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name
  else if @max_len < 60
          select convert(varchar(30), o.name) +
                  case type when 'V' then ' (VIEW)' else '' end as 'table name',
                  convert(varchar(25), u.name) as 'table owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type in ('U', 'V')
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name
  else if @max_len < 80
          select convert(varchar(40), o.name) +
                  case type when 'V' then ' (VIEW)' else '' end as 'table name',
                  convert(varchar(25), u.name) as 'table owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type in ('U', 'V')
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name
  else
          select convert(varchar(60), o.name) +
                  case type when 'V' then ' (VIEW)' else '' end as 'table name',
                  convert(varchar(25), u.name) as 'table owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type in ('U', 'V')
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name

  end        -- tables
else if @what = 'views'
  begin
  select @max_len = max(datalength(name)) from sysobjects where type = 'V'

  /* attempt to reduce length of column (datalength() reports size*2 due to unicode) */

  if @max_len < 40
          select convert(varchar(20), o.name) as 'view name',
                  convert(varchar(25), u.name) as 'view owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type = 'V'
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name
  else if @max_len < 60
          select convert(varchar(30), o.name) as 'view name',
                  convert(varchar(25), u.name) as 'view owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type = 'V'
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name
  else if @max_len < 80
          select convert(varchar(40), o.name) as 'view name',
                  convert(varchar(25), u.name) as 'view owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type = 'V'
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name
  else
          select convert(varchar(60), o.name) as 'view name',
                  convert(varchar(25), u.name) as 'view owner',
                  convert(varchar(26), o.crdate, 109) as 'create datetime'
          from sysobjects o, sysusers u
          where o.uid = u.uid and type = 'V'
          and o.name not in ('syssegments', 'sysconstraints')
          order by o.name

  end        -- views
else if @what = 'databases' or @what = 'db'
  begin
  select        convert(varchar(40), name) as 'database name',
          dbid,
          convert(varchar(20), crdate, 120) as 'create_date',
          convert(varchar(11), databasepropertyex(name, 'recovery')) as 'recovery',
          convert(varchar(10), databasepropertyex(name, 'status')) as 'status'
   from master.dbo.sysdatabases
   order by name
  end

else if @what = 'whoall' or @what = 'processes' or @what = 'connections'
  begin
  select        convert(char(6), convert(varchar, spid) + case spid when @@spid then '*' else '' end) as '[spid]',
          blocked as 'blocker',
          waittime as 'wait (ms)',
          convert(char(12), hostname) as 'from client',
          case cmd
                  when 'AWAITING COMMAND' then ''
                  else lower(cmd)
                  end as 'current command',
          convert(char(18),
                  case convert(char(28), program_name)
                          when 'SQL Query Analyzer' then 'Query Analyzer'
                          when 'MS SQLEM' then 'Enterprise Mgr'
                          when 'MS SQLEM - Data Tools' then 'Enterprise Mgr'
                          when 'SQLAgent - Generic Refresher' then 'Agent-Refresh'
                          when 'SQLAgent - Alert Engine' then 'Agent-Alert'
                          when 'SQLAgent - TSQL JobStep (Job' then 'Agent-SQLstep'
                          when 'Microsoft SQL Server' then 'SQLSVR-' + convert(char(8), hostname)
                          when 'Internet Information Service' then 'IIS Web Server'
                          else program_name
                          end ) as 'program',
          convert(char(15), rtrim(loginame)) as 'login name',
          convert(char(15), db_name(dbid)) as 'database',
          convert(char(11), substring(convert(varchar, login_time, 120), 9, 11)) as 'login time',
          convert(char(11), substring(convert(varchar, last_batch, 120), 9, 11)) as 'last finish',
          cpu as 'cpu (ms)',
          convert(int, physical_io) as 'I/O R/W',
          waittime as 'wait (ms)',
          convert(char(15),
                  case waittime
                          when 0 then ''
                          else lastwaittype
                          end ) as 'wait type',
          convert(char(18),
                  case waittime
                          when 0 then ''
                          else waitresource
                          end ) as 'wait resource',
          open_tran as 'trans',
          convert(char(10), status) as 'status',
          convert(char(6), convert(varchar, spid) + case spid when @@spid then '*' else '' end) as '[spid]'
  from master.dbo.sysprocesses
  where spid >= 0 and spid <= 32767
  order by spid desc
  end

else if @what = 'who'        -- show only user processes, (plus any blocked or blocking system processes)
  begin
  select        convert(char(6), convert(varchar, spid) + case spid when @@spid then '*' else '' end) as '[spid]',
          blocked as 'blocker',
          waittime as 'wait (ms)',
          convert(char(12), hostname) as 'from client',
          case cmd
                  when 'AWAITING COMMAND' then ''
                  else lower(cmd)
                  end as 'current command',
          convert(char(18),
                  case convert(char(28), program_name)
                          when 'SQL Query Analyzer' then 'Query Analyzer'
                          when 'MS SQLEM' then 'Enterprise Mgr'
                          when 'MS SQLEM - Data Tools' then 'Enterprise Mgr'
                          when 'SQLAgent - Generic Refresher' then 'Agent-Refresh'
                          when 'SQLAgent - Alert Engine' then 'Agent-Alert'
                          when 'SQLAgent - TSQL JobStep (Job' then 'Agent-SQLstep'
                          when 'Microsoft SQL Server' then 'SQLSVR-' + convert(char(8), hostname)
                          when 'Internet Information Service' then 'IIS Web Server'
                          else program_name
                          end ) as 'program',
          convert(char(15), rtrim(loginame)) as 'login name',
          convert(char(15), db_name(dbid)) as 'database',
          convert(char(11), substring(convert(varchar, login_time, 120), 9, 11)) as 'login time',
          convert(char(11), substring(convert(varchar, last_batch, 120), 9, 11)) as 'last finish',
          cpu as 'cpu (ms)',
          convert(int, physical_io) as 'I/O R/W',
          waittime as 'wait (ms)',
          convert(char(15),
                  case waittime
                          when 0 then ''
                          else case cpu when 0 then '--' else lastwaittype end
                          end ) as 'wait type',
          convert(char(18),
                  case waittime
                          when 0 then ''
                          else waitresource
                          end ) as 'wait resource',
          open_tran as 'trans',
          convert(char(10), status) as 'status',
          convert(char(6), convert(varchar, spid) + case spid when @@spid then '*' else '' end) as '[spid]'
  from master.dbo.sysprocesses
  where        (spid >= 50 and spid <= 32767) or
          (blocked <> 0) or
          (spid in (select blocked from master..sysprocesses))
  order by spid desc
  end
else if @what = 'procedures'
  begin
  select convert(varchar(60), o.name) as 'procedure name',
          convert(varchar(25), u.name) as 'procedure owner',
          convert(varchar(26), o.crdate, 109) as 'create datetime'
  from sysobjects o, sysusers u
  where o.uid = u.uid and type = 'P' and o.name not like 'dt_%'
  order by o.name
  end
else if @what = 'locks'
  begin

  select @this_db_id = db_id()
  select         convert (smallint, req_spid) As spid,
          convert(varchar(25), db_name(rsc_dbid)) As 'Database',
          case rsc_dbid
           when @this_db_id then convert(varchar(30), object_name(rsc_objid))
           else convert(varchar(30), rsc_objid)
           end as 'Object',
          rsc_indid As 'Index Id',
          substring (v.name, 1, 4) As 'Type',
          substring (rsc_text, 1, 16) as 'Resource',
          substring (u.name, 1, 8) As 'Mode',
          substring (x.name, 1, 5) As 'Status'
  from         master.dbo.syslockinfo,
          master.dbo.spt_values v,
          master.dbo.spt_values x,
          master.dbo.spt_values u
  where   master.dbo.syslockinfo.rsc_type = v.number
   and        v.type = 'LR'
   and        master.dbo.syslockinfo.req_status = x.number
   and        x.type = 'LS'
   and        master.dbo.syslockinfo.req_mode + 1 = u.number
   and        u.type = 'L'
   and   (rsc_objid <> 0 or rsc_indid <> 0 or substring (u.name, 1, 8) <> 'S')
          -- above filters some noise, like shared database locks, from output
  order by spid

  end
else if @what = 'lockcounts'
  begin

  select @this_db_id = db_id()
  select convert (smallint, req_spid) As spid,
          convert(varchar(25), db_name(rsc_dbid)) As 'Database',
          case rsc_dbid
           when @this_db_id then convert(varchar(30), object_name(rsc_objid))
           else convert(varchar(30), rsc_objid)
           end as 'Object',
          rsc_indid As 'Index Id',
          substring (v.name, 1, 4) As 'Type',
          --substring (rsc_text, 1, 16) as 'Resource',
          substring (u.name, 1, 8) As 'Mode',
          case when substring (v.name, 1, 4) = 'TAB' and substring (u.name, 1, 8) = 'X' then '!!!!'
               else '' end as 'Bad',
          substring (x.name, 1, 5) As 'Status',
          count(*) as 'Count'
  from         master.dbo.syslockinfo,
          master.dbo.spt_values v,
          master.dbo.spt_values x,
          master.dbo.spt_values u
  where   master.dbo.syslockinfo.rsc_type = v.number
   and        v.type = 'LR'
   and        master.dbo.syslockinfo.req_status = x.number
   and        x.type = 'LS'
   and        master.dbo.syslockinfo.req_mode + 1 = u.number
   and        u.type = 'L'
   and   (rsc_objid <> 0 or rsc_indid <> 0 or substring (u.name, 1, 8) <> 'S')
   and         substring(u.name, 1, 8) <> 'IS'
          -- above filters some noise, like shared database locks, from output
  group by convert (smallint, req_spid),
          convert(varchar(25), db_name(rsc_dbid)),
          case rsc_dbid
           when @this_db_id then convert(varchar(30), object_name(rsc_objid))
           else convert(varchar(30), rsc_objid)
           end,
          rsc_indid,
          substring (v.name, 1, 4),
          --substring (rsc_text, 1, 16),
          substring (u.name, 1, 8),
          case when substring (v.name, 1, 4) = 'TAB' and substring (u.name, 1, 8) = 'X' then '!!!'
               else '' end,
          substring (x.name, 1, 5)
  order by spid
  end
else if @what like '%contention%'
  begin

  select @this_db_id = db_id()
  select convert (smallint, a.req_spid) As spid,
          convert(varchar(25), db_name(a.rsc_dbid)) As 'Database',
          case a.rsc_dbid
           when @this_db_id then convert(varchar(30), object_name(a.rsc_objid))
           else convert(varchar(30), a.rsc_objid)
           end as 'Object',
          a.rsc_indid As 'Index Id',
          substring (v.name, 1, 4) As 'Type',
          '[' + substring (a.rsc_text, 1, 16) + ']' as 'Resource',
          substring (u.name, 1, 8) As 'Mode',
          substring (x.name, 1, 5) As 'Status'
  from    master.dbo.syslockinfo a, master.dbo.syslockinfo b,
          master.dbo.spt_values v,
          master.dbo.spt_values x,
          master.dbo.spt_values u
  where           a.rsc_type = v.number
   and       v.type = 'LR'
   and       a.req_status = x.number
   and       x.type = 'LS'
   and       a.req_mode + 1 = u.number
   and       u.type = 'L'
   and        (a.rsc_objid <> 0 or a.rsc_indid <> 0 or substring (u.name, 1, 8) <> 'S')
             -- above filters some noise, like shared database locks, from output
   and        substring (a.rsc_text, 1, 16) = substring (b.rsc_text, 1, 16)
   and        ltrim(rtrim(substring (a.rsc_text, 1, 16))) <> ''
   and        a.req_spid <> b.req_spid
   and        a.req_status <> b.req_status
  order by substring (a.rsc_text, 1, 16), spid
  end
else if @what like '%size%'
  begin

  declare @objname   varchar(100)               -- The object we want size on.
  declare @id        int                        -- The object id of @objname.
  declare @type      character(2)               -- The object type.
  declare @pages     int                        -- Working variable for size calc.

create table #spt_space
  (
          tablename       varchar(100) not null,
          rows            int null,
          reserved        dec(15) null,
          data            dec(15) null,
          indexp          dec(15) null,
          unused          dec(15) null
  )

  declare table_cursor insensitive cursor for
          select id, convert(varchar(25), u.name) + '.' + convert(varchar(100), o.name)
           from sysobjects o, sysusers u
           where o.uid = u.uid
           and o.type = 'U'
          for read only

  -- Now check out each constraint, figure out its type and keys and
  -- save the info in a temporary table that we'll print out at the end.
  open table_cursor
  fetch table_cursor into @id, @objname

  while @@fetch_status = 0
          begin

          dbcc updateusage(0, @objname) with no_infomsgs


          /*
          ** Now calculate the summary data.
          ** reserved: sum(reserved) where indid in (0, 1, 255)
          */
          insert into #spt_space (tablename, reserved)
                  select        @objname,
                          sum(reserved)
                  from        sysindexes
                  where        indid in (0, 1, 255)
                  and        id = @id

          /*
          ** data: sum(dpages) where indid < 2
          **        + sum(used) where indid = 255 (text)
          */
          select @pages = sum(dpages)
                          from sysindexes
                                  where indid < 2
                                          and id = @id
          select @pages = @pages + isnull(sum(used), 0)
                  from sysindexes
                          where indid = 255
                                  and id = @id
          update #spt_space
                  set data = @pages
                  where tablename = @objname


          /* index: sum(used) where indid in (0, 1, 255) - data */
          update #spt_space
                  set indexp = (select sum(used)
                                  from sysindexes
                                          where indid in (0, 1, 255)
                                                  and id = @id)
                              - data
                  where tablename = @objname

          /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
          update #spt_space
                  set unused = reserved
                                  - (select sum(used)
                                          from sysindexes
                                                  where indid in (0, 1, 255)
                                                          and id = @id)
                  where tablename = @objname

          update #spt_space
                  set rows = i.rows
                          from sysindexes i, #spt_space
                                  where i.indid < 2
                                          and i.id = @id
                  and tablename = @objname

          fetch table_cursor into @id, @objname
          end

  close table_cursor
  deallocate table_cursor

  select convert(varchar(40), tablename) as 'table name',
          convert(int, str((data * d.low) / 1048576., 15, 0)) as 'data (Mb)',
          rows as 'row count',
          convert(int, str((indexp * d.low) / 1048576., 15, 0)) as 'indexes (Mb)',
          convert(int, str((reserved * d.low) / 1048576., 15, 0)) as 'reserved (Mb)',
          convert(int, str((unused * d.low) / 1048576., 15, 0)) as 'unused (Mb)'
  from #spt_space, master.dbo.spt_values d
          where d.number = 1
                  and d.type = 'E'
  order by 'data (Mb)' desc, 'row count' desc

  drop table #spt_space

end        -- if @what like '%size%'
else
  begin
  select @msg = 'usage:' + @tab + 'show tables' + @crlf
                  + 'or' + @tab + 'show views' + @crlf
                  + 'or' + @tab + 'show databases' + @crlf
                  + 'or' + @tab + 'show procedures' + @crlf
                  + 'or' + @tab + 'show who' + @crlf
                  + 'or' + @tab + 'show locks' + @crlf
                  + 'or' + @tab + 'show lockcounts' +@crlf
                  + 'or' + @tab + 'show contention' +@crlf
                  + 'or' + @tab + 'show sizes' + @crlf
  print @msg
  end

end        -- procedure show
go

grant execute on show to public
go