Category: scripts

Table references in stored procedures

We are using SQL 2005 in of our instances,so as part of migration ,we are in need to tables used in stored procs .SQL 2005 doesn’t have dependancies DMV.So wrote quick dirty script ..

—below script gives you table names which are used in sps,you can modify final temp table as per your requirements



if object_id('tempdb..#temp_tablenames') is not null
drop table #temp_tablenames;

create table #temp_tablenames
(
name nvarchar(300)
)
go

insert into #temp_tablenames
select name from sys.tables where is_ms_shipped<>1

if object_id('tempdb..#temp_tblresults') is not null
drop table #temp_tblresults

create table #temp_tblresults
(
spname nvarchar(300),
tblname nvarchar(200)
)


declare @tblname nvarchar(200)
declare @sql nvarchar(max)
set @sql='select  object_name(object_id), @tblname from sys.sql_modules  where definition like '

declare @search nvarchar(200)




declare table_name cursor for 
select name from #temp_tablenames

open table_name

fetch next from table_name
into @tblname


while @@fetch_status=0
begin

set @sql='select  object_name(object_id),'+''''+ @tblname+'''' +' from sys.sql_modules  where definition like '+'''%'+@tblname+'%'''

print @sql

insert into #temp_tblresults
exec (@sql)

fetch next from table_name
into @tblname

end

close table_name
deallocate table_name

--Not used tables 
select 
tbl.name,b.tblname from sys.tables tbl
left join
(select distinct  tblname from #temp_tblresults where  spname not like '%[0-9]%' and  spname not like '%back%' and  spname  not like '%Wi%' and  spname not like '%Back%') b
on b.tblname=tbl.name
order by tbl.name

Advertisements