1

Microsoft SQL Server 2005.

I have quite a lot of experience with day to day writing of SQL scripts, but am less familiar with SQL Admin tasks.

I have inherited a couple of 3rd party systems that require databases to be copied at the end of financial year. These databases are named "data_1415","data_1516" etc. These all exist on the same server.

This all works fine. However at year end I will need to copy / rename the database and then alter all the views and stored procedures in this new version to access the new database name.

So .. is there a more efficient method of updating multiple views/stored procedures?

I also wondered if using synonyms might help, but cannot get these to work unless they point to a specific table, which defeats the object.

In an ideal world I would simply name the databases "This Year" and "Last Year" and avoid doing any changes, but the users want to retain the current naming as it avoids any confusion in selecting the correct database in the 3rd party software.

MiguelH
  • 1,415
  • 1
  • 18
  • 32
  • 1
    I'm not sure if this feature existed back in 2005, but right-clicking a database in Management Studio has a "tasks" option. One of them is to script database objects. Here, you could choose to script views and stored procedures, including drop if exist. Then, it's a matter of search/replace from one set of years to another. I hope that helps. – Mads Nielsen Mar 14 '16 at 19:18
  • 1
    Thanks @MadsNielsen. I can do this in SQL Server 2005. Looks like it could be useful! – MiguelH Mar 17 '16 at 10:46

0 Answers0