Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


copy/paste multiple columns


copy/paste multiple columns

Author
Message
r.v.schaik
r.v.schaik
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 93
Comments posted to this topic are about the item copy/paste multiple columns
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1089 Visits: 2090
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.
Micha-1016108
Micha-1016108
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 378
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 '.'.
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1089 Visits: 2090
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'
r.v.schaik
r.v.schaik
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 93
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 :-D
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.
Dave Vroman
Dave Vroman
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 722
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.
bgrossnickle
bgrossnickle
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 344
How do you "Put the stored procedure under a shortcut. "?
liver.larson
liver.larson
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 115
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?
DaniSQL
DaniSQL
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 938
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.
Greg Drake
Greg Drake
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 534
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search