Fun with PATINDEX() and DB_NAME()

I’m going to guess everyone can guess that DB_NAME() will give you the name of the current database when called with no parameters. If not, now you know. PATINDEX() is a little bit more fun, so before we get into things, here’s how PATINDEX() works:``` DECLARE @my_var AS NVARCHAR(30); SET @my_var = ‘fun with patterns’;

SELECT PATINDEX(’%pattern%’, @my_var); – returns 10

PATINDEX() returns the position of the pattern in the search expression. It’s 1 indexed for those of you who come from a 0-based string world. Moving forward! So far, I haven’t done anything cunning. But what if you have a crazy situation like this: You have a publisher database. There are two subscriptions set up with bidirectional transactional replication. They are named Subscriber1 and Subscriber2. They are stored on separated physical servers, but they have identical schemas. Funnily enough, and this is where it gets tricky, there’s another database in the picture. One each server there’s a Web database (WebOne and WebTwo). It does reporting and uses tables in the subscriber. It also holds all the stored procedures. Suddenly things get a lot more entertaining because you now have to fully reference table names. (Is this confusing enough without pictures?) What to do?!? Well, for 99% of of your stored procedures, you can use CTRL+H and ignore this blog post. You probably can ignore this blog post for the other 1%. Unless you’re insane, like me, and generate ad hoc SQL from a CLR assembly. If that’s the case, keep reading. So, I have a CLR assembly which contains a function. That function emits SQL code which is executed via sp\_executesql. Rather than maintain two versions of this assembly, one for each server, I decided to use REPLACE, PATINDEX, and DB\_NAME to make my life more fun. DECLARE @condition AS NVARCHAR(10); SET @condition = ‘%Two’;

IF (SELECT PATINDEX(@condition, DB_NAME())) > 0 BEGIN SELECT @magic = REPLACE(@magic, ‘Subscriber1’, ‘Subscriber2’); END