RSS

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]
GO
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))
SET NOCOUNT ON
INSERT tempLargeTable
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
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
4.tempLargeTable
🙂 Keep Daxing

 

Advertisements
 

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: