Post reply

Remove Column Header "text" from sp_helptext

  • March 14, 2009 at 4:27 am

    #129087

    I want to avoid generating Column Header "Text" when sp_helptext is used.

    This is done in Text Mode of QueryAnalyser.

    If there is way , suggest a way which can do this.

  • March 14, 2009 at 4:50 am

    #959666

    I guess there is an option in QA as well as in SSMS to enable/disable column headers display in results window.

    --Ramesh

  • March 16, 2009 at 5:22 am

    #960121

    thanks for the reply,

    i am aware of the option, wanted to know if we can do the same via programmatically so that we do not have to toggle on/off the setting.

    some query or script.

  • March 16, 2009 at 5:48 am

    #960136

    Hi

    Try this

    [font="Courier New"]

    SET NOCOUNT ON

    DECLARE @definition TABLE (id INT IDENTITY, line NVARCHAR(MAX))

    INSERT INTO @definition

       EXECUTE sp_helptext 'sp_test'

    DECLARE @id INT

    DECLARE @line NVARCHAR(MAX)

    WHILE EXISTS (SELECT TOP 1 * FROM @definition)

    BEGIN

       SELECT TOP(1) @id = id, @line = line FROM @definition ORDER BY id

      

       PRINT @line

      

       DELETE FROM @definition WHERE id = @id

    END[/font]

    Greets

    Flo

  • March 16, 2009 at 8:00 am

    #960237

    excellent workaround to overcome a problem.

    thanks very much,

    However one thing, as i said from the previos post, we can change it manually from the Query Options by checking/unchecking the

    "Include Column Headers in the ResultSet".

    Is there a way we can do this or change any Query Options via sql script.

  • March 16, 2009 at 8:02 am

    #960241

    Hi

    No that's not possible via SQL. The SSMS is only a client which transports information between you and your SQL Server. The SQL will not be executed in SSMS; only in SQL Server Engine.

    Greets

    Flo

  • March 16, 2009 at 8:21 am

    #960261

    Ok, That's very kind of you to share the solution and information.

    I will be shortly sharing the uitlity script which i was preparing and this thing was coming in way.

  • March 17, 2009 at 1:23 pm

    #961336

    yet another method:

    SQLCMD -H-1

    1> exec sp_helptext sp_procname

    2> go

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 17, 2009 at 2:13 pm

    #961374

    Off topic

    Wow... I'm currently starring at my previous posting.

    SQL scripts look quiet funny if created with SQLPrettifyer since forum update this today. 😀

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply