quarta-feira, 25 de abril de 2018

[From Technet] The April release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the April release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this April release is the fifth major update since the announcement. If you missed the March release announcement can be viewed here.

The April Public Preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top Github issues.

Highlights for this build include the following.

  • Public preview release of SQL Agent extension
  • Added new extensions and improved existing extensions
    • Improvements to Server Reports Extension
    • Release of SSMS Keymap extension
    • Release of AlwaysOn Insights extension
    • Release of MSSQL Instance Insights
    • Release of MSSQL Db Insights
  • Added Visual Studio Code 1.21 platform source code refresh
    • Improved large and protected file support for saving Admin protected and >256M files within SQL Ops Studio
    • Integrated Terminal splitting to work with multiple open terminals at once
    • Reduced installation on-disk file count footprint for faster installs and startup times
  • Continue to fix GitHub issues

For complete updates, refer to the Release Notes.

Preview release of SQL Agent extension

Since SQL Operations Studio was released for public preview, one of the most requested features was providing SQL Agent support. Bringing over the most popular SSMS features has always been on our roadmap, but we wanted to make sure we did this the right way. For years, customers have submitted issues that were difficult to change due to being built on old dialog and wizard frameworks. With SQL Operations Studio, we had an opportunity to bring a modern user experience to our features while maintaining the same functionality that our users are experts with.

To make this possible, the engineering team reached out to the SQL Server community to learn more about your top scenarios and get direct feedback about our initial mock-ups. This involved creating surveys, scheduling user interviews, and promoting community discussion through a demo on Youtube showing our initial prototype. With your help and the release of Extensions Manager in the March release, we have provided you an initial preview release of SQL Agent.

When you install SQL Agent from the Extension Manager, you can view the SQL Agents extension as a tab on your server dashboard. To learn how to install an extension, please view this how-to guide.

This initial release focuses on providing a great View Jobs and Job History experience. You can see a list of all jobs including color-coded successful and filed jobs, names of jobs, and error messages. To see the job history of a specific job, you simply click on that job. This view lets you see a history of past runs, and also provides the ability run or stop the job.

The next step will be to add Job Configuration functionalities, including providing support for creating a job, setting alerts, and scheduling jobs. We would love to hear your feedback about this initial release through our GitHub Issues page and also any suggestions you may have as we build out Job Configuration.

This is the first step as we continue to bring over popular features to SQL Operations Studio from SSMS. Please continue to let us know what are your must-have features and feel free to join the discussion.

Adding and improving extensions

In the March Public Preview release, we first introduced the Extension Manager to SQL Operations Studio. With this release, we introduce 5 new extensions that you can now try out in the Extensions Manager (to get started, read the how-to install extension guide).

  • SQL Agent is the extension to view and run SQL Agent jobs as described earlier.
  • SSMS Keymap ports the most popular SSMS keyboard shortcuts to SQL Operations Studio. Created by Kevin Cunnane.
  • AlwaysOn Insights is a collection of widgets designed to provide insights into AlwaysOn Availability Group components to assist DBA’s or similar in managing their environment. Created by Matt Lavery.
  • MSSQL Instance Insights is a collection of widgets designed to provide insights into MSSQL Instance to further extend the built-in default widgets. Created by Matt Lavery.
  • MSSQL Db Insights is a collection of widgets that are designed to provide insights into MSSQL Database to further extend the built-in default widgets. Created by Matt Lavery.

Over the past month, we have received a lot of emails and tweets from the community who were interested in creating their own extension. One of our engineers, Kevin Cunnane, wrote awesome blog posts that includes his process to create and publish the SSMS Keymap extension:

For additional resources to get started writing an extension, please refer to our GitHub Extensibility Wiki Guide. Feel free to also reach out to @sqlopsstudio on Twitter if you need help getting started after checking out these resources.

In addition to adding new extensions, we also brought updates to existing extensions, especially Server Reports. These changes include:

  • Fixed DB Space Usage where it threw an error when database names contain special characters
  • Changed DB Space Usage and DB Buffer Usage to show only top 10 data
  • Updated ReadMe to reference Paul Randal’s wait types library for more info about the Wait Counts widget

