How to Find Large Tables in AX Database through SQL Server

07 Aug

If your database appears to be larger than you expect it to be, this article will show you how to display a list of your MS SQL Database tables in size order, and list the associate size of the table.

  1. Open and log in to Microsoft SQL Server Management Studio
  2. Copy the following script into the New Query page replacing [DatabaseName] with the name of your database and Click the Execute button
USE [DatabaseName]
CREATE TABLE tempLargeTable(
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
INSERT tempLargeTable
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
COUNT(*) AS col_count,
FROM tempLargeTable a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE tempLargeTable
🙂 Keep Daxing



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: