Solved

View dependencies on a table column

Posted on 2006-10-30
5
385 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
  • 2
  • 2
5 Comments
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 250 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 250 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now