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 |
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
 Posted - 2010-08-17 : 12:41:15
|
| HiI know we can see the dependencies of a table,sp etcI want to know whether we have a way to identify dependencies based on a particular column.I mean what all tables,views,sp are dependent on a column |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 13:00:13
|
| no direct method. you can however query catalog views to get this information.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-08-17 : 13:10:19
|
...like...SELECT DISTINCT 'TABLES' AS OBJECT, t.TABLE_TYPE AS OBJECT_TYPE, t.TABLE_NAME AS [OBJECT_NAME] FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON t.TABLE_CATALOG = c.TABLE_CATALOG AND t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE COLUMN_NAME IN ('acct_ledger', 'acct_num') UNIONSELECT DISTINCT 'ROUTINES' AS OBJECT, ROUTINE_TYPE AS OBJECT_TYPE, ROUTINE_NAME AS [OBJECT_NAME] FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_DEFINITION LIKE '%acct_ledger%' OR ROUTINE_DEFINITION LIKE '%acct_num%'Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2010-08-17 : 13:18:09
|
| uhhhhhhIt will take sometime for me to digest the above code and use it for our environmentThanks Bret my freind and Visakh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 13:24:52
|
quote:
Originally posted by X002548
...like...SELECT DISTINCT 'TABLES' AS OBJECT, t.TABLE_TYPE AS OBJECT_TYPE, t.TABLE_NAME AS [OBJECT_NAME] FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t ON t.TABLE_CATALOG = c.TABLE_CATALOG AND t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE COLUMN_NAME IN ('acct_ledger', 'acct_num') UNIONSELECT DISTINCT 'ROUTINES' AS OBJECT, ROUTINE_TYPE AS OBJECT_TYPE, ROUTINE_NAME AS [OBJECT_NAME] FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_DEFINITION LIKE '%acct_ledger%' OR ROUTINE_DEFINITION LIKE '%acct_num%'Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
What if routine definition goes beyond 4000 chars. hence i would prefer belowSELECT OBJECT_NAME(m.object_id) AS Name,m.definitionFROM sys.sql_modules mINNER JOIN sys.objects oON o.object_id = m.object_idWHERE m.definition like '%column name here%'AND o.type='P' if you want to look specifically for procedures add code in green also------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
column dependencies