SQL USE DATABASE VARIABLE

Mauro Cazabonnet
Mauro Cazabonnet used Ask the Experts™
on
Not sure why I'm getting syntax error near GO
on EXEC (@command)

SET NOCOUNT ON;
DECLARE @i int, @dbcount int, @dbname nvarchar(200), @Command nvarchar(4000)

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT; 
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @dbid SMALLINT;

DECLARE @tempDBnames TABLE
(
	id int,
	dbname nvarchar(200)
)

INSERT INTO @tempDBnames
SELECT ROW_NUMBER() OVER(ORDER BY [name] ASC) AS [id], [name] 
FROM master.dbo.sysdatabases 
WHERE dbid > 4 

SET @dbcount = (SELECT COUNT([dbname]) as [cnt] FROM @tempDBnames)

SET @i = 1
WHILE @i <= @dbcount
BEGIN
	SET @dbname = (SELECT [dbname] FROM @tempDBnames WHERE id = @i)
	
	SET @command = 'USE ' + QUOTENAME(@dbname) + '[CRLF]GO[CRLF]'
	SET @command = REPLACE(@command,'[CRLF]',CHAR(13)+CHAR(10))
	PRINT @command
	EXEC (@command)

	SET @i = @i + 1
END

SET NOCOUNT OFF;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Commented:
'GO' in the dynamic sql is causing the issue

'[CRLF]GO[CRLF]'   -> replace it with a ';'
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
what are you trying to do?
IT Engineer
Distinguished Expert 2017
Commented:
I agree with Aneesh (you don't need the GO) but like Éric, I'm wondering what are you trying to achieve.
Mauro CazabonnetSenior .NET Software Engineer
Top Expert 2015

Author

Commented:
I was trying to run actions on different databases as I looped thru them
But yes go is not needed

Thx!!!!!
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Do you know about the undocumented stored procedure sp_MSforeachdb?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial