sql - Is there a way I can identify the tables in a module used in different ways?

Question

I have a database which has about 10-15 table starting with abc_tables

dbo.abc_patientsname,
dbo.abc_employeesname,
dbo.abc_departmentsname 

and so on.

Is there a way where I can find if any of them are used to create other abc_ tables (Ex:2 or more abc_tables can be used to create a main abc_table) or any of the abc_ tables which are used in views or sp or reports or ssis packages ?

Answer 1

Here's a way I check for where different objects are used when we need to 86 them. I'm sure there's a more efficient way, but this works for me when I need something quick.

SELECT
    so.[name],
    so.[type],
    so.[type_desc]
FROM syscomments AS sc
LEFT JOIN sys.objects so
    ON sc.id = so.object_id
WHERE 
    sc.[text] LIKE '%dbo.abc_patientsname%'
    OR sc.[text] LIKE '%dbo.abc_employeesname%'
    OR sc.[text] LIKE '%dbo.abc_departmentsname%';

Running this for my client's table it returns:

+-------------------------------+------+---------------------------+
|             name              | type |         type_desc         |
+-------------------------------+------+---------------------------+
| OrderItems2                   | TF   | SQL_TABLE_VALUED_FUNCTION |
| kaeGetActiveClientList        | P    | SQL_STORED_PROCEDURE      |
| rpt_InvoiceClient             | P    | SQL_STORED_PROCEDURE      |
| kaeGetCallCountsAnsw          | P    | SQL_STORED_PROCEDURE      |
| rpt_CombinedCampaign          | P    | SQL_STORED_PROCEDURE      |
| ReportStatusXML               | FN   | SQL_SCALAR_FUNCTION       |
| kaeGetACMGInBoundSalesByAgent | P    | SQL_STORED_PROCEDURE      |
| ssisSempris                   | P    | SQL_STORED_PROCEDURE      |
| kaeGetAgentPay_WK             | P    | SQL_STORED_PROCEDURE      |
| trig_tblClientsLastModified   | TR   | SQL_TRIGGER               |
+-------------------------------+------+---------------------------+