Output all figures of the tables' usage

,

This store procedure outputs the tables' usage figures in a usable (I hope) format. If you create in the master database it will show the information for the database that it runs against.

CREATE  PROCEDURE sp_tables_usage AS

CREATE TABLE #tables_usage (
    name varchar(50) NULL, 
    rows varchar(15) NULL, 
    reserved varchar(15) NULL, 
    data varchar(15) NULL, 
    index_size varchar(15) NULL, 
    unused varchar(15) NULL,
)

DECLARE AllUserTables CURSOR
FOR 
SELECT so.name as TName, su.name as UName
FROM sysobjects so join sysusers su 
ON so.uid = su.uid 
WHERE so.type = 'U'

DECLARE @tname varchar(40)
DECLARE @uname varchar(40)

OPEN AllUserTables 

FETCH NEXT FROM AllUserTables 
INTO @tname, @uname

WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        insert into #tables_usage 
        exec('sp_spaceused ''' + 
             @uname + '.' + @tname + '''')
    END
    FETCH NEXT FROM AllUserTables 
    INTO @tname, @uname
END

CLOSE AllUserTables 
DEALLOCATE AllUserTables

SELECT * FROM #tables_usage
GO

Rate

5 (1)

Share

Share

Rate

5 (1)