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