Display Line Numbers in a SQL Server Management Studio Query Window

By:   |   Updated: 2021-10-04   |   Comments (35)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Management Studio Configuration


Problem

Often we see an error message from SQL Server which references a particular line number within our T-SQL code. It is a little frustrating to find the line number where the syntax error has occurred. In this tip we will take a look at how to quickly enable line the numbers feature in SQL Server Management Studio.

Solution

Let us see how to enable or disable the display of line numbers in SQL Server Management Studio's Query Window.

1. Open SQL Server Management Studio. Click Tools > Options from the drop down menu as shown in the below to open up Options dialog box.

Tools and Options menu in SQL Server Management Studio

2. In the Options dialog box on the left side panel expand the Text Editor option and expand Transact-SQL. Next select General as shown in the snippet below.

Options Dialog Box in SSMS

3. In the General page's right side panel you need to select the check box "Line numbers" as shown in the above snippet and click OK to save the changes. Note: in new versions of SSMS the screen looks a little different from above.

4. Going forward whenever you open a new query window in SQL Server Management Studio you will see line numbers displayed. In the below snippet you will see that line numbers are displayed in the sample T-SQL code. This feature is very helpful when you need to debug an error in your T-SQL code, especially in scenarios when SQL Server mentions that error is found on a particular line number.

SSMS Query Window Showing Line Numbers

5. If this feature is not turned and you need to go to a particular line number press CTRL + G to open the Go To Line dialog box, enter line number and click OK as shown below.

Specify Value of the line number in SQL Server Management Studio

6. To turn off the line number feature in SQL Server Management Studio you need to navigate to Tools > Options > Text Editor > Transact-SQL > General and uncheck the Line Numbers check box and click the OK button to save the changes so that next time you open a new query window in SSMS line numbers will not be displayed.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-10-04

Comments For This Article




Friday, October 11, 2024 - 4:44:34 AM - Ali Bayramoglu Back To Top (92561)
Thanks for help.

Thursday, September 28, 2023 - 7:23:57 AM - spart Back To Top (91605)
great advice, thanks

Friday, February 17, 2023 - 9:45:05 AM - Vinay R Back To Top (90935)
Thanks

Thursday, February 16, 2023 - 1:56:39 PM - Alfredo Back To Top (90934)
Thanks!!

Wednesday, February 2, 2022 - 6:34:11 PM - A. Daniel Macedo Back To Top (89727)
Thanks!... You saved me... !!!

Thursday, November 14, 2019 - 12:44:54 PM - pepe Back To Top (83092)

Thanks!

Tuesday, July 10, 2018 - 9:46:57 PM - Nithya Back To Top (76607)

 

 Thanks for the effort of step by step explaining.

Friday, July 28, 2017 - 4:58:40 PM - Roy J Back To Top (63511)

 In SSMS v17.1 which I have installed, the Line Number option is found in a different place; Options | Text Editor | Transact-SQL | Settings

Thursday, March 2, 2017 - 12:35:52 PM - Roman Back To Top (46971)

 Thanks for the very easy too follow explanation!

 

Thursday, November 5, 2015 - 4:20:22 PM - Tim Harms Back To Top (39029)

All you have to do to find an error by line number is double click on the error message and SSMS takes you directly to it. Test it out and see for yourself.

SELECT

 

 

1/0

 

 

Msg 8134

 

,Level 16,State 1, Line 1

 

 

Divide

 

by zero error encountered.

 

 

 

 

 

Friday, July 31, 2015 - 6:41:13 AM - bandu Back To Top (38333)

not working

Wednesday, July 8, 2015 - 9:17:38 AM - benicio Back To Top (38150)

Very helpful, thanks. If I could give you a tip I would :)

Monday, February 23, 2015 - 12:41:14 AM - santhosh Back To Top (36309)

thank you...

Wednesday, January 14, 2015 - 1:02:36 AM - vikrant thakkar Back To Top (35929)

It is fine


Wednesday, August 20, 2014 - 9:38:57 AM - Murray Back To Top (34205)

This is great!  Thanks so much.  Also huge thanks to Fatherjack for his tip as well!

 

Monday, May 19, 2014 - 6:55:48 AM - babu Back To Top (30835)

I Love MSSQLTips as they bringing something new every day for every learner.

