Unused Objects in the Server

  • Comments posted to this topic are about the item Unused Objects in the Server

  • 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'

  • I could REALLY use something like this... do you have a version that will run on SQL 2000?

  • Thank you Steve,

    The revised script works just fine.....

    Cheers

    Madhu

  • 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

  • Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply