Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

View dependencies on a table column

Is it possible to view dependencies (stored proc's) on a particular table column, rather than on the whole table?
0
Rouchie
Asked:
Rouchie
  • 2
  • 2
2 Solutions
 
NightmanCTOCommented:
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
 
rw3adminCommented:
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
 
NightmanCTOCommented:
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
 
rw3adminCommented:
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
 
RouchieAuthor Commented:
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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now