Monday, May 19, 2014 - 6:53:37 AM - Ravi Back To Top (30834)

Thank you

Monday, May 19, 2014 - 6:52:46 AM - babu Back To Top (30833)

 

Thanks, chaps, that's a very useful one.

Thursday, April 24, 2014 - 3:20:33 AM - Babłke Back To Top (30491)

Thank you

Friday, October 25, 2013 - 12:15:37 PM - Shelly Back To Top (27283)

Well, I'm a "seasoned beginner" and I did not know how to turn on the line count, nor about the double-click error jump.  I like having some basic tips and they are not only for beginners.  If I do not consistently use a tool, I will forget about it.  Then when I need it, I can't remember how to get to it.  Thanks for sharing and keep the basic reminders coming. 

Saturday, October 12, 2013 - 1:54:26 AM - Arnor Baldvinsson Back To Top (27129)

Thanks for this tip!  I'm not new to SSMS, but I haven't written much SQL code in it - done that in external tools.  A quick google searched showed this article.  Simple when you know how to;)

Best regards,

Monday, May 20, 2013 - 8:41:00 AM - Josetta Back To Top (24044)

Thanks for the tip. I'm not an absolute beginner, but I'd have spent way too much time looking for this myself. Also, handy to know that double-clicking the error takes me right to the error. But I DO know how to turn my computer on, so no need for a tip on that. :)

Thanks again!!

Tuesday, August 7, 2012 - 12:48:48 PM - Gene Wirchenko Back To Top (18943)
Zen: Yes, it was a tip, and it was useful to me. If you did not need it, fine, but that does not mean that no one else did.

Tuesday, August 7, 2012 - 9:10:00 AM - Dom Back To Top (18941)

If you have more than one script in a query window, which I do often enough, then it is frustrating to find the the error message gives you a line number *within the specific script*.  That is, if the error message complains about line 17, it is not really line 17, it is the 17th line withing the scipt.  In that case, double-clicking the error message is really the only way to find the offending line.

Tuesday, July 24, 2012 - 8:38:29 AM - Jeremy Kadlec Back To Top (18773)

Zen,

Please send me a direct email ([email protected]).  I would like to hear your feedback.

Thank you,
Jeremy Kadlec

Tuesday, July 24, 2012 - 6:12:15 AM - Miki Breakwell Back To Top (18768)

Thanks, chaps, that's a very useful one.

 

Monday, July 23, 2012 - 9:10:10 PM - Bill Fennell Back To Top (18757)

Yeah, you never know who's reading a tip, so it's good to include newbies where possible.  Don't forget... we were also newbies at one point.

Another cool thing in SSMS around line numbers is if you double-click an error message in the results pane, it usually takes you to the line that has the error!

=B-)

Monday, July 23, 2012 - 11:08:31 AM - Daniel Back To Top (18750)

Wow! Thanks MSSQLTips for this quick tip.

I had seen Line Numbers display in SSMS while attending few webcasts and always wondered how to get this feature enabled as this is so useful in day-to-day work.

Thanks for pointing out how to enable this.

I Love MSSQLTips as they bringing something new every day for every learner.

Monday, July 23, 2012 - 11:00:13 AM - TimothyAWiseman Back To Top (18749)

@Zen There is room for tips that are targetted at the absolute beginners as well.  We were all beginners once and I see nothing wrong with trying to help them at. 

Monday, July 23, 2012 - 10:42:49 AM - [email protected] Back To Top (18748)

Wow....this really helps! I am new to SQL Server. You a life saver!!!!

Monday, July 23, 2012 - 9:10:37 AM - Greg Robidoux Back To Top (18747)

@Zen - thanks for your feedback.  The thing to keep in mind is that the site has various levels of people using SQL Server.  Some things that may be very obvious to some are not so obvious to others such as this tip. 

Thanks again for your feedback and we'll try to balance the tip topics the best we can.

Monday, July 23, 2012 - 9:04:45 AM - ZEN Back To Top (18746)

 

How to display line # in SSMS?   huh... Are you kidding me? I am not saying to the writer.  I am saying to Jeremy.  Could next MSSQL TIPS be how to turn on your computer???

 

We love this website because of it's contents and this is really not TIPS at all. 

