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?
source to share
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.
source to share