Size all tables in volume

by 
DECLARE @name VARCHAR(128)
DECLARE @myList TABLE ([row] int, [id] int,[Name] VARCHAR(128)) ;
DECLARE @myResult TABLE ([row] int, [Name] VARCHAR(128), [Value] VARCHAR(128)) ;

INSERT INTO @myList ([row], [id], [name])
SELECT 
    row_number() OVER(ORDER BY o.name),
    o.id,
    o.name
FROM dbo.sysobjects o join 
sys.all_objects syso on o.id = syso.object_id

where
o.name like 'f_uks_%'
and
type_desc = 'USER_TABLE'

DECLARE @total INT
DECLARE @count INT
DECLARE @strSQL nvarchar(max)

SET @total = (SELECT COUNT(Name) FROM @myList)
SET @count = 1

WHILE @count <= @total
    BEGIN
        SET @name = (   SELECT Name
                        FROM @myList
                        WHERE [row] = @count
                     )

        INSERT @myResult
        EXEC ('SELECT ' + @count +',''' + @name + ''', count(*) FROM ' + @name )

        SET @count += 1         
    END

    SELECT * from @myResult

Leave a Comment