copy/paste multiple columns

  • Comments posted to this topic are about the item copy/paste multiple columns

  • 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.

  • 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 '.'.

  • 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'

  • 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.

  • 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.

  • How do you "Put the stored procedure under a shortcut. "?

  • 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?

  • 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.

  • 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.

  • 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.

  • 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

  • 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