thanks Gail..
I will do it and let you know, mean while I try to write a code as below to get the usage of tables.
--Query to Get Dependency of each table in the database.
Use Database
go
set nocount on
--select * from sys.dm_db_index_usage_stats
declare @TableName varchar(200), @i int , @C int
create table #TableUsage (TableName varchar(200), ProcedureName varchar(200))
select distinct identity(int,1,1) id, table_name into #Tables
from INFORMATION_SCHEMA.tables
select @I=min(id), @C=max(id) from #Tables
while @i<=@c
begin
select @TableName=table_name from #Tables where id=@i
insert into #TableUsage(TableName,ProcedureName)
select @TableName TableName,routine_name
from INFORMATION_SCHEMA.routines
where routine_type='PROCEDURE'
and substring(routine_definition,charindex(@TableName,routine_definition),len(@TableName))=@TableName
order by routine_name
print @i print @tablename
set @i+=1
end
go
select *
from #TableUsage
go
if object_id('tempdb..#TableUsage') is not null drop table #TableUsage
if object_id('tempdb..#Tables') is not null drop table #Tables
go
set nocount off
Thanks,
Prabhu