This is a stored procedure you can run in SQL query of Visual Studio Management Studio. It will return all the tables in AX and tell you how many records, how much size etc. You can use it so see where you need to clean up tables. You can also code to output the first 10 lines of each table so that you can see very fast where data is remaining.
After you have created the sp you can run it with EXEC [dbo].[V4AbBaytGetTableSize] or by doing a right click on the sp.
==
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[V4AbBaytGetTableSize]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[V4AbBaytGetTableSize]
GO
/*
THIS CODE IS TAILOR MADE; DO NOT CHANGE!
DATA EXCHANGE WITH NAVISION BACK OFFICE AND V4A STORE
INITIAL VERSION: 20070218 JO V4A
CHANGE HISTORY:
20070320_01 JO change RFC2
*/
CREATE PROCEDURE [dbo].[V4AbBaytGetTableSize]
(
@TableNames NVARCHAR(255) = NULL
)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TBLSize
(Tblname varchar(255),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @DBname varchar(255)
DECLARE @tablename varchar(255)
SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ’ + @@ServerName + ’ / ’ + @DBName
PRINT ‘’
PRINT ‘By Size Descending’
DECLARE TblName_cursor CURSOR FOR
SELECT [NAME]
FROM sysobjects
WHERE xType = ‘U’
AND
(
@TableNames IS NULL
OR
(@TableNames IS NOT NULL AND [NAME] LIKE ‘’ + @TableNames + ‘’)
)
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize ( Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @tablename
– Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
/*
SELECT CAST(Tblname as Varchar(255)) ‘Table’,
CAST(TblRows as Varchar(14)) ‘Row Count’,
CAST(LEFT(TblReserved, CHARINDEX(’ KB’, TblReserved)) as int) ‘Total Space (KB)’,
CAST(TblData as Varchar(14)) ‘Data Space’,
CAST(TblIndex_Size as Varchar(14)) ‘Index Space’,
CAST(TblUnused as Varchar(14)) ‘Unused Space’
FROM #tblSize
Order by ‘Total Space (KB)’ Desc
*/
PRINT ‘’
PRINT ‘By Table Name Alphabetical’
SELECT CAST(REPLACE(Tblname, N’V4A_DNN_’, N’’) as Varchar(255)) ‘Table’,
CAST(TblRows as INT) ‘Row Count’,
CAST(LEFT(TblReserved, CHARINDEX(’ KB’, TblReserved)) as int) ‘Total Space (KB)’,
CAST(TblData as Varchar(14)) ‘Data Space’,
CAST(TblIndex_Size as Varchar(14)) ‘Index Space’,
CAST(TblUnused as Varchar(14)) ‘Unused Space’
FROM #tblSize
Order by ‘Total Space (KB)’ Desc – Order by ‘Table’
DROP TABLE #TblSize
END
