sqlsavior.com
create procedure dbo.show                                -- created by: fred.williams <at> sqlsavior.com
@what varchar(20)       = NULL,
@how varchar(60)        = NULL
as
begin
declare @msg            varchar(1000)
declare @crlf           char(2)
declare @tab            char(1)
declare @max_len        int

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

if @what = 'drives'
       begin

       declare @drive varchar(5)
       declare @cmd varchar(100)

       create table ##dir_listing (output varchar(255))

       create table #drives
       (
       drive    varchar(5) not null,
       MB_free         int        not null
       )
       insert #drives exec master.dbo.xp_fixeddrives

       declare drive_cursor insensitive cursor for
                  select drive from #drives
                  for read only

       open drive_cursor
       fetch drive_cursor into @drive
       while @@fetch_status = 0
                  begin
                  select @cmd = 'insert ##dir_listing exec master.dbo.xp_cmdshell ''dir ' + @drive + ':\'''
                  --print @cmd
                  exec(@cmd)
                  fetch drive_cursor into @drive
                  end

        close drive_cursor
        deallocate drive_cursor
        drop table #drives

        delete ##dir_listing where output like ' Volume Serial Number%' or output = 'output' or output like
'-----------------%'
        update ##dir_listing set output = '' where output is NULL
        select output as 'Root directory listings for fixed drives:' from ##dir_listing

        drop table ##dir_listing

       end
else if @what = 'tables'
      begin

      select @max_len = max(datalength(name)) from sys.objects 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 o.type when 'V' then ' (VIEW)' else '' end as 'table name',
                      convert(varchar(25), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id
              and o.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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id
              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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id
              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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id
              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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id 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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id 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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id 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), s.name) as 'schema name',
                      convert(varchar(26), o.create_date, 109) as 'create datetime'
              from sys.objects o, sys.schemas s
              where o.schema_id = s.schema_id 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',
              convert(varchar(12), recovery_model_desc) as 'recovery model',
              convert(varchar(16), state_desc) as 'status',
              convert(varchar(26), create_date, 109) as 'create_date',
              convert(varchar(3),  compatibility_level) as 'level'
       from sys.databases
       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 'Microsoft SQL Server Managem' then
                                                                       case when program_name like '%Query%' then 'Mgmt Studio
Query'
                                                                            else 'Mgmt Studio' end
                              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(20), rtrim(loginame)) as 'login name',
              convert(char(15), db_name(dbid)) as 'database',
              convert(char(11), substring(convert(varchar, last_batch, 120), 9, 11)) as 'last finish',
              convert(char(11), substring(convert(varchar, login_time, 120), 9, 11)) as 'login time',
              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 'Microsoft SQL Server Managem' then
                                                                       case when program_name like '%Query%' then 'Mgmt Studio
Query'
                                                                            else 'Mgmt Studio' end
                              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(20), rtrim(loginame)) as 'login name',
              convert(char(15), db_name(dbid)) as 'database',
              convert(char(11), substring(convert(varchar, last_batch, 120), 9, 11)) as 'last finish',
              convert(char(11), substring(convert(varchar, login_time, 120), 9, 11)) as 'login time',
              cpu as 'cpu (ms)',
              convert(int, physical_io) as 'I/O R/W',
              waittime as 'wait (ms)',
              convert(char(22),
                      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), s.name) as 'schema name',
              convert(varchar(26), o.create_date, 109) as 'create datetime'
      from sys.objects o, sys.schemas s
      where o.schema_id = s.schema_id and type = 'P' and o.name not like 'dt_%'
      order by o.name
      end
else if @what like 'schema%'
      begin
      select convert(varchar(40), s.name) as 'schema name', count(*) as 'table/view count'
      from  sys.schemas s, sys.objects o
      where s.schema_id = o.schema_id
      and o.type in ('U', 'V')
          group by s.name
      order by s.name
      end
else if @what = 'locks'
      begin
      declare @this_db_id int
      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 = 'sizes'
      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 object_id, convert(varchar(100), name) from sys.objects where type = 'U'
              for read only

      -- dbcc updateusage(0) with no_infomsgs

      open table_cursor
      fetch table_cursor into @id, @objname

      while @@fetch_status = 0
              begin
      
              /*
              **  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 = 'sizes'
else if @what = 'size' and @how is not null
      begin
      
      declare @objname2 varchar(100)                -- The object we want size on.
      declare @id2        int                        -- The object id of @objname.
      declare @type2        character(2)                 -- The object type.
      declare        @pages2        int                        -- Working variable for size calc.

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

          select @objname2 = @how
          select @id2 = OBJECT_ID(@objname2)
      -- dbcc updateusage(0) with no_infomsgs

      if @id2 is not null
              begin
      
              /*
              **  Now calculate the summary data.
              **  reserved: sum(reserved) where indid in (0, 1, 255)
              */
              insert into #spt_space2 (tablename, reserved)
                      select        @objname2,
                              sum(reserved)
                      from        sysindexes
                      where        indid in (0, 1, 255)
                      and        id = @id2
      
              /*
              ** data: sum(dpages) where indid < 2
              **        + sum(used) where indid = 255 (text)
              */
              select @pages2 = sum(dpages)
                              from sysindexes
                                      where indid < 2
                                              and id = @id2
              select @pages2 = @pages2 + isnull(sum(used), 0)
                      from sysindexes
                              where indid = 255
                                      and id = @id2
              update #spt_space2
                      set data = @pages2
                      where tablename = @objname2
      
      
              /* index: sum(used) where indid in (0, 1, 255) - data */
              update #spt_space2
                      set indexp = (select sum(used)
                                      from sysindexes
                                              where indid in (0, 1, 255)
                                                      and id = @id2)
                                  - data
                      where tablename = @objname2
      
              /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
              update #spt_space2
                      set unused = reserved
                                      - (select sum(used)
                                              from sysindexes
                                                      where indid in (0, 1, 255)
                                                              and id = @id2)
                      where tablename = @objname2

              update #spt_space2
                      set rows = i.rows
                              from sysindexes i, #spt_space2
              where i.indid < 2
                                              and i.id = @id2
                      and tablename = @objname2

              end

      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_space2, master.dbo.spt_values d
              where d.number = 1
                      and d.type = 'E'
      order by 'data (Mb)' desc, 'row count' desc

      drop table #spt_space2

      end        -- if @what = 'size' and @how is not null
else
      begin
      select @msg = 'usage:' + @tab + 'show tables' + @crlf
                      + 'or' + @tab + 'show views' + @crlf
                      + 'or' + @tab + 'show databases' + @crlf
                      + 'or' + @tab + 'show schemas' + @crlf
                      + 'or' + @tab + 'show procedures' + @crlf
                      + 'or' + @tab + 'show who' + @crlf
                      + 'or' + @tab + 'show locks' + @crlf
                      + 'or' + @tab + 'show lockcounts' + @crlf
                      + 'or' + @tab + 'show sizes' + @crlf
                      + 'or' + @tab + 'show size, [tablename]' + @crlf
                      + 'or' + @tab + 'show drives' + @crlf
      print @msg
      end

end        -- procedure show
show2005.sql - stored procedure to replace several catalog procedures, for MSSQL versions 2005-2014

usage:        show tables
or        show views
or        show databases
or        show procedures
or        show schemas
or        show who
or        show locks
or        show sizes

Author:              Fred Williams

Directions:        Create this procedure in the model database, and all existing databases. (just highlight and
copy to the clipboard...)