sql server: looking for table usage through out database...

Question

How would I figure out what type of sql code such as procs, functions, views etc. are interacting with my table called TABLE1 through out a given database. Sample code would be very helpful for me.

thanks

Answer 1

Not enough info in your question, but one thing you can do is use SQL Profiler to profile where INSERTs, UPDATEs, and DELETEs are coming from.

I assume you are talking about how an app is interacting with data and what name (of say a sproc) is doing the insert / update / delete.

Look at SQL Profiler, it comes with your client tools install. Filter it to only show connections to your database (either db name or ID).

Answer 2

select so.name, so.xtype
from sysobjects so (nolock)
inner join syscomments sc (nolock) on sc.id = so.id
where sc.text like '%tablename%'

This code will search all SQL Server objects for a reference to your table. You have to run this query for each database.

If a stored procedure uses your table it will appear in this query. The same is true of functions, views, and triggers.

xtype tells you the type of object.

Here are the possible xtype values:

• D = Field names
• F = Foreign Key
• FN = Function
• P = Stored Procedures
• PK = Primary Key
• S = System Tables
• U = User tables
• V = Hidden tables

Answer 3

If you've been good and created your SPs/views/functions after your table was created, sp_depends will tell you evertyhing referencing the table. Exept for dynamic sql that is.