Mauro Cazabonnet
asked on
SQL USE DATABASE VARIABLE
Not sure why I'm getting syntax error near GO
on EXEC (@command)
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;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you know about the undocumented stored procedure sp_MSforeachdb?
ASKER
But yes go is not needed
Thx!!!!!