October 9, 2013 at 11:09 pm
Comments posted to this topic are about the item copy/paste multiple columns
October 10, 2013 at 4:42 am
I like it -> 5 stars from me. Nice idea, and easily extensible. I'm not sure I'd have gone for the dynamic sql...I'd have left it to the user to go to the right database. But I understand the reasons for this choice. The disadvantage is that it makes it slightly more fiddly to extend.
October 10, 2013 at 9:11 am
Very helpfully, especialy with the shurtcut.
WHY do you have posted this not 5 years ago!!
BUT I always get an Error when I marked for example schema.tablename and press the short cut.
I have a german system but the Error ist like Syntax Error near by ".".
In German:
Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von '.'.
October 10, 2013 at 9:39 am
Micha-1016108 (10/10/2013)
Very helpfully, especialy with the shurtcut.WHY do you have posted this not 5 years ago!!
BUT I always get an Error when I marked for example schema.tablename and press the short cut.
I have a german system but the Error ist like Syntax Error near by ".".
In German:
Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von '.'.
It's because it's a string parameter. If you put quotes around it, it will work OK (or if you leave out the schema.)
So you can put
PORTFOLIO
or
'dbo.PORTFOLIO'
October 10, 2013 at 12:37 pm
Micha-1016108 (10/10/2013)
Very helpfully, especialy with the shurtcut.WHY do you have posted this not 5 years ago!!
Thanks! Next time I will do my best to think about these things 5 years earlier 😀
This little script has been born out of years of frustration about not being able to (easily) select multiple columns. I was actually a bit disappointed myself that I did not think about this earlier.
October 10, 2013 at 4:25 pm
Superb. Love it. I added a couple of columns because i'm constantly doing JOINs and PROCEDUREs.
EXEC(
'SELECT b.Name AS ColumnStart,
'','' + b.Name AS ColumnNext,
''['' + b.Name + '']'' AS ColumnStartBr,
'',['' + b.Name + '']'' AS ColumnNextBr,
''??.'' + b.Name AS ColumnJoin,
'', ??.['' + b.Name + '']'' AS ColumnJoinBr,
'''' + UPPER(t.name) + CASE t.name WHEN ''varchar'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''nvarchar'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''binary'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''varbinary'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''sysname'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''text'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''ntext'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''char'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''sql_variant'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '')''
WHEN ''decimal'' THEN ''('' + CAST(b.max_length AS VARCHAR(5)) + '','' + CAST(b.precision AS VARCHAR(5))+ '')''
ELSE '''' END AS [Declare]
FROM ' + @Database + '.sys.objects a
INNER JOIN ' + @Database + '.sys.columns b ON a.object_id=b.object_id
INNER JOIN ' + @Database + '.sys.types t ON b.system_type_id = t.system_type_id
INNER JOIN ' + @Database + '.sys.schemas d ON a.schema_id=d.schema_id
WHERE a.Object_ID = OBJECT_ID(''' + @Database + '.' + @Schema + '.' + @Object + ''')
AND d.name = ''' + @Schema + '''
'
)
I use the "??" as a simple tag for aliases in joins.
October 11, 2013 at 10:33 am
How do you "Put the stored procedure under a shortcut. "?
October 11, 2013 at 11:55 am
nifty.
Is there any way to structure the shortcut so you don't have to wrap the parameter in ' ', so I could just highlight my database.schema.objectname and execute?
October 11, 2013 at 1:56 pm
bgrossnickle (10/11/2013)
How do you "Put the stored procedure under a shortcut. "?
After you installed the script:
1. Open SSMS
2. Select Tools | Options | Keyboard | Query Shortcuts
3. Choose any shortcut and type sp_ColumnSelect on the stored procedure column on the right and then press enter.
October 14, 2013 at 3:26 pm
I really like the multi-column copy/paste trick you came up with in the results. Also, the tempdb integration is very simple.
I have a similar script that I have begun modifying again based on what I liked in yours. Thank you for sharing! Maybe I'll put mine up some time, too.
October 15, 2013 at 1:03 am
liver.larson (10/11/2013)
nifty.Is there any way to structure the shortcut so you don't have to wrap the parameter in ' ', so I could just highlight my database.schema.objectname and execute?
Unfortunately not. This is the same if you use any of the built-in shortcuts such as sp_help (Alt-F1).
The 'error' occurs before the stored procedure is called i.e. it tries to pass what it considers to be an invalid parameter into the stored procedure. When you put the quotes, it is recognised as a string.
October 15, 2013 at 2:36 pm
that's unfortunate. Thanks for replying. I've been wondering about that for a while, but didn't find any threads anywhere on the shortcuts. cheers
October 16, 2013 at 12:58 am
liver.larson (10/15/2013)
that's unfortunate. Thanks for replying. I've been wondering about that for a while, but didn't find any threads anywhere on the shortcuts. cheers
You're welcome. As an additional demonstration, in any query window, try the following 2 sqls.
sp_help syscolumns -- works fine without the quotes, even though sp_help takes an nvarchar parameter.
sp_help sys.syscolumns -- this gives "Incorrect syntax near '.'."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply