(Working with Microsoft SQL Server Management Studio)
I have a query that should, in theory, return a table containing all of the tables contained within the databases of the server to which I am connected.
However, whenever I run the query, I get the following error:
Msg 137, Level 16, State 1, Line 17
Must declare the scalar variable "@tempFinalTable".
The query itself
DECLARE @tempTableVariable TABLE (id INT NOT NULL IDENTITY(1,1), DB varchar(1000))
DECLARE @tempFinalTable TABLE (id INT NOT NULL IDENTITY(1,1), DB varchar(1000), TABLE_LOC varchar(1000))
DECLARE @DBIndex INT = 1
DECLARE @Query varchar(1000)
DECLARE @MyDB varchar(1000)
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tempTableVariable)
INSERT INTO @tempTableVariable
SELECT [name]
FROM MASTER.dbo.SYSDATABASES WITH (NOLOCK)
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
WHILE @DBIndex < @RowCount
BEGIN
SET @MyDB = (SELECT DB FROM @tempTableVariable WHERE id = @DBIndex)
SET @Query = 'INSERT INTO'+ @tempFinalTable + ' (DB, TABLE_LOC)
SELECT TABLE_CATALOG, CONCAT(TABLE_CATALOG, ''.'', TABLE_SCHEMA, ''.'', TABLE_NAME)
FROM ' + @MyDB + '.INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_CATALOG'
EXEC(@QUERY)
SET @DBIndex = @DBIndex + 1
END
SELECT *
FROM @tempFinalTable
Any guidance as to where I have made a mistake would be greatly appreciated.