show2005

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

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

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'
      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(15), 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',
             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(15), 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',
             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), 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), 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 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 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 like '%size%'
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 sizes' + @crlf
     print @msg
     end

end        -- procedure show