Finding Unused Objects SQL 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’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: