SQL Server Performance

Line of codes

Discussion in 'Third Party Tools' started by SQL2000DBA, Sep 27, 2005.

  1. SQL2000DBA New Member

    Hi,

    Do you know any tool which can prvoide me line of codes written in stored procedure. I have collect how many line of codes is written for stored procedure in my DB.

    Thanks and Regards

    Ravi K
  2. satya Moderator

  3. FrankKalis Moderator

    Also, check out the usual tools in the software section. Toad for SQL Server by<a target="_blank" href=http://www.quest.com>http://www.quest.com</a> and ApexSQLEdit can do this, too. And Toad is freeware. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  4. SQL2000DBA New Member

    Hello FrankKalis,

    I have downloaded TOAD for SQL server but i am not able find the option of genrating lines of codes for all stored procedure in my DB.

    Please let me know if you know the option.

    Thanks and Regards

    Ravi K

  5. Madhivanan Moderator

    See if this helps you

    Create table #t(sptext varchar(8000))
    Insert into #t EXEC sp_helptext 'yoursp'
    Select count(*) as SPLines from #t
    Drop table #t


    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    Madhivanan, this isn't reliable. Potentially I can write a stored procedure on one single line and this would fool your solution. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Ravi, I must admit I've misunderstood your question. I thought you were after some tool that will show you the line number while you are typing. Now after re-reading I see what you want to do. Probably the easiest solution is to script out all your SP's in one file and open it with a text editor of your choice that can count line, such as UltraEdit. However, the same "disadvantage" is valid here. Consider this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />USE tempdb <br />GO<br />CREATE PROCEDURE dbo.test<br />AS<br />SELECT<br />*<br />FROM<br />sysobjects<br />GO<br /><br />Create table #t(sptext varchar(8000))<br />Insert into #t EXEC sp_helptext 'dbo.test'<br />Select count(*) as SPLines from #t<br />Drop table #t<br />DROP PROCEDURE dbo.test<br />GO<br />CREATE PROCEDURE dbo.test AS SELECT * FROM sysobjects<br />GO<br />Create table #t(sptext varchar(8000))<br />Insert into #t EXEC sp_helptext 'dbo.test'<br />Select count(*) as SPLines from #t<br />Drop table #t<br />DROP PROCEDURE dbo.test<br /><br />SPLines <br />----------- <br />6<br /><br />(1 row(s) affected)<br /><br /><br /><br />SPLines <br />----------- <br />1<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />It's not reliable. What do you need this for?<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  7. Madhivanan Moderator

    I think if you include new line charater, then it should be counted<br />So your example is valid [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  8. FrankKalis Moderator

    Yes, each new line character is counted. But you don't need to include one to have a valid procedure. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page