As we build out our extensibility story, we will continue to collaborate with the community to learn and improve the way we build and publish extensions. This is the way we envision bringing over SSMS features while also empowering the community to contribute and build their own extensions to share with the community.

Visual Studio Code Refresh

One of the most significant highlights of this release is the Visual Studio Code 1.21 platform source code refresh. Since we fork from VS Code, we do these periodic updates and also get feature improvements. The key highlights with this refresh specifically for SQL Operations Studio are:

  • Improved large and protected file support for saving Admin protected and >256M files within SQL Ops Studio
  • Integrated Terminal splitting to work with multiple open terminals at once
  • Reduced installation on-disk file count footprint for faster installs and startup times

For additional details, checkout the Visual Studio Code February Release Notes, and the Visual Studio Code January Release Notes.

Fix GitHub issues

Fixing user-reported issues may not always get as much recognition as new feature releases, but it is definitely worth calling out. If we truly want to be a community driven tool, we will continue to work on addressing your submitted issues. Here is a summary of issues addressed:

  • #37 When the chart viewer throws an error, unexpected behavior occurs.
  • #462 Feature Request: Option for Server Groups to be expanded by default
  • #1023 Add square brackets for ms_foreachdb call from flyfishingdba
  • #1050 Clear insights view before showing error
  • #1057 Restore and new query actions in explorer-widget are broken
  • #1068 Dashboard Output windows pops-up with error message for Azure DB
  • #1069 Connection Dialog shows Server Required error when initially displayed
  • #1070 Server Groups now require a double-click to expand
  • #1072 Select control background is semi-transparent
  • #1115 Fix all high contrast accessibility issues in sqlops
  • #1101 Extension fails to upgrade “Download Manually” link goes to wrong location
  • #1103 V Scroll not working on Home Tab
  • #1104 SQL extension tabs stopped working
  • #967 Expect query plan when select XML showplan in the result grid
  • #606 intellisense – Bad suggestion for ‘update’ command
  • #1048 Pre-login SSL/TLS handshake error
  • #1150 Various types of query charts throw exceptions and don’t render

Contributions and thank you

