Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unused Objects in the Server Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 5:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 63, Visits: 181
Comments posted to this topic are about the item Unused Objects in the Server
Post #869520
Posted Tuesday, March 9, 2010 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 20, 2012 12:29 PM
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'
Post #879659
Posted Tuesday, March 9, 2010 1:44 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 7, 2014 11:41 AM
Points: 703, Visits: 4,519
I could REALLY use something like this... do you have a version that will run on SQL 2000?
Post #879708
Posted Tuesday, March 9, 2010 7:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:03 PM
Points: 4, Visits: 49
Thank you Steve,

The revised script works just fine.....

Cheers
Madhu
Post #879860
Posted Friday, March 12, 2010 7:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:48 AM
Points: 65, Visits: 157
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
Post #881754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse