I have about 500 tables across a number of servers/databases for which I need to write a script to identify dependencies. Basically these tables are undergoing changes in field size, etc. and we want to identify if any other objects - stored procedures, functions, views etc. that could be referencing them. This list is not final and could be growing as well. I found the following two ways to find dependencies:
--Method 1:
sp_depends 'Your_tableName'
GO
-- Method 2:Using DMV sys.dm_sql_referencing_entities
SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Your_tableName', 'OBJECT');
GO
My question is what is the best possible way to achieve this? Where to put that list so that I could use a while loop to read all the list of tables and list the dependencies? I cannot create SQL tables on production database for table names that are being changed so that I could read in a WHILE loop. Also, I only have access to SSMS. Please advise!
P.S. Please let me know if there is a way to check dependencies on linked servers