Finding Referenced Objects in SQL Server

When working with a large amount of objects in sql server there are times when you want to know what columns or fields are used in other objects (views, stored procedures, triggers, functions, etc). In SQL here are two queries that you can run to determine what if any dependencies there are.
Just replace the ‘%LastName%’ with whatever you are looking for. The only thing you have to remember is if you have multiple table names with the same named column then you will get a result back with both tables. To prevent this then you might what to fully qualify the table names in your objects and then search for them that way. For example; if you a salesman table and a customer table with ‘LastName’ fields then this could be a problem.

The last query helps identify which tables are used in what Stored Procedure.

[code language=”SQL”]
–Finds all tables that have a particular column
SCHEMA_NAME(schema_id) AS schema_name, AS table_name, AS column_name
FROM sys.tables AS tb
INNER JOIN sys.columns cl ON tb.OBJECT_ID = c.OBJECT_ID
WHERE LIKE ‘%LastName%’
ORDER BY schema_name, table_name;
[code language=”SQL”]
–Finds all objects which have the searched text in it.
–This finds all objects that have ‘%LastName%’ in the definition
SELECT ao.type_desc ,
OBJECT_SCHEMA_NAME(sm.object_id) as schema_name,
OBJECT_NAME(sm.object_id) as object_name
FROM sys.sql_modules sm
LEFT JOIN sys.all_objects ao on sm.object_id = ao.object_id
WHERE [definition] LIKE ‘%LastName%’

This query will tell you what tables are used in stored Procedure

[code language=”SQL”]
–Finds all tables that are used in stored Procedures
Select * from (SELECT AS table_name, as proc_name,
ROW_NUMBER() OVER(partition by, ORDER BY, AS row
FROM sysdepends d
INNER JOIN sys.procedures ou on ou.[object_id]
INNER JOIN sys.tables ii on ii.[object_id] = d.depid
) st where row = 1
order by proc_name, table_name

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , | Posted under ASP .NET 4.0, Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *