none
SQL Sever man studio 2012 script table as function MS missed boat on this one RRS feed

  • Question

  • from object explorer you can right click on a table ->script table as->select to->new query window

    the code that gets generated has changed

    ver SQL man studio 2008 r2

    SELECT [ActiveID]

          ,[SubscriptionID]
          ,[TotalNotifications]
          ,[TotalSuccesses]
          ,[TotalFailures]
      FROM [ReportServer].[dbo].[ActiveSubscriptions]
    GO


    ver SQL man studio 2012

    USE [ReportServer]
    GO

    SELECT [ActiveID]
          ,[SubscriptionID]
          ,[TotalNotifications]
          ,[TotalSuccesses]
          ,[TotalFailures]
      FROM [dbo].[ActiveSubscriptions]
    GO

    even though I have my tools->options->SQL server object explorer->scripting->table and view options->schema qualify object names set to true.

    it does not seem to work?

    I'm expecting it to generate  [ReportServer].[dbo].[ActiveSubscriptions]


    Friday, April 11, 2014 6:33 PM

Answers

  • Hi,

    The script a table functionality has changed since SQL Server Management Studio 2012.

    To retain [databasename].[schema].[tablename], I also recommend to use SQL Server Management Studio previous version as the workaround. Connect to SQL Server 2012 instance and create scripts to select.

    Sorry for the inconvenience you have experienced and thank you for your understanding and continued support.

    Thanks.


    Tracy Cai
    TechNet Community Support

    • Proposed as answer by DeviantLogic Sunday, April 20, 2014 5:13 AM
    • Marked as answer by sleepy01011 Monday, April 21, 2014 3:53 PM
    Monday, April 14, 2014 6:03 AM
    Moderator

All replies

  • Hi Sleepy,

    I did check this setting in SQL 2014 CTP 2.  Same functionality.

    I know that the options have change from 2005/2008 (R2) to 2012 / 2014.

    Dropping the USE statement under general settings does not effect the output.

    At the database level, you can script multiple objects under the task section.

    It does show you the options selected before generating the script to file or query window.

    One of the things that I miss are automatic drop and create.

    You can either drop or create without testing for existence.

    In the older version, it would test for you.

    I will look around for a 2005/2008 version to test.  But my hunch is they changed the functionality.

    Sincerely

    John

    MCDBA 2000 / MCSA 2003

    www.craftydba.com

    PS:  

    Check out this blog article on old options.

    http://www.kendalvandyke.com/2009/01/improvements-to-scripting-in-management.html

    Check Aaron Bertrand's reply.  Write you own tool (TSQL script) and do not be at the mercy of Microsoft.

    http://sqlblog.com/blogs/andy_leonard/archive/2010/07/19/why-does-ssms-generate-create-scripts-with-exec-dbo-sp-executesql.aspx

    • Edited by John Miner Friday, April 11, 2014 6:58 PM
    • Proposed as answer by Naomi NModerator Friday, April 11, 2014 7:23 PM
    • Unproposed as answer by sleepy01011 Friday, April 11, 2014 7:48 PM
    Friday, April 11, 2014 6:48 PM
  • for those of us who work in data mart and warehouse environments... not having the db qualified name is a real bummer.  I'm actually going to go back to sql man studio 2008 because this is a must have for me.  hate when they do stuff like this because it was a perfect feature.  I use it all the time.

    I have 3 or 4 replicants and 3 or 4 star schemas that I need to do adhoc stuff with... such a huge productivity killer to have to cut and paste db names back into everything.  driving me nuts.

    and yes I suspect your correct I tried a couple different things and they missed the boat on this one.

    Friday, April 11, 2014 6:58 PM
  • Hi,

    The script a table functionality has changed since SQL Server Management Studio 2012.

    To retain [databasename].[schema].[tablename], I also recommend to use SQL Server Management Studio previous version as the workaround. Connect to SQL Server 2012 instance and create scripts to select.

    Sorry for the inconvenience you have experienced and thank you for your understanding and continued support.

    Thanks.


    Tracy Cai
    TechNet Community Support

    • Proposed as answer by DeviantLogic Sunday, April 20, 2014 5:13 AM
    • Marked as answer by sleepy01011 Monday, April 21, 2014 3:53 PM
    Monday, April 14, 2014 6:03 AM
    Moderator