question

ruancra avatar image
ruancra asked

TSQL - Filtering out data

Hi guys I have a requirement to write a query that retrieves a list of all procedures that references either one both tables (Example TableA and TableB). That's easy, got that to work. The other requirment is, the query needs to return the list of procedures only if the column "Balance" is specified in either TableA or TableB. (Can be a SELECT, INSERT, UPDATE or DELETE). If "Balance" is referenced to any other table, the proc shouldn't be in the list. Any ideas?? Thanks Ruan
tsqlsql 2014
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered
sys.sql_modules would be your best option on this select * from sys.sql_modules where (definition like '%tablea%' or definition like '%tableb%') and definition like '%balance%'
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image ruancra commented ·
Thanks, my query did exactly that. Problem is, it returns procs where the column "Balance" is referenced from other tables. So, a proc can have a reference to TableA and/or TableB, but the column "Balance" is referenced in another table.
0 Likes 0 ·
anthony.green avatar image anthony.green commented ·
Do you have a standard aliasing convention in your organisation? Eg TableA is aliased as A, TableB is B etc? You could then extend the balance filter to a.balance or b.balance. Other than that, I believe that would be the closest you would get then you would need to manually look through each of the returned definitions to find the right sprocs which match your requirements.
0 Likes 0 ·
ruancra avatar image ruancra commented ·
Unfortunately not, i have noticed some tables using an alias, and in some other procs they use none, which makes it near impossible in my view.
0 Likes 0 ·
anthony.green avatar image anthony.green commented ·
Does this help? select object_name(d.object_id) from sys.sql_dependencies d inner join sys.columns c on d.referenced_major_id = c.object_id and d.referenced_minor_id = c.column_id where c.object_id in (object_id('TableA'),object_id('TableB')) and c.name = 'balance'
0 Likes 0 ·
ruancra avatar image ruancra commented ·
It doesn't return anything.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.