We would like to thank all our users who raised issues, and in particular the following users who helped contribute fixes:

  • flyfishingdba for Add square brackets for ms_foreachdb call (#1023)

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below, message us on Gitter, or tweet us @SQLOpsStudio.



from SQL Server Blog https://ift.tt/2KfcOib

[From Technet] Modernize to cloud with Azure SQL Database

Companies are embracing digital transformation by migrating to the cloud. IT organizations are continuously looking for new ways of optimizing how they use the data that fuels their operations. Modernizing to the cloud offers the right mix of operational efficiencies and business enablement to drive continued growth. IT optimization now requires new perspectives to consider:

  1. eliminate time spent managing “long tail” of applications – lift and shift to managed cloud,
  2. free up limited IT resources to drive transformation, and
  3. migrate business critical apps to the cloud – extend and innovate

One way IT organizations can confront challenges on their transformation journey is by adopting a PaaS (Platform as a Service) solution like Azure SQL Database. Azure SQL Database is a fully-managed relational database-as-a-service that provides the best and most economical cloud destination for your SQL data. You can build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure.

Within SQL Database, there is a new deployment option called Managed Instance that enables you to quickly and confidently move your SQL Server data (2005 and newer) at scale to the cloud. SQL Database shares a common code base with SQL Server and supports most of the same features at both the database and instance level, so you can reduce or eliminate the need to rearchitect your apps for the cloud.

Now is the perfect time to start your migration. Not sure how? Sign up for our webinar on April 26th to learn how you can start your migration journey to Azure SQL Database and learn how Managed Instance works. We’ll discuss the new innovations in SQL Database, security, enhanced performance and analytics built-in to modernize your business. Register today!



from SQL Server Blog https://ift.tt/2HNOkOR

terça-feira, 24 de abril de 2018

[From Technet] SQL Server 2016 Service Pack 2 is now available

The SQL Server team is excited to bring you the second service pack release for SQL Server 2016. The service pack is now available for download on the Microsoft Download Center and will be coming soon to Visual Studio Subscriptions, MBS/Partner Source, and VLSC. As part of our commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2016 deployments.

SQL Server 2016 Service Pack 2 (“SP2”) contains a roll-up of released hotfixes as well as multiple improvements centered around performance, scalability, and supportability based on feedback from customers and the SQL community. These improvements enable SQL Server 2016 to perform faster and with expanded supportability and diagnostics. It also showcases the SQL Server product team’s commitment to providing continued value into in-market releases.

SQL Server 2016 Service Pack 2 includes:

  • Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
  • Supportability and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2012 SP4.

Find more information on SQL Server 2016 SP2 in our KB article and Release Notes, and download today by visiting the links below:

If you have a question or would like to make a suggestion, you can let us know through UserVoice. We look forward to hearing from you!



from SQL Server Blog https://ift.tt/2HoPzV9

quinta-feira, 19 de abril de 2018

[From Technet] SQL Server 2017 on SUSE Enterprise Linux Server offer

This post is authored by John ‘JG’ Chirapurath, General Manager, Azure Data.

Since SQL Server 2017 on Linux was made generally available on October 2, 2017, we have seen tremendous growth in adoption. We’ve had ~ 5 million Docker pulls for SQL Server 2017 on Linux and are seeing strong affinity with our customers who are on other database systems running on Linux as well as customers consolidating on Linux as their operating system. Additionally, we’ve had a great response from the open source developer community.

SQL Server 2017 is supported on Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), and Ubuntu. It is also supported as a Docker image, which can run on Docker Engine on Linux or Docker for Windows/Mac.

In the path forward to deliver choice, today Microsoft and SUSE are announcing some great offers for our mutual customers both on-premises and on Azure to adopt SQL Server 2017 on Linux as their database of choice.

Microsoft and SUSE have a decade long relationship, powering enterprises focused on interoperability and high-performance solutions. With SQL Server 2017 running on SUSE Enterprise Linux Server, we are bringing the most cost-effective path for our customers. If you combine the industry-leading database with the Enterprise Linux OS supporting Persistent Memory that can turbo-charge your analytics performance, you are looking at a winning combination for all your high-performance workloads.

Recently, Microsoft, SUSE, and HPE announced a new record-setting performance benchmark thanks to Persistent Memory support in SUSE Linux Enterprise Server 12SP3.

  • HPE’s new world record 1TB TPC-H benchmark result showcases the first enterprise-class “diskless database” leveraging persistent memory and the ability to deliver > 5x performance on analytic queries directly against storage at up to 50 percent of the cost.
  • This incredible price/performance is achieved by combining the power of SQL Server 2017, HPE’s scalable persistent memory, and SUSE Linux Enterprise 12 SP3 Persistent Memory Support.

Customers can receive all the benefits of SQL Server 2017 running on SUSE Enterprise Linux Server with limited time pricing offers to help with upgrades and migrations. For a limited time, SUSE is offering customers a self-service promotional code to drive cost savings for SQL Server on SUSE Linux providing a 100% discount on the software cost of SUSE Linux Enterprise Server 12SP3 including free patches and maintenance for 1 year – qualified customers are also eligible for up to 100% discount on 24×7 or 12×5 phone support by working with a SUSE sales rep. Avail the offer through SUSE’s self-service portal.

Microsoft is also offering discounted SQL Server Licenses to lower your TCO even further. Customers can avail up to 30% off SQL Server 2017 through an annual subscription. Customers can get enterprise features for their Linux workloads, backed by Microsoft security and support. Please check out our page to learn more about the offer.

In addition to discounts on SQL Server and SUSE Enterprise Linux, all of this is backed by joint support from Microsoft and SUSE. Learn more about this offer, plus how SQL Server and SUSE work together for performance and reliability at low TCO by talking to your local SUSE or Microsoft seller today!

