Menu

How to See the Size of Individual Tables in Your ConnectWise Database

Google+0
LinkedIn3

Have you ever wanted to find out how big certain tables in your ConnectWise database are?

Here is a simple script to show you the top tables in your database, and as well as their true size.

database

This script does several things:

  1. The first part is a handy function that pretties up a lot of the ConnectWise table names. Table names in their raw form in the DB are not as easy to read, so this puts the most common table names into an easy-to-read format. The function is dropped at the end of the script, but feel free to leave it in your DB if you find it useful for other purposes.
  2. The second part of the script uses some of SQL Server’s built-in functions to count the size of a database and collate it for you. First, it will spit out the overall size of your database and then it will show the top 25 tables with their row numbers and sizes.

Script to Reveal Table Size in ConnectWise

USE [cwwebapp_site]
create function [dbo].[CleanCWTableName]
(
@tablename varchar(100)
)
returns varchar(100)
as
begin

declare @ret varchar(100);
set @ret = Replace(@tablename, 'DM_', 'Document-');
set @ret = Replace(@ret, 'AGR_', 'Agreement-');
set @ret = Replace(@ret, 'BL_', 'Billing-');
set @ret = Replace(@ret, 'SO_', 'Opportunity-');
set @ret = Replace(@ret, 'SR_', 'Service-');
set @ret = Replace(@ret, 'PM_', 'Project-');
set @ret = Replace(@ret, 'IV_', 'Inventory-');
set @ret = Replace(@ret, 'SRT_', 'Task-');
set @ret = Replace(@ret, 'EX_', 'Expense-');
set @ret = Replace(@ret, 'SV_', 'Configuration-');
set @ret = Replace(@ret, 'TE_', 'Time Entry-');
set @ret = Replace(@ret, 'WF_', 'Workflow-');
set @ret = Replace(@ret, 'KB_', 'Knoweledge Base-');
set @ret = Replace(@ret, '_', ' ');
return @ret;
end
go

SET NOCOUNT ON
EXEC sp_spaceused;

-- Table row counts and sizes.
DECLARE @tablesize  TABLE
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT @tablesize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT top 25
[dbo].[CleanCWTableName](name) as [Table Function],
name as[Table Name],
cast(rows as int) rows,
cast(replace(reserved,' KB','') as int)/1024 as SizeMB
from @tablesize
order by SizeMB desc

drop  function [dbo].[CleanCWTableName];

FAQ

Hey, I only see X many tickets in my ConnectWise UI. Why does the table have so many more?

There could be several reasons for this discrepancy. Project tickets and service tickets use the same table. Your user could only have access to certain tables in the ConnectWise UI. Or there could be some old tickets in the database that are not visible in the UI.

Are none of these the scripts you’re looking for? Check out all the other ways we can help you manage your ConnectWise database.

Want to do more?

Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact usfor one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.

Related Posts