0

I currently have external software that is regularly updated and as it is new databases are created (I cannot control this) the naming structure is:

  • FIS_SUB_0030310QGT810_700133_HULLCO_1617ILR_015_040417_125840
  • FIS_SUB_0030310QGT810_700133_HULLCO_1617ILR_019_130617_142530

I can use the system table to identify the most up to date one but how do I refer to this as the active database as the tables within it will not change?

Eg. a table valid.learner exists in both but I want it to always use the last DB created

Is this possible?

EDIT I will be querying various databases all located on the same server. all databases have static structures within them so it is just the DB name I am trying to resolve.

Thanks for current responses I think the easiest to try is to copy the DB it self and rename it

DannyG1980
  • 1
  • 3
  • Naming convention for a database is THAT long of a string? You will need `dynamic SQL` whenever you create scripts, to create the `USE @DatabaseName` instruction with the name of the latest database. – Rigerta Jun 21 '17 at 09:36
  • thanks for the quick reply can I refer to a field name to get the database name rather than manually changing it every time the system updates?? – DannyG1980 Jun 21 '17 at 09:38
  • The database is a rather ... fundamental thing. It's *NOT* an Excel spreadsheet. Saying that the database name changes is similar to saying that your machine's name and IP changed. I suspect the database is generated by an ORM and people that don't actually care about maintenance? Because this is 100% their bug. – Panagiotis Kanavos Jun 21 '17 at 09:46
  • I answered with a way of doing this programatically but I totally second the comment of @PanagiotisKanavos. Maybe you should find a way of discussing and changing this. It is by far not good practice. Starting with the [naming convention](https://stackoverflow.com/questions/3593582/database-naming-conventions-by-microsoft) – Rigerta Jun 21 '17 at 09:47
  • Where do those queries run? Why not just change the target database in the connection string? Are you running cross-database queries? If not, just change the connection – Panagiotis Kanavos Jun 21 '17 at 09:48
  • 1
    It's look like a sentence DBname.. Just kidding.. whenever you run the query just use 'Use YourDBname' – Vijunav Vastivch Jun 21 '17 at 09:51
  • I understand it is not good practice the software is uk government software and as such any change request will likely take years to implement then break the system entirely so getting this changed is not really an option – DannyG1980 Jun 21 '17 at 09:56
  • The Question here is, How did you know the newly Database name created by Something Software? – Vijunav Vastivch Jun 21 '17 at 09:57
  • 1
    @DannyG1980 *how* are you running the queries? Do you have cross0-database queries? Otherwise just change the connection string. This *IS* important. How many tables too? You can use synonyms if there aren't many tables. You'll have to update the synonyms periodically though – Panagiotis Kanavos Jun 21 '17 at 09:59
  • @DannyG1980 and what is that database? How big? If it's only reference data, you can just copy and/or rename it. – Panagiotis Kanavos Jun 21 '17 at 09:59
  • The OP Question is not too clear...@DannyG1980 much better to edit your question.. Step by step. – Vijunav Vastivch Jun 21 '17 at 10:00

3 Answers3

3

You could do the following:

declare @UseStatement nvarchar(100), @DatabaseName nvarchar(128) 
select @DatabaseName = Name from sys.databases WHERE YourConditionToFindTheLatestOne) --make sure you only get one row here, only the latest db name

select @UseStatement = N'USE ' + QUOTENAME(@DatabaseName) + ';'

print @UseStatement
exec(@UseStatement)

You will need to do this at the top of every script you have, it will replace the usual static Use DatabaseName; that generally is used when you know the database name beforehand.

Rigerta
  • 3,959
  • 15
  • 26
1

The question is rather broad. A lot of information is missing, eg are there any cross-database queries, what type of processing is performed, how big is the database? Are all databases on the same server?

There are a lot of options:

  • It sounds like this database is reference data from a government application. In which case, one can just copy it to another database with a well-known name.

  • Rigerta Demiri already posted how you can execute queries against the latest database if you don't run cross-database queries, ie if you don't want to join data between your database and the application's. Just change the target database. You can do that in the connection string too.

  • You can also just copy the data from the latest database into your own, with an SSIS package. The source (ie the reference database) can be modified programmatically. It can also be specified as a package parameter.

  • You can create SYNONYMs in your database for the reference tables with CREATE SYNONYM MyTable FOR ThatDB.dbo.MyTable. This can be automated with a script that reads the latest database name, drops the old synonyms and creates new ones with the latest database.

Which is best depends on the data volume, type of data, how often it changes, how it is used, how much time you are willing to spend on maintenance etc

UPDATE

I forgot a more advanced scenario - if the data does change, read only the modified rows from the source database and copy them to the target. In other words, treat it just like any other ETL scenario. SQL Server offers lightweight change tracking since 2005, which has to be activated per-table. If you don't want to modify the source each time a new version is deployed, you'll have to drop back to checkiing timestamps or created/updated fields.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
0

Below may help to find latest database:

select top 1 name 
from sys.databases
where name like 'FIS_SUB_0030310QGT810_700133_HULLCO_1617ILR%'
order by create_date desc
Pawel Czapski
  • 1,856
  • 2
  • 16
  • 26