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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy