In SEL SERVER 2005 is there a way to check column dependencies on a table

I want to make some changes to some tables in my database and i want to be able to see what Triggers, Foreign Keys, Stored Procedures, Views would be affected by the change.

I have tried EXEC sp_depends - but this shows dependancies of a table - but i want to try to get this information based on a specific column - how can i do this?

many thanks

jack
LVL 1
jackbensonAsked:
Who is Participating?
 
jackbensonAuthor Commented:
The solution works - but if you have two columns in two tables with the same name then you will have to work out exactly what column the result is talking about manually.

I was looking for a product similar to SQL Management Studio that shows the dependacies of each column in a similar way as it does for the table as a whole.

Rajvja - thank-you for your help.
0
 
rajvjaCommented:
0
 
jackbensonAuthor Commented:
thanks for this - but it does not show column dependancy in:
Tiggers
Stored procedures
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rajvjaCommented:
0
 
jackbensonAuthor Commented:
to confirm that i understand it correctly, if i use the following script the result will include Views, Stored Procedures, User Defined Functions, Constraints and Triggers [but not Foreign Keys] that are dependant on the EmployeeID Column in the tbl_Employee table:

SELECT DISTINCT sysobj.name
FROM syscomments
INNER JOIN sysobjects sysobj ON syscomments.id = sysobj.id
WHERE charindex('tbl_Employee.EmployeeID', text) > 0
0
 
rajvjaCommented:
See the attachment. It will load all the dependencies and populate to a table.
You can query that table to get the result you want.

I hope this will help
LoadObjectDictionary.sql
0
 
jackbensonAuthor Commented:
Rajvja,

thank-you for the script.

is there any documentation that goes with it?

Do i need to create a table called: DatabaseObjectDictionary to store the info in?
What should i set the value of: @strDBName_DwarfDB
What should i set the value of: @strDBName_DwarfPortable

Should the DwarfDB in the lines DwarfDB.dbo.syscommentsbe changed to my database name?

I have no idea what to do with: DwarfPortable.dbo.sysobjects - SQL server is thorwing an error when running this code

thanks

jack


0
 
rajvjaCommented:
Hi,

  Belated reply. The script is commented. No need to populate to a table. It could be a temp table and your search cold be on that.

Chnage DwarfDB to ypur DB name.

DwarfPortable is also my database.
0
 
jackbensonAuthor Commented:
I am really sorry to keep asking but i just dont get it.

what should these variables be set to?

Set @strDBName_DwarfDB       = 'DwarfDB'
Set @strDBName_DwarfPortable = 'DwarfPortable'

Also - these:
DwarfDB.dbo.sysobjects
DwarfPortable.dbo.syscomments

if DwarfDB and DwarfPortable are borth database names - why are there two databases - i have only one?

Agan i am sorry if i am asking the obvious!

thanks

jack
0
 
jackbensonAuthor Commented:
Hi

i have not it to work.

i just removed DwarfDB and DwarfPortable fromt eh lines below:

DwarfDB.dbo.sysobjects
DwarfPortable.dbo.syscomments

I set both of these to my database name:

Set @strDBName_DwarfDB       = 'MyDB'
Set @strDBName_DwarfPortable = 'MyDB'

it returns every result twice but it works.

thanks

jack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.