Sql-server – Fixed database paths in stored procedures/views – is there a smoother way of doing this

sql serverstored-proceduresview

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers.

Every time we migrate to a new database server, something breaks because some obscure procedure or view didn't get fixed at the same time. Documenting them all is already underway.. I still have to ask – isn't there a simpler way of doing this?

Would it be possible for a stored procedure to extract the connection string from a table – let's say "dbo.linked-databases", and have a unique row per database being references, where one of the columns specify the connecting string? Can stored procedures then SELECT this, and expand it as a variable?

Sorry if the question doesn't make any sense, as I'm quite new with SQL.

Best Answer

A linked server definition includes the name of the server that you are connecting to and includes the security settings for the linked server. If you (assuming Microsoft SQL Server) "SELECT * FROM master.sys.databases" you will see all of your linked servers.

Therefore, if you script out all of the linked servers and recreate them on the new server, then you should be able to set up the new server just the same as the old server. (Exception is that any SQL Server logins used in linked servers will not include the passwords, so you will need to update the scripts.)

Of course, the other issue is that one of the servers that you link to will change and you need to update your linked server settings in the primary server.
From that you can determine which linked server definitions need to be changed.

I suggest that you name your linked servers according to their purpose, rather than with a server name. (E.g. FinancialUpdates rather than AccountingSQL005.) That way any code hidden away in stored procedures will not need to be changed when some server name changes or a database is moved to a new server. This also suggests using separate linked servers, even if pointing to the same physical server, for different sets of data.

Just update the linked server by scripting out the definition and change the server name. Then use sp_dropserver and sp_addlinkedserver to drop the old definition and add the new one.

Related Solutions

Sql-server – standard way to use common data and procedures across multiple databases

In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.

USE CentralDB;
GO

CREATE TABLE dbo.Dates(...);
GO

USE OtherDB;
GO

CREATE SYNONYM dbo.Dates
  FOR CentralDB.dbo.Dates;

Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note that some functionality is missing, e.g. you can't set up foreign keys.

In a previous life I managed a system where we would have ~500 databases with nearly identical schema on each instance. We managed common data using synonyms, and deployment for code that had to be local using Red-Gate SQL Compare an SQL Farms Combine (very much like Multi-Script).

Sql-server – Tracking down unused Views and Stored Procedures

For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat.

To find unused stored procs, please see the answer as explained by Aaron Bertrand.

Also, there is an good read to identify as explained here in the article How to find unused Stored Procedures in SQL Server 2005/2008

To tack down unused views:

a) Generate a List of non-recently-used code (by querying the plan cache).

b) Script the Views/Sprocs/etc that aren’t in cache (for whatever reason) using ALTER scripts. (i.e. the SSMS ‘Modify’ option when right-clicking on an object).

c) Execute the ALTER script generated by SSMS in production. Since the object wasn’t in cache, there’s no harm and no foul in worrying about recompilation overhead. And since the script is an ALTER (not a DROP/CREATE), permissions are retained intact.

d) But, if the object references invalid objects, it’ll throw an error and I can definitively know that it’s not only NOT used, but one of the culprits that’s causing me grief during replication snapshot procedures.

Also read as mentioned by Jonnathan@ here

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. Since you want a method that works across 2000-2012 your only real option to guarantee you catch the usage is to use a server-side trace with the Audit Database Object Access event:

Best Answer

Related Solutions

Sql-server – standard way to use common data and procedures across multiple databases

Sql-server – Tracking down unused Views and Stored Procedures

Related Question