Is there a way to determine if a table is referring to a different database / server?

I am creating a personal PHP use script using a driver MSSQL

in PDO (as such there is no plan to let someone else but me use this) which, when given the table name, returns all the objects it referenced

using MySVR.MyDB.dbo.Staff

as my table i first start with this query

SELECT
    referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
    referencing_object_name = o.name,
    referencing_object_type_desc = o.type_desc,
    referenced_schema_name,
    referenced_object_name = referenced_entity_name,
    referenced_object_type_desc = o1.type_desc,
    referenced_server_name, referenced_database_name    
FROM
    sys.sql_expression_dependencies sed
INNER JOIN
    sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
    sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
    referenced_entity_name = 'Staff'
    AND referenced_server_name IS NULL
    AND referenced_database_name IS NULL
ORDER BY
    o.name

      

which lists all the objects on MyDB

that reference my staff table

I know that my table Staff

is used in another database in the stored procedure MySVR.MySecDB.dbo.spMyDB_Staff

. To confirm this, I change the PDO connection string and rerun the query, but referenced_database_name = 'MyDB'

to make sure I only get results that are referenced MySVR.MyDB.dbo.Staff

and not MySVR.MySecDB.dbo.Staff

.

But the problem is how to determine whether the references Staff

to MySecDB

, or any other database is MyDB

to use it? Likewise, how would I determine if my table is referring to another server, for example in MyOtherSVR.anotherDBDB.dbo.spMySVR_MyDB_Staff

?

I was actually thinking about running a query on every server and database (changing the PDO connection string in a loop) and checking if I have any results, but that seems overkill to me.

So, is there a way to determine if a table is referring to a different database / server?

+3


source to share


1 answer


Links in other databases of the same SQL instance are somewhat easy to find - you can run a similar query against the one you sent against each of the other databases in the instance, provided that you have sufficient permissions on all databases.

If you don't have access to all databases in one instance, you're stuck.



Also, in the worst case it is impractical and in the worst case to define links on other servers. This would only be possible if you both knew and were the administrator of all servers that could link to this database.

0


source







All Articles