Monday, July 23, 2012 - 8:56:27 AM - Greg Robidoux Back To Top (18745)

@fatherjack - thanks for pointint that out.  We actually have a tip on doing just that: http://www.mssqltips.com/sqlservertip/1101/finding-sql-server-code-errors-using-query-analyzer/

Monday, July 23, 2012 - 8:38:35 AM - Sanjeev Viswanath Back To Top (18744)

Dear All,

I have enabled the deadlock trace flag 1222 on my server . But I am not able to interpret the error logs after  deadlock occurs on it .

Can anyone help me on this.

Below is the sample error log after deadlock occured. Any help is appreciated.

#################

<deadlock>

  <victim-list>

    <victimProcess id="process1ac25288" />

  </victim-list>

  <process-list>

    <process id="process1ac25288" taskpriority="0" logused="52264" waitresource="OBJECT: 7:1618156860:41 " waittime="19938" ownerId="889665194" transactionname="user_transaction" lasttranstarted="2012-07-13T03:30:00.233" XDES="0x23602ec3b0" lockMode="IX" schedulerid="42" kpid="14536" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-13T03:30:00.233" lastbatchcompleted="2012-07-13T03:30:00.233" clientapp="crmnext" hostname="HBSFATDB" hostpid="18360" loginname="HBSFATDB\casper" isolationlevel="read committed (2)" xactid="889665194" currentdb="7" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">

      <executionStack>

        <frame procname="" line="30" stmtstart="1954" sqlhandle="0x030007002d78ce07332a880088a000000000000000000000" />

        <frame procname="" line="14" stmtstart="1628" stmtend="3242" sqlhandle="0x03000700e40d6902d593960088a000000100000000000000" />

      </executionStack>

      <inputbuf>

Proc [Database Id = 7 Object Id = 40439268]   </inputbuf>

    </process>

    <process id="process1c688748" taskpriority="0" logused="335974140" waitresource="KEY: 7:72057597604724736 (855f87e921e1)" waittime="2172" ownerId="889663938" transactionname="user_transaction" lasttranstarted="2012-07-13T03:29:58.467" XDES="0x3e91cf7950" lockMode="U" schedulerid="112" kpid="21260" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-13T03:29:58.467" lastbatchcompleted="2012-07-13T03:29:58.467" clientapp="crmnext" hostname="HBCRMPRDAP1" hostpid="22212" loginname="HBSFATDB\casper" isolationlevel="read committed (2)" xactid="889663938" currentdb="7" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">

      <executionStack>

        <frame procname="" line="14" stmtstart="760" stmtend="1100" sqlhandle="0x030007007d8ec66afb2b880088a000000000000000000000" />

        <frame procname="" line="1" stmtstart="74" sqlhandle="0x02000000eac2ff2d89337e6bd2413fabc1a8bf7e1057da80" />

        <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />

      </executionStack>

      <inputbuf>

(@maxtotalattempts int,@statusid int)DELETE FROM ClusteredOperationControl   WHERE TotalAttempts &gt; @maxtotalattempts OR StatusID in( @statusid,5)   </inputbuf>

    </process>

  </process-list>

  <resource-list>

    <objectlock lockPartition="41" objid="1618156860" subresource="FULL" dbid="7" objectname="" id="lock410e287900" mode="X" associatedObjectId="1618156860">

      <owner-list>

        <owner id="process1c688748" mode="X" />

      </owner-list>

      <waiter-list>

        <waiter id="process1ac25288" mode="IX" requestType="wait" />

      </waiter-list>

    </objectlock>

    <keylock hobtid="72057597604724736" dbid="7" objectname="" indexname="" id="lock4b239d5200" mode="X" associatedObjectId="72057597604724736">

      <owner-list>

        <owner id="process1ac25288" mode="X" />

      </owner-list>

      <waiter-list>

        <waiter id="process1c688748" mode="U" requestType="wait" />

      </waiter-list>

    </keylock>

  </resource-list>

</deadlock>

###########3

Regards

Sanjeev Viswanath

Monday, July 23, 2012 - 7:52:37 AM - fatherjack Back To Top (18743)

Did you know that you can double click the error message and SSMS jumps you to the start of the offending piece of TSQL too?














get free sql tips
agree to terms