Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Showing sp_executesql in SSMS Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 5:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
Hello Everyone
I have installed SQL 2012 Standard Edition. Seems ok. I also have a SQL 2008 Standard Edition on another instance. When I open any of the sprocs in SSMS 2012, I see there are some rather strange additions to the code I have written. Why is there code that uses "sp_executesql"? And is there a way to turn this off, and simply let SQL Server execute the query that is in the sproc? All of the code within the quotes is now in red letters. Very difficult to read and even more, to modify.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SEL_StateName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[SEL_StateName]

AS
...........

Please tell me there is a way to turn this off, or to show simply the Create or Alter Procedure statement.

Thank you in advance for all your assistance, suggestions and samples.

Andrew SQLDBA
Post #1467409
Posted Tuesday, June 25, 2013 6:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:53 PM
Points: 1,785, Visits: 5,676
Here you go:



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw



  •   Post Attachments 
    Nicer Scripting.PNG (60 views, 32.83 KB)
    Post #1467412
    Posted Wednesday, June 26, 2013 4:14 AM


    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Thursday, July 17, 2014 3:19 PM
    Points: 975, Visits: 3,342
    Thanks Mister.Magoo
    That is it. Perfect.

    I feel silly now, I looked in other places in the options, but not there.

    Thanks so very much

    Andrew SQLDBA
    Post #1467545
    Posted Wednesday, June 26, 2013 4:47 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:53 PM
    Points: 1,785, Visits: 5,676
    AndrewSQLDBA (6/26/2013)
    Thanks Mister.Magoo
    That is it. Perfect.

    I feel silly now, I looked in other places in the options, but not there.

    Thanks so very much

    Andrew SQLDBA


    No worries, now if you can find a way to make dragging the "Columns" node from Object Explorer put [ brackets ] around the column names, that would help me


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1467555
    Posted Wednesday, June 26, 2013 5:38 AM


    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Thursday, July 17, 2014 3:19 PM
    Points: 975, Visits: 3,342
    You know, I Never use brackets, unless some fool used a SQL reserved word, or placed a blank space in the object name.

    Andrew SQLDBA
    Post #1467585
    Posted Wednesday, June 26, 2013 5:58 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:53 PM
    Points: 1,785, Visits: 5,676
    I know a lot of fools.

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1467603
    Posted Wednesday, June 26, 2013 6:19 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 8:24 AM
    Points: 12,887, Visits: 31,835
    mister magoo i have a kinda-sorta solution i use for that issue; instead of a drag and drop, i have a keyboard shortcut in SSMS that i call on any highlighted text (so it assumes a table)

    i created this simple proc and mark it as a system proc in master:

    CREATE PROCEDURE sp_colz @Tablename sysname
    AS
    SELECT DISTINCT
    t.name,
    sq.Columns
    FROM sys.tables t
    JOIN (
    SELECT OBJECT_ID,
    Columns = STUFF((SELECT ',' + quotename(name)
    FROM sys.columns sc
    WHERE sc.object_id = s.object_id
    FOR XML PATH('')),1,1,'')
    FROM sys.columns s
    ) sq ON t.object_id = sq.object_id
    WHERE t.name =@Tablename
    GO
    --mark it as a system object so it functions against the currently-scoped-databases sys.tables/sys.columns
    EXECUTE sp_ms_marksystemobject 'sp_colz'


    then i add a keyboard shortcut:


    from there, if i was typing a query, and needed the column names, i highlight the tablename, hit control+8(in my case)
    and i get the results below for a fast copy/paste.



    you can see I have similar shortcuts for lots of other functions, and in total they all make me immensely more productive for things commonly looked up things like this.



    Lowell

    --There is no spoon, and there's no default ORDER BY in sql server either.
    Actually, Common Sense is so rare, it should be considered a Superpower. --my son
    Post #1467619
    Posted Wednesday, June 26, 2013 7:24 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:53 PM
    Points: 1,785, Visits: 5,676
    Hi Lowell,

    Thanks for that - it is a reasonable work-around until those spoon-heads fix SSMS back to how it used to work...

    Unfortunately, I can't really make use of that as I am a consultant and installing this as a system procedure on someone else's installation would be frowned upon.

    I like it though


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1467640
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse