February 19, 2010 at 5:20 pm
Comments posted to this topic are about the item Unused Objects in the Server
March 9, 2010 at 12:59 pm
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 @cmdVARCHAR(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'
March 9, 2010 at 1:44 pm
I could REALLY use something like this... do you have a version that will run on SQL 2000?
March 9, 2010 at 7:04 pm
Thank you Steve,
The revised script works just fine.....
Cheers
Madhu
March 12, 2010 at 7:14 am
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'
Ryaka
It's not a Waste.
Naps are a way of traveling painlessly through time to the Future
May 19, 2016 at 7:17 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply