0

(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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Sazed
  • 1
  • 2
  • 1
    Table variables go out of scope when you call dynamic sql... use a temp table instead. And this `'INSERT INTO'+ @tempFinalTable + ' (DB, TABLE_LOC)` should be this `'INSERT INTO @tempFinalTable (DB, TABLE_LOC)` - you want the table name as part of the string. (Well this actually `'INSERT INTO #tempFinalTable (DB, TABLE_LOC)`). – Dale K Dec 09 '21 at 22:38
  • As @DaleK said, your string ends up as 'INSERT INTO@tempFinalTable'. – jmag Dec 09 '21 at 22:42
  • And you try and assign `DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tempTableVariable)` *before* you put anything into that table variable. – Dale K Dec 09 '21 at 22:46
  • 2
    *Why* are you using `NOLOCK` against `sys.databases` of all things..? The fact you are suggests you dump it in every `FROM`... Stop doing that, it's a **terrible** idea. – Thom A Dec 09 '21 at 22:55
  • 2
    The insert doesn't even need to be in the dynamic statement, you could just use `INSERT INTO ... EXEC`. – Thom A Dec 09 '21 at 22:57
  • @DaleK Thank you very much! – Sazed Dec 09 '21 at 23:22
  • @jmag `'INSERT INTO'+ @tempFinalTable + ...` is an attempt to combine a string with the contents of a _table variable_. That's much more fun than simply resulting in `'INSERT INTO@tempFinalTable ...'`. – HABO Dec 10 '21 at 02:54
  • Tip: The best practice when assembling object names, e.g. `@MyDB`, into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Dec 10 '21 at 02:57

1 Answers1

0

Your primary issue is that you have a syntax error, because you are trying to use the value of the table variable as part of the dynamic SQL string, but only a scalar variable can be used like that.

Even if you put the table variable within the string properly, you would still run into scoping issues, because variables don't pass over to dynamic SQL.

A much better solution is to forgo the table variable, and instead build a UNION ALL query to select it straight from dynamic SQL.

Note also:

  • Object names are nvarchar(128) you can use the alias sysname
  • You need to quote names with QUOTENAME
  • Don't use dbo.sysdatabases and INFORMATION_SCHEMA, they're deprecated. Instead use sys.databases and sys.tables
DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(CAST('
SELECT
  DB = ' + QUOTENAME(d.name, '''') + ',
  TABLE_LOC = ' + QUOTENAME(d.name, '''') + ' + ''.'' + s.name + ''.'' + t.name
FROM ' + QUOTENAME(d.name) + '.sys.tables t
JOIN ' + QUOTENAME(d.name) + '.sys.schemas s ON s.schema_id = t.schema_id
'
      AS nvarchar(max)), 'UNION ALL')
    FROM sys.databases d
    WHERE d.database_id > 4 -- not system DB
);

PRINT @sql; --for testing

EXEC sp_executesql @sql;

I can't say whether you need QUOTENAME within the dynamic query, because I don't know what result you want, or what you intend to do with the result. If need be, you can do

  TABLE_LOC = QUOTENAME(' + QUOTENAME(d.name, '''') + ') + ''.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name)
Charlieface
  • 52,284
  • 6
  • 19
  • 43