Syntax error in the statement

Question

Syntax error in the statement

Naomi Nosonovsky 8,906

Hi,

I wrote the following query and trying to figure out the syntax error: incorrect syntax near WHERE.

DECLARE @SQL NVARCHAR(MAX)

SET @SQL =  (SELECT 'SELECT COUNT(*) FROM rv.' + table_name, +','+ QUOTENAME((table_name) + ' as table_name ' FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'rv'  ORDER BY Table_Name
)

PRINT @SQL

Answer accepted by question author

4 additional answers

Your answer

Answer 1

Hi Naomi Nosonovsky,

Thanks for reaching out SQL Q&A forum

You’re on the right track with building dynamic SQL, but the concatenation in your snippet is a bit off. The clean way to generate counts for all tables in schema rv is to use STRING_AGG with UNION ALL. Here’s a working example:

DECLARE @SQL NVARCHAR(MAX);

SET

@SQL = (

SELECT 

  STRING_AGG(

    'SELECT ''' + t.table_name + ''' AS table_name, COUNT(*) AS row_count FROM rv.' + QUOTENAME(t.table_name), 

    ' UNION ALL '

  ) 

FROM 

  INFORMATION_SCHEMA.TABLES t 

WHERE 

  t.TABLE_TYPE = 'BASE TABLE' 

  AND t.TABLE_SCHEMA = 'rv'

);

PRINT @SQL;

-- Run the generated SQL

EXEC sp_executesql @SQL;

Fixes:

STRING_AGG: Concatenates multiple rows into one string with a separator (UNION ALL here).

QUOTENAME: Safely wraps table names to avoid issues with special characters.

Aliasing: Added AS table_name so you know which table each count belongs to.

Execution: Use sp_executesql to run the dynamic SQL after building it.

Please ref Attached Screenshot

User's image

Hope This helps

Thanks,

Lakshmi.

Answer 2

Erland Sommarskog 133.2K MVP Volunteer Moderator

An even faster way to do the task:

SELECT t.name, SUM(p.rows) AS "rowcount"
FROM   sys.tables t
JOIN   sys.partitions p ON t.object_id = p.object_id
WHERE  p.index_id IN (0, 1)
  AND  t.schema_id = schema_id('rv')
GROUP  BY t.name

Answer 3

Hi @Naomi Nosonovsky ,

SQL Server has a system stored procedure that allows to execute the same T-SQL statement for each table: sp_MSforeachtable

No need for dynamic SQL and string concatenations!

Check it out below.

DECLARE @SchemaName sysname = N'rv';
IF OBJECT_ID('tempdb..#RowCounts') IS NOT NULL DROP TABLE #RowCounts;
CREATE TABLE #RowCounts
(
    schema_name sysname,
    table_name  sysname,
    row_count   bigint
);

DECLARE @where nvarchar(4000) =
    N' AND OBJECT_SCHEMA_NAME(o.id) = N''' + @SchemaName + N'''';

EXEC sp_MSforeachtable
    @command1 = N'
        INSERT #RowCounts(schema_name, table_name, row_count)
        SELECT PARSENAME(''?'', 2), PARSENAME(''?'', 1), COUNT(*)
        FROM ? WITH (READUNCOMMITTED);',
    @whereand = @where;

SELECT *
FROM #RowCounts
ORDER BY schema_name ASC, table_name ASC;

DROP TABLE #RowCounts;

Answer 4

Viorel 126.7K

It depends on what specific problem was posed.

set @SQL =  (
	select string_agg( 'select count(*) as [count], ' + QUOTENAME(table_name, '''') +' as table_name from rv.' + QUOTENAME(table_name), ' union all ') within group (order by table_name)
	from INFORMATION_SCHEMA.TABLES 
	where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'rv'
)

Answer 5

Q&A Assist

AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

The syntax error in your query is likely due to the incorrect placement of commas and the overall structure of the SQL statement. Here are a few issues to address:

Comma Placement: The comma after table_name in the SET @SQL assignment is misplaced. It should not be there.
Subquery Syntax: The subquery in the SET statement is not correctly formed. You need to ensure that the SELECT statement inside the parentheses is valid and returns a single string.
Concatenation: Ensure that you are concatenating strings correctly without unnecessary commas.

Here’s a corrected version of your query:

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = (SELECT STRING_AGG('SELECT COUNT(*) FROM rv.' + QUOTENAME(table_name) + ' as table_name', ', ') 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'rv')

PRINT @SQL

Changes Made:

Used STRING_AGG to concatenate the results from the subquery.
Removed the misplaced comma and corrected the syntax for QUOTENAME.

This should resolve the syntax error you are encountering near the WHERE clause.

Naomi Nosonovsky 8,906 Reputation points

2026-02-17T18:39:23.4+00:00

Trying this syntax now, but the SQL seems to get truncated.
Naomi Nosonovsky 8,906 Reputation points

2026-02-17T18:57:19.16+00:00
My new query is DECLARE @SQL NVARCHAR(MAX)

SET @SQL = (SELECT TOP (10) STRING_AGG(CAST('SELECT COUNT(*) as cnt', ' + QUOTENAME(table_name,'''') + ' as table_name AS nvarchar(max)) FROM rv.' + QUOTENAME(table_name) ' , ' ' )

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'rv') has a syntax error about incorrect syntax near AS

Share via

Syntax error in the statement

4 additional answers

Changes Made:

Your answer