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 09, 2013 11:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 1:25 AM
Points: 4, Visits: 88
Comments posted to this topic are about the item copy/paste multiple columns
Post #1503369
Posted Thursday, October 10, 2013 4:42 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 641, Visits: 1,794
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: Tuesday, February 11, 2014 6:35 AM
Points: 9, Visits: 370
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 641, Visits: 1,794
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, February 14, 2014 1:25 AM
Points: 4, Visits: 88
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: Yesterday @ 5:57 PM
Points: 146, Visits: 494
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: Tuesday, March 11, 2014 7:10 AM
Points: 12, Visits: 65
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: 2 days ago @ 9:50 AM
Points: 32, Visits: 53
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: Thursday, April 10, 2014 12:39 PM
Points: 115, Visits: 782
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 381, Visits: 411
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