0

Can I make changes in system defined views like I want to add a column that will provide columns name which has been foreign_key applied ?

Like, changes in sys.foreign_key_columns.

Please provide me script or source.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Deep
  • 1
  • 1

1 Answers1

12

Microsoft will tell you that it isn't possible to change the definition of the system views. This is not true for the most part (after all, Microsoft themselves can do it with an upgrade) but it is very true that you, as a user, shouldn't be doing this, and the steps necessary for doing it are (deliberately) very complicated. If you did manage to succeed in changing the system views, your server is no longer covered by support, and you might not be able to install future updates. Basically, don't go this way.

As a more practical approach, consider wrapping the system views in views of your own and using those instead. If you want to make these views available in all new databases, you can add them to the model database.

For the specific case of a view that also includes the name(s) of the column(s) the foreign key is referring to, here's one view that will do the job (and is a friendlier view of foreign keys in general):

CREATE VIEW ext_foreign_keys AS
SELECT 
    OBJECT_NAME(constraint_object_id) AS foreign_key_name,
    OBJECT_NAME(parent_object_id) AS parent_table_name,
    COL_NAME(parent_object_id, parent_column_id) AS parent_column_name,
    OBJECT_NAME(referenced_object_id) AS referenced_table_name,
    COL_NAME(referenced_object_id, referenced_column_id) AS referenced_column_name
FROM sys.foreign_key_columns

If you need the data from sys.foreign_keys itself, join it up. I'm being lazy here by using the system functions OBJECT_NAME and COL_NAME; if you prefer, you can also join explicitly on sys.tables and sys.columns, though that's a lot more typing. You may want to do that anyway because there are subtle issues with locking when using these functions.

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • Thanks @Jeroen. Can you provide any source to make it happen? – Deep Jun 19 '17 at 12:00
  • @Deep: I updated the answer with a suggestion for the kind of view you may be after. If you rather mean the code/steps for changing the system views themselves, I could supply that, but I won't. It would be irresponsible to put something like that on Stack Overflow, even if accompanied by stern warnings. The few people who want to do this for legitimate reasons should also have the background knowledge required to figure it out themselves (or if not, they could make a separate question about it). – Jeroen Mostert Jun 19 '17 at 12:39