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'