CREATE procedure usp_monitoring_tables
@recipient	varchar( 255 ),
@server_id	varchar( 50 )
as

declare @objname	varchar( 55 )
declare @id		int
declare @rowcount	int
declare @tablesize	int
declare @message	varchar( 2000 )
declare @chr_rowcount	varchar( 20 )
declare @chr_size	varchar( 20 )
declare @chr_CR	varchar( 1 )
declare @sqlstmt	nvarchar( 255 )
declare @dbuse		varchar( 50 )
declare @pagesize	int


set nocount on
set @chr_CR=CHAR( 13 )
select @dbuse=value from settings where settingname='DB'
select @pagesize=low from master.dbo.spt_values where number =  1  and type = 'E'
declare usr_cur_scale cursor for select id, name from sysobjects where type='U'
open usr_cur_scale
fetch next from usr_cur_scale into @id, @objname
while @@fetch_status= 0 
begin
	 /* updateing information about usage space in the sysindexes */ 

	 --set @sqlstmt='dbcc updateusage ('''+@dbuse+''','''+@objname+''')'
 
	 --exec master..sp_executesql @sqlstmt
 

	set @sqlstmt='select @id=id from '+@dbuse+'..sysobjects where type='+''''+'U'+''''+' and name=@objname'
	exec master..sp_executesql @sqlstmt, N'@id int, @objname varchar(55)', @id, @objname
 --	select  from sysobjects where type='U' and name=@objname
 

	select @tablesize=sum(convert(dec( 38 , 0 ),reserved))*@pagesize/ 1024  from sysindexes where id=@id  and indid < 2 
	select @rowcount=sum(rowcnt) from sysindexes where id=@id and indid< 2 
	set @chr_rowcount=convert(varchar( 20 ),@rowcount)
	set @chr_size=convert(varchar( 20 ),@tablesize)
	set @message=isnull(@message,'')+''''+convert(varchar( 25 ),getdate())+''','''+@server_id+''','''+@objname+''','+@chr_rowcount+','+@chr_size+@chr_CR
	fetch next from usr_cur_scale into @id, @objname
end
deallocate usr_cur_scale

 /* sending email */ 
exec master..xp_sendmail
	@recipients=@recipient,
	@message=@message,
	@subject='current_table_size'

GO