Solved

View dependencies on a table column

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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