1

I am working on migrating an application from one server to the other. According to the connection string of this application, it is touching different databases. Meaning a view query in DB1 will touch a table in DB2. So while migrating this application, I constant get to see chain of 'Database unavailable' errors and every time I see such error, I have to migrate that specific database.

I am wondering, since we have ER diagrams to know about relationships between tables in a database, is there any way in SQL server to know the relationships/linkages between different DATABASES in a server? Are there any tool that does this?

Rick
  • 1,392
  • 1
  • 21
  • 52
  • 2
    There really isn't an easy way to do that. sys.sysdepends uses the object_id and that is useless when the object is in another database. – Sean Lange Jul 09 '19 at 20:58
  • If other databases are accessed using linked server, you can do a code search for linked server names (defined on the server). Linked servers can be found using `SELECT * FROM sys.servers`. Code search queries can be found at: https://stackoverflow.com/questions/14704105/search-text-in-stored-procedure-in-sql-server and https://www.codeproject.com/Tips/603593/Find-text-in-Stored-Procedures-View-Trigger-and-Fu – Alex Jul 09 '19 at 22:15

1 Answers1

2

Depending on number of databases you have, here would be a somehow quick way you can find that out (number of required search = number of available databases in the server):

  1. Use 'SQL Search' application of Red-get (https://www.red-gate.com/dynamic/products/sql-development/sql-search/download) and search for the other database names one after another by selecting your database of interest. Select all objects.
  2. If you have metadata oriented design (a Stored Procedure looping through the names of different other Stored Procedures / Functions from different databases which are stored in a table as metadata and executing them with a wrapper Stored Procesure), then you will have make use of SQL Locator software (http://www.sqllocator.com/Downloads.html) to search for database names in SQL Table values.

Both of the above software are free.

  • You need to have SSMS (SQL Server Management Studio) installed to be able to use this application. After installation, ‘SQL Search’ will be directly available in your SSMS as an add-on.

  • SQL Locator can be directly used by providing the SQL Server name and your SQL Server credential.

Note:- The above steps will help you find out the referenced databases from a certain database within the same SQL Server. If you need to find out databases from Linked Server (I do not believe your question is asking that anyways), then you will have to smartly utilize the same above tools to find the external server reference by searching the external server name.

san
  • 1,415
  • 8
  • 13