Technical Article

Table Data Sizes

,

Just connect to the appropriate databases and execute.

--SCRIPT TO LOOP THROUGH TABLES IN A DATABASE AND
--LIST OUT THEIR COUNTS
DECLARE @counter int
        ,@tablename varchar(100)
        ,@execString varchar(8000)



create table #temptable(
tablename varchar(100),
rows int,
reserved varchar(50),
Data varchar(50),
index_size varchar(50),
unused varchar(50))

create table #looptable(
loopint int identity(1,1),
tablename varchar(100))
INSERT INTO #looptable
SELECT name from sys.tables

set @counter = 1

WHILE @counter <= (select max(loopint) from #looptable)
BEGIN
    SET @tablename = (select tablename from #looptable where loopint = @counter)

    SET @execString = '
    INSERT INTO #temptable
    EXEC sp_spaceused '''+@tablename+'''
    
    '
    EXEC(@execString)
    SET @counter = @counter + 1
END


GO
SELECT * from #temptable
ORDER BY reserved desc


GO
DROP TABLE #temptable,#looptable

Rate

1.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

1.75 (4)

You rated this post out of 5. Change rating