Avatar of Mauro Cazabonnet
Mauro Cazabonnet
Flag for United States of America

asked on 

SQL USE DATABASE VARIABLE

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

Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon