find column dependencies on linked servers

Question

Can anyone share the way how to find the column dependencies on linked servers?

For example: I have a stored proc with the definition

select column1, column2 from linkedserver.db.schema.table

sys.dm_sql_referenced_entities gives me the servername, dbname, schemaname, tablename but I have to know the column names as well, like the columns I have this in definition, like column1, column2.

I want to know cross server column dependencies.

No luck with: Getting SQL Server Cross database Dependencies

Smelser

Answer 1

you can use the built in function sp_describe_first_result_set in sql server

like this:

EXEC sp_describe_first_result_set @tsql = N'SELECT * FROM linkedserver.db.schema.table'

Ischia

Answer 2

I don't have an exact answer for this as I am not sure of a simple solution. If there is not a simple solution there is possibly a more difficult one. My approach to this in that case would be:

1. to get your list of dependencies.

SELECT * FROM sys.sql_expression_dependencies

2. collect a list of your columns in your table and store them somewhere you can call later in the logic.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

3. Next I would write code to parse the code of the dependencies to see if those stored columns get mentioned by name.

SELECT * FROM sys.syscomments

4. I would also need logic that parses the code to handle cases where the table is mentioned, but columns might not be. For example if a procedure is dependent, but only uses "SELECT * FROM" instead of specific column names.

There may be an easier way, but this is how I would go about it. My guess is the overall goal is to speed up traffic and remove columns that are not needed and this would certainly help.

Lotuseater