Please download the brochure to learn more about the joint offer.

Want to build an app using SQL Server? Get started with SQL Server on macOS, Linux and Windows today!



from SQL Server Blog https://ift.tt/2K0jmB9

terça-feira, 10 de abril de 2018

[From Technet] Celebrating 1M installations milestone for mssql extension for VS Code with another update

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce that a new update for the mssql extension for VS Code is now available on the VS Code Marketplace.

Use the mssql extension to turn VS Code into a powerful editor of T-SQL development, which supports SQL Server running on-prem, Azure SQL Database, and Azure SQL Data Warehouse.

Download the latest update from the VS Code Marketplace.

1M installations milestone

Thanks to your help, the mssql extension has hit an awesome milestone of 1 million+ downloads in less than a year since general availability announcement on May 1, 2017. Since GA, more and more VS Code users have downloaded the mssql extension through the recommended extensions experience as they write out T-SQL queries.

As our user base grows, we will continue to send updates from the high priority issues determined by the community. To make this meaningful for the community, we need your help to report and upvote your high priority features and issues on our Github Issues page. Over the past year, the SQL Tools team has prioritized bringing our new tools open source, and using the Github Issues page to help drive priority has become common practice. Check out SQL Operations Studio and mssql-cli Issues page to see this practice in action.

Thank you again for using this tool and providing us feedback. We know how much the developer community loves VS Code, and we will make sure you continue to have an awesome T-SQL editing experience with our future updates.

Release notes

  • Fixed issue #1036 where copy/pasting Unicode text can fail on Mac depending on the active locale environment variable
  • Fixed issue #1066 RAND() function using GO N produces the same result
  • Syntax highlighting more closely matches SSMS for local variables, global system variables, unicode string literals, bracketed identifiers, and built in functions
  • Show all error messages instead of just the first one when query execution results in multiple errors

Contributions and “thank you”

We would like to thank all our users who raised issues, and in particular the following users who helped contribute features or localization of the tool:

  • @rhires for updating and editing the Kerberos help documentation
  • @zackschuster for cleaning up the VS Code API wrapper to remove a deprecated function call
  • We would like to thank everyone who contributed to localization for this update and encourage more people to join our open source community localization effort.

For more information, check out the full release notes here: Release Notes

Get started

Eric Kang demoing mssql extension for VS Code

Contact us

If you have any feature requests or would like to report any issues, please open an issue on our Github Issues page. For any other updates, please follow us on Twitter @sqldatatools.



from SQL Server Blog https://ift.tt/2qkJ7UH

[From Technet] Cloud data and AI services training roundup

To help you stay up to date on online training opportunities, we’re releasing a monthly list of the latest free data and artificial intelligence (AI) sessions in one convenient post.

SQL Server 2017

Whether on Windows, Linux, or Docker containers, you have the flexibility of leveraging SQL Server 2017’s industry-leading performance and security wherever you like. Here’s a rundown of recent and upcoming training sessions to help you learn more.

  • Complying with GDPR on Microsoft SQL-based technologies

Extended support for SQL Server 2008 and 2008 R2 is coming to an end on July 9, 2019, which means it’s time to choose your path to modernization. Without support, security updates will no longer be available, and you may run the risk of non-compliance with industry regulations such as GDPR (General Data Protection Regulation). Join this detailed training session on demand to understand how to begin your migration journey today.

  • Move your transactional workloads to the cloud with Azure SQL Database

Moving your on-premises database to the cloud has never been easier. Join this demo-filled training to learn how Azure SQL Database helps you unlock the potential of your database (and your people) without changing your application architecture. Specifically, we cover how to use built-in intelligence for enabling high performance and security, building reports into your apps to drive customer value, and management of emerging application requirements.

Artificial Intelligence (AI)

Infuse your apps, websites, and bots with intelligent algorithms to see, hear, speak, understand, and interpret your user needs through natural methods of communication. The Microsoft AI platform offers a comprehensive set of flexible AI services for any scenario and enterprise-grade AI infrastructure that runs AI workloads anywhere at scale.

  • Introduction to Custom Vision Service

