[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

View dependencies on a table column

Posted on 2006-10-30
5
Medium Priority
?
408 Views
Last Modified: 2008-03-17
Is it possible to view dependencies (stored proc's) on a particular table column, rather than on the whole table?
0
Comment
Question by:Rouchie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 1000 total points
ID: 17835932
No. But you can search the syscomments table - e.g.


SELECT
  so.name,
  so.id,
  so.xtype
FROM
  sysobjects so
INNER JOIN syscomments sc ON  sc.id=so.id
AND sc.text LIKE '%mycolumnname%'

Hopefully this is not a common enough string to return everything - will also show you views and functions
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 1000 total points
ID: 17836018
there is no bullet proof way of doing that, as your proc maybe saying
select * from YourTable without explicitly going for a column name

if you want to query sysobjects table add where xType='P' in Nightman's query

SELECT
  so.name,
  so.id,
  so.xtype
FROM
  sysobjects so
INNER JOIN syscomments sc ON  sc.id=so.id
AND sc.text LIKE '%mycolumnname%'
Where so.xtype='P'

0
 
LVL 29

Expert Comment

by:Nightman
ID: 17836440
An interesting addendum - when you rebuild an object in SQL, for some reason the dependancies (from the sysdepends table) are dropped.

e.g.
1. Create View A
2. Create View B that uses View A
3. Note the dependancies
4. Rebuild view A (forces a recompile)
5. Look at the dependancies again - gone?

use syscomments as your only reliable option.

This has been in place since SQL 7 (at least that I know of - maybe even earlier, although I nevere cared enough to check) - I have asked MS for an explanation (as this is a very useful way of identifying dependacies in a development environment to build an 'autodeploy' script in the correct sequence - still awaiting resolution.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17836520
Nightman,
You are right sysdepends table can go out of sync, also I usually script objects in one script from one DB to other, since objects are scripted in alphabatical order SQL will create them in exact order (unless you check script dependent objects option).
Running such script on another DB always gives warning, something like "no entry was created in sysdepends table as "[yourobjectname]" is referencing a missing object "[missingobjectname]"......"
I always end up using syscomments table.
I wish SQL was more like ORACLE where it checks for dependent objects before letting you create another one that references them...
rw3admin
0
 
LVL 25

Author Comment

by:Rouchie
ID: 17840617
Hi guys.
I ran this query against the database and it found 2 procedures, as required.  Thanks very much for your input, I'll remember this trick for future use!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question