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 12»»

copy/paste multiple columns Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 11:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 5, 2015 12:40 AM
Points: 4, Visits: 92
Comments posted to this topic are about the item copy/paste multiple columns
Post #1503369
Posted Thursday, October 10, 2013 4:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, July 20, 2015 4:47 AM
Points: 665, Visits: 1,945
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.
Post #1503459
Posted Thursday, October 10, 2013 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 8, 2015 7:46 AM
Points: 9, Visits: 374
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 '.'.
Post #1503645
Posted Thursday, October 10, 2013 9:39 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, July 20, 2015 4:47 AM
Points: 665, Visits: 1,945
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'
Post #1503670
Posted Thursday, October 10, 2013 12:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 5, 2015 12:40 AM
Points: 4, Visits: 92
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.
Post #1503741
Posted Thursday, October 10, 2013 4:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 23, 2015 11:53 PM
Points: 151, Visits: 703
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.
Post #1503821
Posted Friday, October 11, 2013 10:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 26, 2015 8:30 PM
Points: 17, Visits: 227
How do you "Put the stored procedure under a shortcut. "?

Post #1504085
Posted Friday, October 11, 2013 11:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 29, 2015 10:56 AM
Points: 33, Visits: 76
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?
Post #1504112
Posted Friday, October 11, 2013 1:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 26, 2015 2:47 PM
Points: 116, Visits: 908
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.
Post #1504141
Posted Monday, October 14, 2013 3:26 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 25, 2015 9:39 AM
Points: 477, Visits: 501
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.
Post #1504557
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse