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: 257
  • Last Modified:

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
0
jackbenson
Asked:
jackbenson
  • 6
  • 4
1 Solution
 
rajvjaCommented:
0
 
jackbensonAuthor Commented:
thanks for this - but it does not show column dependancy in:
Tiggers
Stored procedures
0
 
rajvjaCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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