How to make changes in system defined views?

Question

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.

Answer 1

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.