locked
SSDT Power Tools - Feature Requests RRS feed

  • Question

  • Trying to post comments to the blog about the new release of the power tools but didn't seem to work. So I thought I'd try here instead.

    http://visualstudiogallery.msdn.microsoft.com/9b0228c6-15d1-44de-9279-66dde12bf861?SRC=Featured

    This is awesome and with the new functionality I will now start actually using this in real life.

    A few things that leap out at me at first glance

    1. It seems the Script-As functionality is not available for tables under the Projects superfolder (only in SQL Server). Any chance of this being extended to this as this would be extremely useful for rapid development.
    2. I am publishing a dacpac with a bunch of generic sql sp udfs etc that developers can include as a same database, same server dependency. Any chance that SQL Server Object Explorer could allow adding dacpac's for browsiing the objects?
    3. Would be good if there was an option to hide folders with no objects in (and perhaps an indicator/count of how many objects are in each) - save on clicking and aid browsing an unfamiliar project.
    4. msdb/master objects don't seem to be considered built-in objects? With a project including msdb and removing show built in objects, I still get all the standard msdb. Perhaps the ability to control this per dependency would be good (or even have another filter that split per database reference alongside split by schema). Often I would like to view non-system objects but not master / msdb.

    Perhaps this would solve my point 2 if solved in certain ways?
    5. A way to have the object in Object Explorer automatically/manually selected upon the current file, or by context menu click (kind-of the reverse of showing in solution explorer).


    Tuesday, June 12, 2012 10:52 AM

Answers

  • Thanks for your great feedback guys and discussion. I will keep this thread around for our next releases.
    • Proposed as answer by Sam Hughes Monday, June 18, 2012 5:18 PM
    • Marked as answer by Janet Yeilding Monday, June 18, 2012 7:23 PM
    Monday, June 18, 2012 5:15 PM

