Tool to search for servers, stored procedures, funtions, triggers, etc..

• Manolo Munar

  SSC Journeyman

  Points: 90

  More actions

  November 8, 2006 at 9:43 am

  #173365

  We are migrating our databases to new servers and we have hundreds of stored procedures, some functions, triggers, and views. Most of our servers are linked so a stored procedure can access more than one server. Is there a tool out there that can list all the servers a stored procedure is accessing? Or search a server and list all the stored procedures that are accessing the server including sprocs from the other servers? Thanks.

  Manolo  

• Venu Dukka

  Old Hand

  Points: 351

  More actions

  November 8, 2006 at 7:22 pm

  #670745

  I am not aware of any tools which can accomplish this. I can give you a suggestion though. Query the syscomments table (view in SQL 2005) like this:

  select so.[name] ObjectName, so.xtype ObjectType, substring(sc.[text], 1, charindex(sc.[text], '.') - 1) ServerName

  from syscomments sc join sysobjects so on sc.id = so.id and so.xtype in ('P', 'TR', 'V', 'FN', 'IF', 'TF')

  where sc.[text] like '%.%.%.%'

  I have just typed it off in notepad so if it gives you any errors while executing tweak it a little. The query is straight forward to understand.

  Let me know if it helped.

  Venu