Link to home
Start Free TrialLog in
Avatar of jackbenson
jackbensonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of rajvja
rajvja
Flag of United Kingdom of Great Britain and Northern Ireland image
Avatar of jackbenson
jackbenson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

thanks for this - but it does not show column dependancy in:
Tiggers
Stored procedures
Avatar of jackbenson
jackbenson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of rajvja
rajvja
Flag of United Kingdom of Great Britain and Northern Ireland image
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
Avatar of jackbenson
jackbenson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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


Avatar of rajvja
rajvja
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of jackbenson
jackbenson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of jackbenson
jackbenson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of jackbenson
jackbenson
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial