Solved

View dependencies on a table column

Posted on 2006-10-30
5
391 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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