All replies

  • Hi Brett,

    I think they take suggestions in the QnA section of http://visualstudiogallery.msdn.microsoft.com/9b0228c6-15d1-44de-9279-66dde12bf861?SRC=Featured

    Trying to post comments to the blog about the new release of the power tools but didn't seem to work. So I thought I'd try here instead.

    http://visualstudiogallery.msdn.microsoft.com/9b0228c6-15d1-44de-9279-66dde12bf861?SRC=Featured

    This is awesome and with the new functionality I will now start actually using this in real life.

    A few things that leap out at me at first glance

    1. It seems the Script-As functionality is not available for tables under the Projects superfolder (only in SQL Server). Any chance of this being extended to this as this would be extremely useful for rapid development.

    Not sure I quite understand this one. Anything under Projects is only there because it already exists as a script - double-clicking on the object will open up that script. Have I misunderstood?

    2. I am publishing a dacpac with a bunch of generic sql sp udfs etc that developers can include as a same database, same server dependency. Any chance that SQL Server Object Explorer could allow adding dacpac's for browsiing the objects?
    You mean you want to view the contents of a dacpac seperate from any project in which it is referenced? Not a bad idea that, not a bad idea at all.

    3. Would be good if there was an option to hide folders with no objects in (and perhaps an indicator/count of how many objects are in each) - save on clicking and aid browsing an unfamiliar project.
    Yes yes yes. Absolutely, great idea. I second this one - particularly optional hiding of folders.

    4. msdb/master objects don't seem to be considered built-in objects? With a project including msdb and removing show built in objects, I still get all the standard msdb. Perhaps the ability to control this per dependency would be good (or even have another filter that split per database reference alongside split by schema). Often I would like to view non-system objects but not master / msdb.

    Perhaps this would solve my point 2 if solved in certain ways?

    Can see why this might be useful.
    5. A way to have the object in Object Explorer automatically/manually selected upon the current file, or by context menu click (kind-of the reverse of showing in solution explorer).


    If that were optional, sure, that'd get my vote.


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    • Proposed as answer by Sam Hughes Wednesday, June 13, 2012 7:32 PM
    • Unproposed as answer by Sam Hughes Wednesday, June 13, 2012 7:44 PM
    Tuesday, June 12, 2012 12:04 PM
  • 1. It seems the Script-As functionality is not available for tables under the Projects superfolder (only in SQL Server). Any chance of this being extended to this as this would be extremely useful for rapid development.

    Not sure I quite understand this one. Anything under Projects is only there because it already exists as a script - double-clicking on the object will open up that script. Have I misunderstood?

    - No but you may know more here than I noticed  - I was hoping for DML (insert, update delete_ scripts for tables.. but it may be that these aren't available with the Script As... now. If this is the case then that is my request! Script DML - both servers and for current project!

    Thanks for the comments Jamie

    Tuesday, June 12, 2012 12:56 PM
  • 1. It seems the Script-As functionality is not available for tables under the Projects superfolder (only in SQL Server). Any chance of this being extended to this as this would be extremely useful for rapid development.

    Not sure I quite understand this one. Anything under Projects is only there because it already exists as a script - double-clicking on the object will open up that script. Have I misunderstood?

    - No but you may know more here than I noticed  - I was hoping for DML (insert, update delete_ scripts for tables.. but it may be that these aren't available with the Script As... now. If this is the case then that is my request! Script DML - both servers and for current project!


    Aha, I *did* misunderstand :) Yes, that would be useful. I think there are tonnes of stuff they could do around DML generation. For example, why can't I right-click on a table inside a script and say "generate an INSERT" for me. Why can't I replace a wildcard (i.e. *) with a column list. There's a massive scope for improvements here IMO.


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Tuesday, June 12, 2012 1:20 PM
  • Now we digress but you can replace a wildcard with a column list using the standard Refactor SSDT tools. Find a script with * in and use "Refactor \ Expand Wildcards...". With quick use of a regex find/replace (", " -> "\n,\t") it then puts it in correct format for me - job done.
    Tuesday, June 12, 2012 1:26 PM
  • Hi Guys, 

    1) Jamie answered 90% of it right off the bat, but to answer your question about select, insert, update, and delete scripts, those are on my radar for sure. I sure appreciate the conversation and suggestions.

    2) Yes, it's on the list, but a ways down there.

    3/4) We kicked around several ideas on this. I think we have a very cool idea for filtering that I'm looking forward to trying out on you guys. The count is a little tricky since we lazy load the tree... You could end up spinning a lot of connections up to do that.  Perhaps an option to pre-populate portions of a tree? or Project only counts?

    5) We didn't do this because there's a wrinkle... what are your thoughts on which node to select  when there are multiple objects defined in a single file? We had a couple thoughts: a prioritized order that we pick things or just select the first object. Again... it's lazy loaded. Do you want to pay the toll to wait on the connected database? Or would a project-only experience be ok?

    Re: WildcardsOnce you have a script inside your project, you should be able to refactor/expand the wildcards. I understand that won't work for connected... yet, but I will keep the idea in mind for our next revision.


    • Edited by Sam Hughes Wednesday, June 13, 2012 7:45 PM
    Wednesday, June 13, 2012 7:43 PM
  • Hi Sam, thanks for the reply.

    1. Further to this just thought I'd add my frustrations with the current script as in ssms today. The way it formats is not the way that I would and it would be good to have some way to control the delimiting/template. To illustrate my preferred format is to block format:

    select 	
        blah
    ,   blah2
    ,   blah3
    from mytable
    
    insert mytable
    (    blah
    ,    blah2
    ,    blah3
    )
    select
         1
    ,    2
    ,    3
    
    update mytable
    set blah   = 1
        blah2  = 2
        blah3  = 3
    
    delete mytable
    where blah = 1
    

    By having them structured like this it clearly separates and aligns those parts that are the same and it means that it is easy to build insert/update statements from other tables etc. using the new block copy/paste functionality in VS 2010 (using the ALT button when selecting).

    Especially important is the "update" functionality to block justify the = signs to allow this to work, so I can choose one table to update then simply copy paste fields from, say, a view straight into the assignment side of the update.

    The other thing that I find myself doing is needing to just get a column list (again in the above format) that I can then copy paste in - so perhaps a "get column list to clipboard" for tables - where I can define the way to delimit in settings (e.g. \n,\t) would actually solve an awful lot of my use of scripts - I generally only use the select script for this and usually it is only for this feature. insert, update and delete both require significant additional work anyway from the skeleton. 

    3/4. Eager-loading definately to get the object counts definately for projects. I wonder if you could have eager-loading only when clicking on key root level folders (e.g. database, security) to prevent the connect to everything in your tree on startup. Of course it should be async so no blocking during eager-load.

    5. I suspected that this was the case when I realised it wasn't there. If prioritised this would need to be configurable and I think likely this would be too hard to bother with for most users. I think first object would be fine (although ignore sp_extendedproperty statements **- often these are at the top of my scripts for documentation) along with a context sensitive menu ability to then pick other specific symbols to navigate object explorer to within a script? Is this workable?

    **This is another good candidate for a feature - ability to add extended property template for an object, perhaps triggered by a context menu for an object?

    Thursday, June 14, 2012 9:01 AM
  • Personally I'm of the opinion that getting everyone to agree on a format is an exercise in futility; moreover, there is a trade-off between giving us lots of knobs to tweak it and simplicity. FWIW, here's how I like to structure stuff:

    INSERT INTO (
    		[col1]
    ,		[col2]
    ,		[col3]
    ,		[col4]
    )
    SELECT	t1.[col1]
    ,		t1.[col2]
    ,		t2.[col3]
    ,		t2.[col4]
    FROM	[schema].[table1]t1
    INNER JOIN [schema].[table2]t2
    	ON	t1.[col1]	=	t2.[col2]
    WHERE	t1.[col2]	=	@param1
    	AND	t2.[col5]	=	@param2
    ;

    One "knob" that I will campaign vigorously for is for is an option to wrap all object identifiers in square brackets. I hate the fact that drag-and-drop columns in SSMS today does not do that.

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson



    Thursday, June 14, 2012 9:35 AM
  • Agreed on the square bracket feature to go alongside the refactor fully-qualify all objects. Make that request #7! :)

    Interestingly your preferred format is the same as mine in all the important areas Jamie and I agree that sql isn't quite at c# levels of standardisation and appetite for change here is low in sql developers. I hope you have found the ALT block copy/paste vs2010 feature as this provides significant productivity benefits to this style!

    I do think that if we *expect* diversity then we should allow customisation to allow the tools to support us in that diversity. Definitely keep it simple though - this is why I would lean more towards the "delimited column list" from rowset returning objects rather than worrying too much about the dml script templating as that would cover most of my needs.

    It would be good if that customisation extended to the refactor - expand wildcard though.

    Thursday, June 14, 2012 9:59 AM
  • Interestingly your preferred format is the same as mine in all the important areas Jamie and I agree that sql isn't quite at c# levels of standardisation and appetite for change here is low in sql developers.

    That's one reason (of many many reasons) why I wish Microsoft would do what they did for app devs back in 1999 and start from a clean slate with a brand new language (one based on LINQ would be nice) rather than having to put up with this 30-year old bag of bolts. I know that this is never going to happen tho :)
    I hope you have found the ALT block copy/paste vs2010 feature as this provides significant productivity benefits to this style!

    I know about ALT block copy but am struggling to understand how it could be useful here. Sounds interesting tho - can you elaborate?

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 14, 2012 10:13 AM
  • Re Block Copy

    So my common use is for insert and update statements. You are generally referring from another table/view.

    If you use the "newline for each column" style in both types of statement you can quickly use script as insert, update and select tools to help you produce statements with copy and paste.

    This is fairly easy with "insert ... select" - as source/destination columns don't trespass on each others lines. The gotcha is with updates where it is destfield = sourcefield and this is where the block copy / paste can be useful. If all your update ='s signs line up you can block select the source field list (from the script as select output), position the cursor after the first ='s sign and paste and there you have the sourcefield list populated.

    You can of course then alt-shift select the beginning of those fields and add in your table alias to all of them in one go and viola - a scalable way to write update statements.

    Note that it does matter if you copy the source text as a block (using ALT) or as wrapped text (not using ALT). It changes the way that the paste works, both if you paste to a single location vs pasting to a selected area or a block selected area. I won't go into detail here but worth mentioning as it caught me out a few times as each combination has a slightly different effect. Maybe I need my own blog post for this kind of stuff!

    This also works for store procedure arguments etc. if you line up your symbols for easy reuse.

    There are other areas that this can help, but this is probably the most common.

    Thursday, June 14, 2012 10:55 AM
  • while we're on the subject of DML from SSOX (sorry, can't resist)...

    I'm currently writing a default constraint defined as:

    DEFAULT (N'NVG' + RIGHT(N'00000000000000000' + CONVERT(NVARCHAR(20),1),17))

    Now, that '1'  isn't really what I want - I actually want to use a value from a Sequence. Thing is, I can't remember what the syntax is for getting the next value for a sequence and the only way I'm going to find it is to go to Books Online. How much easier would it be if I simply dragged my sequence object from SSOX onto my design surface and it automatically created:

    NEXT VALUE FOR [dbo].[MySequenceName]

    for me? That would be so so cool.

    Are you getting all this Sam? :)


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 14, 2012 12:52 PM
  • This is fairly easy with "insert ... select" - as source/destination columns don't trespass on each others lines. The gotcha is with updates where it is destfield = sourcefield and this is where the block copy / paste can be useful. If all your update ='s signs line up you can block select the source field list (from the script as select output), position the cursor after the first ='s sign and paste and there you have the sourcefield list populated.

    You can of course then alt-shift select the beginning of those fields and add in your table alias to all of them in one go and viola - a scalable way to write update statements.

    Aha.
    That. Is. Awesome. I write lots of MERGE statements and this would definitely come in useful - especially given that I already always line up my "="s

    Geeky fun!


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 14, 2012 1:02 PM
  • 3/4) We kicked around several ideas on this. I think we have a very cool idea for filtering that I'm looking forward to trying out on you guys. The count is a little tricky since we lazy load the tree... You could end up spinning a lot of connections up to do that.  Perhaps an option to pre-populate portions of a tree? or Project only counts?

    Project-only counts would be fine for me. Browsing projects is what I use SSOX for 95% of the time anyway.

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 14, 2012 1:05 PM
  • Agreed on the square bracket feature to go alongside the refactor fully-qualify all objects

    I skipped over that earlier on - fantastic idea. Not sure that this is a refactor operation given that you'd only be changing views/sprocs/functions (thus an ALTER would be sufficient) but nonetheless, I would love an option to fully qualify everything. Perhaps if you guys had extensibility hooks someone else could build it (hint hint) :)

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Thursday, June 14, 2012 1:09 PM
  • Thanks for your great feedback guys and discussion. I will keep this thread around for our next releases.
    • Proposed as answer by Sam Hughes Monday, June 18, 2012 5:18 PM
    • Marked as answer by Janet Yeilding Monday, June 18, 2012 7:23 PM
    Monday, June 18, 2012 5:15 PM
  • First off, this is really nice work.  I'm glad Microsoft is taking on this painful problem and SSDT is a nice start.

    I absolutely need to be able to handle reference data.  I know it's on the way, just giving my thumbs up on that feature.

    Another feature that is essential for my work is the ability to filter out specific objects when doing a schema compare.  Right now, the tool can exclude all objects of a given type.  Filtering to the object level would allow me to ignore any customer changed objects when generating an upgrade script for them.

    Tuesday, July 17, 2012 3:03 PM