CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Water Cooler v2
Water Cooler v2

Reputation: 33880

Print a row number or counter along with each row

I'd like to print the names of all the tables in my database but with a row number like so:

S.No.  Table Name
-------------------
1.     Products
2.     Orders
3.     OrderDetails
...

I tried the following but that gives me the total number of rows.

-- USE [MyDatabase]; -- implied

SELECT @@ROWCOUNT AS [S.No.], 
       Name as [Table Name] 
FROM Sys.Tables;

I figure I have to do something like:

@Counter int;
SELECT (SELECT ++@Counter AS [S.No.]), 
       Name AS [Table Name]
FROM Sys.Tables;

But I'm not quite sure of the exact syntax. Could you please help me with that?

I am using Microsoft SQL Server 2014.

Upvotes: 1

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use row_number():

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as [S.No.], 
       Name as [Table Name] 
FROM Sys.Tables;

Upvotes: 3

Related Questions