I have a view and I need to remove a column in the view. Before doing that modification I need to determine which are all the applications that access the view. I need that information so as to make sure that removing the column would not break any application code that access the view.

Is there any DMV or system stored procedure that contains such information? Or is there any system tables that store such information?

  • 1
    Information about what? Whether some app runs once in a while SELECT column_in_question FROM view_a ; We don't have access to your application code. – ypercubeᵀᴹ Sep 29 '16 at 11:44
  • You can find if there are other objects/views that depend on this view, in their definition (using the system dependency tables). But I don't think the column details are stored there. – ypercubeᵀᴹ Sep 29 '16 at 11:47
  • 1
    You might want to try the Profiler with some column filters. That's is if you have no access to the code... – Nelz Sep 29 '16 at 19:56
  • A server side trace would be much better than a profiler which has perf consequences. – Kin Shah Dec 23 '16 at 16:31

If you do not have access to the code from the applications accessing your database, using the Profiler over a reasonable period of time, as commented by @nelson-casanova, is your best option.

If you do have access to the application code, check there. Also check the definition of any stored procedures, functions, or other views.

I presume you need the column removed for security reasons? If so, perhaps you can modify your view to use IS_SRVROLEMEMBER and return those columns only when authorized? Copy your view and name it 'original_view', copy your view and edit your columns then and name it 'modified_view. Now edit your view and call the new views:

IF IS_SRVROLEMEMBER ('role_view_all') = 1      
   select * from original_view;   
ELSE IF IS_SRVROLEMEMBER ('role_view_all') = 0      
   select * from modified_view;

If there is an application that you missed in your Profiler run, you can temporarily add that application's user(s) to the 'role_view_all' role, while developers sort out a workaround or fix their queries.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.