If you are looking to easily customize your own state-of-the-art computer vision models that fit perfectly with your unique use cases, this session is for you. Join us for the 60-minute demo-rich training to learn more about Microsoft’s Custom Vision Service, a tool for building custom image classifiers. In addition to an overview of the tool, you’ll learn to use labeled images to teach Custom Vision Service the concepts you want it to learn and to use simple REST API calls to quickly tag images with your new custom computer vision model.

Azure Cosmos DB

Azure Cosmos DB offers the first globally distributed, multi-model database service for building planet-scale apps.

  • Azure Cosmos DB Technical Training Series

Engineering experts are leading a seven-part technical training series on Azure Cosmos DB, complete with interactive Q&As. In addition to technical deep dives, this series covers a wide array of topics. Join the series to learn to build serverless applications and conduct real-time analytics using Azure Cosmos DB, Azure Functions, and Spark. Attend the entire series (past sessions are available on demand) to become an Azure Cosmos DB guru, or simply register for the sessions that interest you.



from SQL Server Blog https://ift.tt/2qmypfv

Treating missing values in R

There are different ways to treat missing values in R. One of the most commom ways to do that, is to replace the NA values with the mean (or mode). You can do that in a vector (or a column/line from  a data frame) using the is.na function:

> testena <- c(2,3,8,NA,9)
> testena[is.na(testena)] <- mean(testena,na.rm=T)
> testena
[1] 2.0 3.0 8.0 5.5 9.0

On RStudio...



Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

segunda-feira, 9 de abril de 2018

[From Technet] Celebrating data and you with #SQLDataWins

Hey you, data whiz! You’re an expert, a data aficionado, a steward of a finely tuned database – but not everyone gets it. Well, we do.

#SQLDataWins celebrates professionals who make an impact with data every day. Play along with us on Twitter by responding to our weekly tweets. It’s that simple.

Every Monday at 9:00am PT @SQLServer will tweet out a fun data-related scenario. We want to see your reaction in the form of a GIF, because, who doesn’t love a good GIF? Reply directly to the weekly tweet by Thursday at 11:59pm PT with your favorite GIF and include the hashtags #Sweepstakes #SQLDataWins in your tweet and you’ll be entered to win* an awesome SQL Server prize pack! A couple pro tips: You must be a legal resident of the 50 United States, and be sure to come back to @SQLServer every week for a brand-new scenario and another chance to win.

Looking to brush up on all things SQL Server and keep on winning with data? Check out these helpful resources to keep your data game on point.

FAQs

How do I enter?

  1. Visit twitter.com/sqlserver
  2. Find the current week’s #SQLDataWins scenario tweet that’s pinned to the top of the timeline during each prize period
  3. Reply using the speech bubble/comment icon underneath the @SQLServer tweet
  4. Use the built-in GIPHY function in Twitter or select a GIF from the web or your phone to attach to your reply
  5. Include the two campaign hashtags by typing #Sweepstakes #SQLDataWins in the body of your reply
  6. Hit send!

Am I eligible to win?

You need to be:

  • A legal resident of the 50 United States (including the District of Columbia)
  • 18 years of age or older
  • If you’re under 18, you need to have consent of a parent or legal guardian

What can I win?

Each prize pack includes three (3) custom #SQLDataWins items:

  • One (1) Coffee mug
  • One (1) T-shirt
  • One (1) Backpack

How many winners are there each week?

We’ll randomly select 10 lucky winners each week

How do I find out if I won?

Check your mentions on Fridays! We’ll reply to the week’s winners every Friday. Be sure to DM us within 72 hours so we can confirm that you’re eligible and get your address to send you your prize!

Can I enter every week to win?

Yeah, go for it!

Can I enter to win more than once per week?

No, you can only enter to win once per week. You can reply as much as you want (you might just REALLY love GIFs), but multiple replies within the weekly entry period will not increase your chances of winning.

