suggest a query to in sql to find the unused tables

Question

SNAHi

anyone please suggest a query in sql to find the unused tables.

I have a legacy application migrated to .net from coldfusion.But lots of tables are unused now

What is the best way to find all the unused objects from database. (sql 2005)

thanks SNA

Answer 1

In SQL Server, the acutal table data IS the clustered index. Using this query on the Dynamic Management Views (DMV) in SQL Server 2005 and up, you can find unused indices - if you find any clustered index (index_id=1) being unused over an extended period of time, the table is not being used anymore:

DECLARE  @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT   
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
FROM     
    SYS.INDEXES I
JOIN 
    SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE    
    OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
    AND I.INDEX_ID NOT IN 
        (SELECT S.INDEX_ID
     FROM SYS.DM_DB_INDEX_USAGE_STATS S
     WHERE S.OBJECT_ID = I.OBJECT_ID
           AND I.INDEX_ID = S.INDEX_ID
           AND DATABASE_ID = @dbid)
ORDER BY 
    OBJECTNAME,
    I.INDEX_ID,
    INDEXNAME ASC

Another option would be to temporarily rename a table if you suspect it's not being used, and then see if your app(s) still work as expected. If they do for e.g. 30 days or so, then you're pretty sure you don't need that table anymore.

Marc

Answer 2

hi, Here is a query i have written to find the tables not used by any store procedures.. ......................................................................................

Declare @tablename nvarchar(40)

Declare tablecursor cursor for

Select name from sysobjects where xtype = 'U'

DECLARE @sqlCommand nvarchar(1000)

declare @rowCount int

DECLARE @searchstring varchar(50)

DECLARE @ParmDefinition nvarchar(500);

create table #temp

(

UnusedTables nvarchar(40)

)

open tablecursor fetch next from tablecursor into @tablename while @@fetch_status = 0

begin

set @searchstring='p' SET @sqlCommand = N'SELECT @rows = count(o.name) from sysobjects o , syscomments c where o.type='+char(39)+@searchstring + char(39)+' and o.id=c.id and c.text like '+ char(39)+'%' + @tablename +'%'+char(39); SET @ParmDefinition = N'@rows int OUTPUT';

EXECUTE sp_executesql @sqlCommand, @ParmDefinition,@rows=@rowCount OUTPUT;

if @rowCount = 0 begin

insert into #temp values (@tablename)

end

fetch next from tablecursor into @tablename

end

close tablecursor

deallocate tablecursor

select UnusedTables from #temp

drop table #temp

thanks SA