Unused Objects in the Server

,

This Script is used to List out the objects lying in the box which were un used from the day of the sql server recycled. This will be useful to reclaim the unnecessary usage of space in the server and by this we can say the backup will consume less space.

Please copy the script into the sql box. Reveiw for a while and then execute and test it on any of the development box. Once you are confident enough please use on production box.

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#UnwantedTables]'))
drop table #UnwantedTables

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#tablelist]'))
drop table #tablelist

create table #tablelist
(
    dbname varchar(64)
    ,tablename varchar(256)
    , test varchar(256))

Create table #UnwantedTables
(

tblname varchar(256),
Row int,
reserved varchar(32),
data varchar(16),
index_size varchar(16),
Unused varchar(16))


insert into #tablelist
exec sp_msforeachdb @command1='USE ?;SELECT DISTINCT 
            dbname = db_name() ,
            OBJECTNAME = OBJECT_NAME(I.OBJECT_ID)
            ,db_name() + ''.dbo.sp_spaceused [''+ ss.name + ''.'' + OBJECT_NAME(I.OBJECT_ID) + '']'' as test
        FROM 
            SYS.INDEXES AS I
            INNER JOIN SYS.OBJECTS AS O
                ON I.OBJECT_ID = O.OBJECT_ID AND O.TYPE = ''U''
            inner join sys.schemas as ss on ss.schema_id = o.schema_id 
            LEFT JOIN (select distinct object_id from SYS.DM_DB_INDEX_USAGE_STATS) AS S 
                ON S.OBJECT_ID = I.OBJECT_ID
        WHERE 
        S.OBJECT_ID IS NULL'

DECLARE Cur_UnWantedTbls CURSOR
    READ_ONLY
    FOR select test from #tablelist where (dbname not like 'MASTER'
     and dbname NOT LIKE 'MSDB' and dbname NOT LIKE 'TEMPDB' and dbname NOT LIKE 'MODEL')

DECLARE @objectName varchar(512)

    OPEN Cur_UnWantedTbls

    FETCH NEXT FROM Cur_UnWantedTbls INTO @objectName

    WHILE (@@fetch_status <> -1)

    BEGIN

     IF (@@fetch_status <> -2)

        BEGIN
            insert into #UnwantedTables 
            exec ( @objectName)
        END
     FETCH NEXT FROM Cur_UnWantedTbls INTO @objectName
    END
    CLOSE Cur_UnWantedTbls
    DEALLOCATE Cur_UnWantedTbls

select distinct @@servername, DBNAME, TABLENAME, LEFT(RESERVED, LEN(RESERVED)-2) ReservedInKB, 
(select create_Date from sys.databases where name = 'tempdb') RecycledTime
from #tablelist inner join #UnwantedTables on tblname = tablename

where reserved <> '0 KB'

Rate

Share

Share

Rate