*No Purchase Necessary. Game ends 6/7/18. For full details, see the Official Rules.



from SQL Server Blog https://ift.tt/2qkeONd

quarta-feira, 28 de março de 2018

[From Technet] The March release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server and Karl Burtram, Senior Software Engineer, SQL Server. 

We are excited to announce the March release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this March release is the fourth major update since the announcement. In case you missed it, you can view the February release announcement here.

Take a moment to breathe, this is a loaded update.

The March Public Preview release is focused on improving our Extensibility story and continuing to address top GitHub issues. This includes enabling Extension Manager, improving the Manage Dashboard experience and providing a couple Insights extensions. Please see the following details.

  • Enhance the Manage Dashboard extensibility model to support tabbed Insights and Configuration panes
  • Dashboard Insights extensions for sp_whoisactive from whoisactive.com and a Server Reports example
  • Extension Manager enables simple acquisition of 1st-party and 3rd-party extensions
  • Add additional Extensibility APIs for connection and objectexplorer management
  • Community Localization open for 10 languages
  • Continue to fix important customer impacting GitHub issues

For complete updates, refer to the Release Notes.

Extension Manager enables simple acquisition of 1st-party and 3rd-party extensions

The Extension Manager makes it easy to discover and acquire additional SQL Operations Studio functionality. We’ve adopted the in-product extension management experience from Visual Studio Code. For users unfamiliar with this UI it consists of the following components.

  • ‘Marketplace’ of Recommend Extensions which can be easily discovered and installed
  • List of Installed Extensions which can be disabled or uninstalled
  • Extension Details page that displays the extensions readme content and other metadata

Extensibility is a key tenet of the SQL Operations Studio project roadmap. We plan to make available both 1st and 3rd features as extensions. We look forward to working with the community to build a rich tools ecosystem. Please contact us on Gitter if you’re interested in building an extension.

Note: If you don’t see the Extension Manager icon you may need to enable it by right-clicking on the left Activity Bar, and then click Extensions on the context menu.

Dashboard Insights extensions for sp_whoisactive from whoisactive.com

The March release introduces improved Manage Dashboard extensibility. Extensions can now provide tab panels to the dashboard to group related functionality. Check out the sp_whoisactive extension as a great example of using the dashboard insights extensibility points.

Here are some notes from the extension readme: Sp_whoisactive is a procedure written by Microsoft MVP Adam Machanic. It is a very useful tool for activity monitoring and troubleshooting.

Who is Active is a DMV-based monitoring stored procedure that uses 15 different views to show a large amount of data about what’s running on your server.

Who is Active was designed to be extremely flexible, and includes options to not only get different types of data, but also to change the output column list and sort order.

Check out the source code for the whoisactive extension and other extensibility samples.

Join the extensibility ecosystem

With these extensibility investments, we want to empower the community to build their own extensions and join our extensibility ecosystem. We strongly believe that our talented SQL Server community can build awesome extensions, and we have provided resources to help get you started:

Community Localization Open

Recently, we announced SQL Operations Studio availability on the new Microsoft Community Localization platform. Localization is now opened for 10 languages: French, Italian, German, Spanish, Simplified Chinese, Traditional Chinese, Japanese, Korean, Russian, and Portuguese (Brazil). You can contribute to localization for both software and docs.

Contact us

If you have any feature requests or issues, please submit to our Github issues page. For any questions, feel free to comment below, message us on Gitter, or tweet us @SQLOpsStudio.



from SQL Server Blog https://ift.tt/2pOq2JA

CREATE TABLE Script when you don't have the rights for it?

I was trying to get the CREATE TABLE script from a table in SQL SERVER, and due to permission rights, I was getting "Property text is not available for DefaultConstraint. This property may not exist for this object, or may not be retrievable due to insufficient access rights".

So how do I get the CREATE TABLE script when I dont have the rights for it?

I ended up finding the following script for it on crackoverflow: https://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table 

Here it is:

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

That gives me CREATE TABLE script for the table, that I need for a ETL job.

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06