Create bar charts, even includes X and Y labels!
create proc Hbar
@cmd varchar(255) , /* TSQL
SELECT statement 1st column
Character, 2nd column numeric */
@barsize
int=100 , /* Maximum size of bar */
@data_label bit=1, /* Label each bar with
value 1=yes */
@x_label varchar(25)=null,
@y_label
varchar(25)=null,
@title varchar(255)=null, /* Title for chart */
@barchar
char(1)=’*’ /* Character to use for bar */
as
SET NOCOUNT ON
—
Get x & y into table #t1
CREATE TABLE #t1 (x varchar(25),y
real)
INSERT INTO #t1 exec (@cmd)
— Format bars
DECLARE
@barsize2 varchar(3) Select
@barsize2=convert(varchar(8),@barsize)
DECLARE @max real,@i
varchar(12)
SELECT @max=max(y) from #t1
SELECT @i =
str(@barsize/@max,6,6)
CREATE TABLE #t2 (x varchar(25),bar varchar(255),y
real)
INSERT INTO #t2 EXEC ("SELECT right(replicate(‘
‘,25)+x,25),
Bar=convert(char("+ @barsize2
+"),
isnull(replicate(‘"+@barchar+"’,convert(int,y*" + @i
+")),”)),y
from #t1")
— Label bars if required
IF
@data_label =1
UPDATE #t2 SET bar=bar+’ (‘+convert(varchar(14),y)+
‘)’
— Print title
DECLARE @line varchar(255) ,@c int
SELECT
@c=((@barsize+50)-datalength(@title))/2
SELECT @line = REPLICATE(‘ ‘,@c) +
@TITLE
PRINT @line
SELECT @line = REPLICATE(‘ ‘,@c) + replicate
(‘=’,datalength(@title))
PRINT @line
PRINT ‘ ‘
SELECT
@line=right(REPLICATE(‘ ‘,25)+@x_label,25)
PRINT @line
PRINT ‘ ‘
—
print chart
DECLARE hb_cursor INSENSITIVE CURSOR
FOR SELECT
x,bar,y
FROM #t2
DECLARE @x varchar(25), @bar varchar(255) ,@y
real
OPEN hb_cursor
WHILE 1=1
BEGIN
FETCH NEXT FROM hb_cursor
INTO @x ,@bar ,@y
IF @@FETCH_STATUS <> 0
BREAK
EXEC (‘ PRINT
"’ + @x + ‘ | ‘ + @bar +’"’)
END
DEALLOCATE hb_cursor
—
Print y axis
DECLARE @Scale varchar(255) , @y_axis
varchar(255)
SELECT @scale = ‘ +’
SELECT @y_axis
= ‘ 0’
DECLARE @ten int SELECT
@ten=@max/(@barsize/10)
DECLARE @ten2 int SELECT @ten2 =
@ten
WHILE @ten2 < @max+@ten
BEGIN
SELECT @scale = @scale +
‘—-+—-+’
SELECT @y_axis = @y_axis + right (‘
‘+convert(varchar(5),@ten2),10)
SELECT @ten2=@ten2 + @ten
END
PRINT
@scale
PRINT @y_axis
PRINT ‘ ‘
SELECT @line = REPLICATE(‘ ‘,@c) +
@y_label
PRINT @line
PRINT ‘ ‘
go
/* TEST DATA */
exec
Hbar ‘select convert(varchar(5),spid),cpu from
master..sysprocesses where
cpu<>0′,
@barsize=100,
@data_label=0,
@title=’Processes by
CPU’,
@barchar=’#’,
@x_label=’SPID’,
@y_label=’CPU’
set nocount
on
declare @cmd varchar(255)
select @cmd="
select o.name,
size=sum(used * 2)
from sysindexes i, sysobjects o ,syscolumns c
where
i.id = o.id
and i.indid in (0, 1, 255)
and c.id = o.id
and o.name not
like ‘sys%’
GROUP BY o.name,o.id "
exec Hbar
@cmd,@barsize=100,@data_label=1,@title=’Tables by Size’
