Today I got task to determine the table size which table taking how much space on disk.
So I have write the following script to determine that I hope It will help you out somewhere.
Just checkout
BEGIN TRY
DECLARE @tblName AS VARCHAR(200)
DECLARE @tblTest TABLE ([Name] VARCHAR(100),
[rows ] INT,
reserved VARCHAR(100),
data VARCHAR(100),
index_Size VARCHAR(100),
Unused VARCHAR(100))
DECLARE cur_Test Cursor FOR SELECT [name] FROM sys.objects WHERE [type] = ‘U’
OPEN cur_Test
FETCH NEXT FROM cur_test INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tblTest
EXECUTE sp_spaceUsed @tblName
FETCH NEXT FROM cur_test INTO @tblName
END
CLOSE cur_Test
DEALLOCATE cur_Test
SELECT * FROM @tblTest ORDER BY rows desc
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH
Enjoy Programming
Thanks
Rajat Jaiswal
The Suburbs
