You, Me and Dynamics GP
EXEC sp_spaceused @updateusage = true
This will return the space used by whatever DB you run it against. The @updateusage=true updates statistics first to ensure that the sizes are correct.If you want the space used by a particular table, run the stored proc as:
EXEC sp_spaceused 'PM00200', @updateusage = true
substituting your own table for PM00200.If you want to see the usage for EVERY table, you can use the undocumented MSforeachtable stored procedure and do it all in one line.
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?', @updateusage = true "
Unfortunately, GP has too many tables to run this command directly and you probably don't want all the tables anyway. Most likely you want to see the sizes of a few tables that are important to you or you want to see the whole thing. To get just a few tables and give them friendly names, I've put together some SQL Code here that I'll also make available in the download box on the right.This code creates a temp table, plugs all the sizes in the temp table and let you add friendly names via an included CASE statement. You limit the tables via the where clause. If you don't add a friendly name, don't worry, the code will pull the table name from GP for any tables you stick in the where clause.
The temp table and display are two different parts so you could separate them and run the temp table portion once a day and this will speed up the display for the user. This would make creating an SSRS or Crystal Report from this data much easier to build. The output looks like this:
There's no reason why you couldn't combine this with the table name data already available on DynamicAccounting.net to add friendly names to everything. Well, no reason except that it's close to Thanksgiving and I'm going to chose football over writing SQL code!
