I need to calculate the total size of individual tables in databases hosted on Sybase ASE system. The release of Sybase ASE system that we are using is 15.7.1.
The goal is to determine the total disk storage used by each table in the databases hosted on the system. Thus, I need to account for the sizes of both the Data and Indexes portion of each table.
And, what I am looking to achieve is a listing of every table in a database, with the following info: - Size of the Data - Size of Indexes - Total Size (Data + Indexes)
I believe that I need multiple queries to produce the above sought after result. But, I am not sure about the accuracy of some of my query logic, and especially that for calculating the sizes of Indexes. And, any advise from the site will be appreciated.
Below are the queries that I have created thus far in effort to achieve the above goal:
--Calculate Size of Data of all Tables --
SELECT obj.[name], stats.[rowcnt], stats.[datarowsize], (stats.[rowcnt] * stats.[datarowsize]) AS 'ObjectSize'
FROM [dbo].[systabstats] stats
INNER JOIN [dbo].[sysobjects] obj ON obj.[id] = stats.[id]
WHERE (obj.[type] = 'U') AND (stats.[indid] = 0)
-- Calculate Size of Clustered Indexes on AllPages-Locked Tables --
SELECT obj.[name], stats.[rowcnt], stats.[datarowsize], (stats.[rowcnt] * stats.[datarowsize]) AS 'ObjectSize'
FROM [dbo].[systabstats] stats
INNER JOIN [dbo].[sysobjects] obj ON obj.[id] = stats.[id]
WHERE (obj.[type] = 'U') AND (stats.[indid] = 1)
-- Calculate Size of Clustered and Nonclustered Indexes on Data-Only-Locked Tables in a Database --
SELECT obj.[name], idx.[name] AS 'IndexName', stats2.[rowcnt] AS 'Object_rowcnt', stats.[leafrowsize], (stats2.[rowcnt] * stats.[leafrowsize]) AS 'ObjectSize'
FROM [dbo].[systabstats] stats
INNER JOIN [dbo].[sysobjects] obj ON obj.[id] = stats.[id]
INNER JOIN [dbo].[sysindexes] idx ON (idx.[id] = stats.[id]) AND (idx.[indid] = stats.[indid])
LEFT OUTER JOIN [dbo].[systabstats] stats2 ON (stats2.[id] = stats.[id]) AND (stats2.[indid] IN (0, 1))
WHERE (obj.[type] = 'U') AND (stats.[indid] > 1)
