Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unused Objects in the Server


Unused Objects in the Server

Author
Message
Kumar Katakam-462492
Kumar Katakam-462492
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 200
Comments posted to this topic are about the item Unused Objects in the Server
steve812
steve812
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 117
Hi,
Very handy script. It blows up at my site due to microsoft naming

"Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.INDEXES'.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_7b18298b'."

This revised version works fine.

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

Create table #UnwantedTables
(
tblname varchar(256),
Row int,
reserved varchar(32),
data varchar(16),
index_size varchar(16),
Unused varchar(16))
go
create table #tablelist
(dbname varchar(64),
tablename varchar(256),
test varchar(256))
go
declare @cmd VARCHAR(5000)
SELECT @cmd =
'USE [?]
IF ''?'' in (''master'',''model'',''msdb'',''AdventureWorks'') return
IF CHARINDEX(right(upper(''?''),6),''TEMPDB'') > 0 OR
CHARINDEX(right(upper(''?''),6),''SERVER'') > 0 OR
CHARINDEX(left(upper(''?''),10),''SHAREPOINT'') > 0 OR
CHARINDEX(left(upper(''?''),14),''SHAREDSERVICES'') > 0 OR
CHARINDEX(left(upper(''?''),10),''WSS_SEARCH'') > 0
RETURN
insert into #tablelist
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'

EXEC sp_msforeachDB @command1=@cmd

DECLARE Cur_UnWantedTbls CURSOR
READ_ONLY
FOR select test from #tablelist

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'
Uripedes Pants
Uripedes Pants
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 4519
I could REALLY use something like this... do you have a version that will run on SQL 2000?
madhubabu.alasyam
madhubabu.alasyam
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 76
Thank you Steve,

The revised script works just fine.....

Cheers
Madhu
Ryaka
Ryaka
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 177
I would suggest the following changes to handle issues in database naming.



IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#UnwantedTables]'))
drop table #UnwantedTables
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#tablelist]'))
drop table #tablelist
GO
create table #tablelist
(
dbname sysname
,tablename varchar(256)
,test varchar(max)
)

Create table #UnwantedTables
(
tblname varchar(256),
Row int,
reserved varchar(32),
data varchar(16),
index_size varchar(16),
Unused varchar(16)
)


DECLARE @statement nvarchar(max)
SET @Statement = 'USE [?];
SELECT DISTINCT
''[''+DB_NAME()+'']'' AS [Current Database]
,OBJECT_NAME(I.OBJECT_ID) AS OBJECTNAME
,''[''+DB_NAME()+''].dbo.sp_spaceused[''+ss.name+''.''+OBJECT_NAME(I.[object_id])+'']'' as test
From sys.indexes I
inner join sys.objects o
on I.[object_id]=O.[Object_id]
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
AND O.TYPE = ''U'''

insert into #tablelist
exec sp_msforeachdb @statement

DECLARE Cur_UnWantedTbls CURSOR READ_ONLY
FOR select test from #tablelist where dbname not in
('[master]','[msdb]','[tempdb]','[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'




Kindest Regards,

Ryaka


It's not a Waste.
Naps are a way of traveling painlessly through time to the Future
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10048 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search