How can i found which sql types from sql server are not called in my program?

Question

I have a vb.net windows form application which is connected with an SQL Server. I would like to get a list with the name of stored procedures/functions (and tables if is possible) that are not nessecary for my application (because are old probably, eg. "sp_old_1").

I have to note that some stored procedures are called within other stored procedures so i don't want to delete them.

Answer 1

you can get a list of all stored procedures like this

select distinct
       o.name as procedure_name
from   sys.sql_modules m
  inner join sys.objects o ON m.object_id = o.object_id
where o.Type = 'P'  

But there is no way that sql server can know if you use it or not inside your vb application. So your only option is to search with each procedure name in your source code

To find procedures that are called inside other procedures, you can use something like this

select distinct
       o.name AS Object_Name,
       o.type_desc,
       m.definition
from   sys.sql_modules m
  inner join sys.objects o ON m.object_id = o.object_id
where m.definition Like '%your procedure name%'

But I am afraid you will have to call this query for each procedure found in the first query

EDIT

you can get al list of all stored procedures, that are called inside another stored procedure, by looping thru the list of procedures using a cursor. It wont be fast but it can work
It will also list all procedures that are called from other objects, like triggers for example

it will look something like this

declare @t table (procname varchar(200), called_in varchar(200))
declare @ProcName varchar(200)

declare crProcs cursor local FAST_FORWARD READ_ONLY for
  select distinct
         o.name as procedure_name
  from   sys.sql_modules m
    inner join sys.objects o ON m.object_id = o.object_id
  where o.Type = 'P'

open crProcs
fetch next from crProcs into @ProcName
while @@FETCH_STATUS = 0
begin
     insert into @t (procname, called_in)
     select @ProcName,
            o.name
     from   sys.sql_modules m
       inner join sys.objects o ON m.object_id = o.object_id
     where  m.definition Like '%' + @ProcName + '%'
     group by o.name

     fetch next from crProcs into @ProcName
end 
close crProcs
deallocate crProcs

select * 
from   @t t
where  1 < (select count(1) from @t t2 where t2.procname = t.procname )