Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ssiss
Starting Member
10 Posts |
 Posted - 2008-09-08 : 13:25:28
|
| How to find out dependancies on a column of a table.I looked into sp_depends, but we can't give column name |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-08 : 14:06:03
|
| have a look at INFORMATION_SCHEMA.KEY_COLUMN_USAGE and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-08 : 14:07:13
|
| If you're talking about references to that column in code - No great way to do that. You can check DB by DB for code that references the column using something like:select distinct object_name(id) from syscomments where text like '%<Column_Name>%'But that won't catch any saved scripts, inline code, etc. And if the column_name has bad name like [ID] then you're going to get way to many false positive hits.Be One with the OptimizerTG |
|
|
|
ssiss
Starting Member
10 Posts |
Posted - 2008-09-08 : 14:18:28
|
| I ran the following code but it didnt return table names, it's returning sp's and other stuffs though. How do i get the table names use Abcgoselect name from syscomments c join sysobjects o on c.id = o.id order by name |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-08 : 14:24:18
|
| So by dependencies you are referring to Foreign Key references?try "sp_help <TableWithYourColumn>"output includes Foreign Key infoBe One with the OptimizerTG |
|
|
|
ssiss
Starting Member
10 Posts |
Posted - 2008-09-08 : 14:31:27
|
| Basically I want to find out the column 'A' in Table 'ABC' is being used where..I am looking for the output that should give me list of all the tables, sp's and other places where this column might have been used. kinda like what sp_depends 'tablename' returns for table |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-08 : 14:56:25
|
| >>I am looking for...list of all the tables, sp's and other places where this column might have been used. As I said earlier there is no easy, single thing you can run to determine all references to a column by "tables, sp's and other places". For SPs, Triggers and UDFs you can use the query I gave earlier from sysComments (or information_schema.routines). But that will only return ojbects from database you are querying so if you reference tables in code from other databases you will need to query all those databases as well.For "Tables", I assume you mean other tables with the same column_name as the column_name you are checking on. ie: when referencial integrity was not established via Foreign Key Constraints. [Information_schema].[columns] can be used to find all [table_name]s for tables and views that has a column by that name. Again that only works in the database you are running the query in so you need to run that in all possible databases also.For "tables" where there are Foreign Key constraints you can use the views Visakh mentioned or "sp_help" commands to see those references.For "other places" - what does that mean? Saved script files, in-line application code, T-Sql Job step commands, other database servers that use linked server calls, etc. You will need to define what those "other places" are and devise a seperate method to check for those.The easy alternative is to simply drop the column and see what breaks :)Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
How to find out dependancies on a column ?