Need to find out tables

Transact-SQL
1

Hi All, is there any way to find out tables created in Xyz DB might be used in Proc/Function/Views of Abc DB. Server is same for both Db's(Xyz and Abc).

Thanks in advance.

2

Idea

Will require tweaking depending on your requirements

SELECT   
NAME as 'List Of Tables'  
FROM SYSOBJECTS  
WHERE ID IN (   SELECT SD.DEPID   
                FROM SYSOBJECTS SO,   
                SYSDEPENDS SD  
                WHERE SO.NAME IN
				(   
                                    'Names of stored procedures'
                                     ,  'Names of Functions'
                                     ,  'Names of Views
                                   )
                AND SD.ID = SO.ID  
            )

you can google search

image
image1033×864 56.5 KB

3

For cross-database references, this will work:

SELECT CONCAT(s.name, '.', o.name) this,
       o.type_desc this_type,
       d.referenced_database_name refs_that_db,
       CONCAT(d.referenced_schema_name, '.', d.referenced_entity_name) refs_that,
       d.referenced_class_desc ref_type
--       ,d.*  -- uncomment to get other referenced information, including linked server references
FROM sys.sql_expression_dependencies d
    INNER JOIN sys.objects o ON d.referencing_id = o.object_id
    INNER JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE d.referenced_database_name='name_of_database'
      -- AND o.name in ( 'object_im_looking_for') -- uncomment this if looking for specific object
ORDER BY this, refs_that

You would run this in the ABC database to find objects in there that reference other databases.

Also note that this query could return references to linked server objects, if you have any. You'd want to look at the other columns in sys.sql_expression_dependencies to find those.

4

Thank you @harishgg1 and @robert_volk far your time and support.