CODEWITHSUNDEEP
sqlsql-server
zenrumi
zenrumi

Reputation: 115

Subquery syntax error

I'm not entirely sure why this doesn't work. I'm sure its very basic SQL and I'm missing something somewhere. 

SELECT COUNT(*)
FROM (SELECT MIN(sys.tables.name)
        FROM sys.tables)

I'm getting this error Message::

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.

The correct output should be just one number, the number of rows in the first table in the current database.

Upvotes: 1

Views: 60

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

To get the number of rows in the first table in the current database you can write a query as:

 SELECT SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 WHERE ta.name =(SELECT MIN(T.name) FROM sys.tables T) 
 GROUP BY ta.name

Upvotes: 0

roman
roman

Reputation: 117345

To get your query working, just add alias after last ) and alias name for column:

SELECT COUNT(*)
FROM (SELECT MIN(sys.tables.name) as name FROM sys.tables) as A

But this one will not return you number of rows in the first table in the current database. It will just return 1, because there's one record in this subquery. To get number of rows in the first table in the current database you have to use dynamic SQL, something like :

declare @Table_Name nvarchar(128), @stmt nvarchar(max)

select @Table_Name = min(name) from sys.tables

select @stmt = 'select ''' + @Table_Name + ''' as Table_Name, count(*) from ' + @Table_Name

exec sp_executesql @stmt = @stmt

Upvotes: 2

Related Questions