Query Showing sp_executesql in SSMS

Question

Post reply

Query Showing sp_executesql in SSMS

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
June 25, 2013 at 5:40 pm

#276650

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
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Here you go:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

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 :w00t:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

I know a lot of fools.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Lowell SSC Guru Points: 323518 More actions · Answer 6

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

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

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);