2

I want to detect databases beginning with 'NAV'in a MS SQL DB. I tried it with this code:

DECLARE @DBName NVARCHAR(MAX);
SET @DBName = (SELECT name FROM master.dbo.sysdatabases where name LIKE '%NAV%');
EXECUTE ('USE' + @DBName);

But I got the error message:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Database'.

Do know what is wrong there?

WIbadeneralp
  • 71
  • 2
  • 8
  • 1
    do this... `declare @sql varchar(4000) = ('USE' + @DBName); print(@sql)` and you'll see you need a space at a minimum (assuming that query returns only one result). It's always good to print your dynamic sql before attempting to execute it so you can see what's actually being executed. – S3S Jul 06 '17 at 13:31
  • Are you using `sa` to run that query? – Alfabravo Jul 06 '17 at 13:32
  • Obligatory link for dynamic SQL questions for SQL Server: http://www.sommarskog.se/dynamic_sql.html – Ocaso Protal Jul 06 '17 at 13:36

2 Answers2

3

I'm not sure about the keyword database in the error message, but I do spot problems: You concatenate USE and the databasename without a space: USENAV01 doesn't work. You should use 'USE ' + QUOTENAME(@DBName).

Secondly, I don't know what the intention is, but if you do EXECUTE ('USE ' + @DBName); followed by other (dynamic) queries, the following queries are executed on your current connection. In other words, the USE XXX doesn't matter for the following queries.

Thirdly, as mentioned by Jesse in below comment; if you have more than one database with a name like '%NAV%' (which your question suggests), your code is only executed for one of those databases. Which one that will be is unpredictable without an order by. If you want to execute code for all relevant databases, you have to loop through them.

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • 1
    very good point on the follow on queries. I hope this was a water downed example and the dynamic sql is longer, and deserving of being dynamic. – S3S Jul 06 '17 at 13:35
  • 1
    In addition to this, If there is more than 1 database like %NAV% this code will break as you can only assign one to a variable. You'd have to use a temp table and loop through it to be more precise. – Jesse Jul 06 '17 at 13:37
2

Put a space after USE and put brackets around the database name:

DECLARE @DBName NVARCHAR(MAX);
SET @DBName = (SELECT name FROM master.dbo.sysdatabases where name LIKE '%NAV%');
EXECUTE ('USE [' + @DBName + ']');
cloudsafe
  • 2,444
  • 1
  • 8
  • 24
  • Or use [QUOTENAME](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) – S3S Jul 06 '17 at 13:33