May 26, 2011 at 2:04 pm
How one might one retrieve a list of views containing a reference to a specific column (even if that column is not returned as part of the view, but is used in a conditional statement)?
I've used sp_depends 'table_name' to retrieve views dependent on a specific table, but now I would like to retrieve a subset of those views conditionally referencing a specific column in that table.
Thank you
May 26, 2011 at 2:09 pm
here you go...look at the WHERE statement at the end..specific column name, and must be a view...you can change that to fit your needs if needed:
/*
obj_nmcol_nmdep_obj_nmdep_obj_typedep_col_nm
GMAACCPACTTBLKEYVW_GMBENEFViewNULL
GMAACCPACTTBLKEYVW_CDBGACCOMPViewNULL
*/
DECLARE @ColumnName VARCHAR(100)
SET @ColumnName = 'ACTTBLKEY'
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where col.name = @ColumnName
AND depobj.type='V'
--ROLLBACK TRAN
--COMMIT TRAN
Lowell
May 26, 2011 at 2:51 pm
Unbelievable. Beautiful. I plugged my column name into variable and voila, totally awesome. Thanks.
May 26, 2011 at 2:57 pm
glad I could help!
Thansk for the feedback!
Lowell
May 26, 2011 at 3:03 pm
Kindly show me the edit that would include stored procedures in the result set:unsure:
May 26, 2011 at 3:07 pm
hxkresl (5/26/2011)
Kindly show me the edit that would include stored procedures in the result set:unsure:
it's right at the end: remove the last AND to see ALL dependancies, ro make it look like the example below for views/procs/functions
AND depobj.type='V'
--change to
AND depobj.type IN('V','P','FN','IF','TF')
Lowell
May 26, 2011 at 3:37 pm
oh, it's an IN, simple!~:kiss:
May 26, 2011 at 5:05 pm
^^^^^^^^^
Old Skool 😀
SELECT *
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE COLUMN_NAME = ''
admittedly, it doesnt cover sp's
These may be useful
SELECT *
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS
May 26, 2011 at 5:51 pm
MysteryJimbo, however this doesn't retrieve views that make reference to the column in areas other than column headings. In many cases, my views are not pulling in the column but rows conditionally on the basis of whether that column, which has a bit datatype, is 0 or 1. Therefore, your script is very limited.
May 26, 2011 at 8:25 pm
Lowell (5/26/2011)
glad I could help!Thansk for the feedback!
Great code but it's another reason for me to hate what MS thinks is "progress". :sick: The f4 key use to have that functionality and more in Query Analyzer.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2011 at 12:33 am
hxkresl (5/26/2011)
MysteryJimbo, however this doesn't retrieve views that make reference to the column in areas other than column headings. In many cases, my views are not pulling in the column but rows conditionally on the basis of whether that column, which has a bit datatype, is 0 or 1. Therefore, your script is very limited.
I wasnt really giving a script, meerly pointing out MS progress as Jeff is hinting at as well.
May 27, 2011 at 4:20 am
well, mince and dice however you like, it was exactly what I needed. Thanks.
ps. AND made my day, in fact made my two days.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply