We help IT Professionals succeed at work.

SQL USE DATABASE VARIABLE

852 Views
Last Modified: 2017-03-16
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

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mauro CazabonnetSenior Windows Automation Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017

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