SSMS Tip – how to script data

Just wanted to write up a SSMS tip I’ve found really useful over the years but not everybody knows it is there.

Start by right clicking on the database which contains the data you want to script.

N.b. The tasks menu doesn’t appear on the context menu of the tables just at database level.
Select Tasks> Generate Scripts

You should see the following dialog

Select the table(s) you want to script

So the key to this tip is to click the advanced button

(You’ll probably also want to pick the Save to new query window radio  button)

Set the correct options
Here we set to script Schema & Data, also worth noting is that the default is to NOT script indexes – which is rather annoying.

Enjoy your script with both the table and the data

<pre> 


USE [CONTROL]

GO

/****** Object:  Table [dbo].[STAGE_SourceConnection]    Script Date: 2017-03-03 16:21:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[STAGE_SourceConnection](

       [Id] [int] NOT NULL,

       [Name] [varchar](100) NULL,

       [SourceType] [varchar](5) NULL,

       [Provider] [varchar](100) NULL,

       [Connection] [varchar](500) NULL,

       [Inserted] [datetime2](7) NULL,

       [Updated] [datetime2](7) NULL,

       [Comment] [varchar](max) NULL,

       [Active] [bit] NULL,

 CONSTRAINT [PK_STAGE_SourceConnections] PRIMARY KEY CLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [CONTROL]

) ON [CONTROL] TEXTIMAGE_ON [CONTROL]




GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[STAGE_SourceConnection] ([Id], [Name], [SourceType], [Provider], [Connection], [Inserted], [Updated], [Comment], [Active])

VALUES (5, N'Allegro', N'DB', N'SQLNCLI11', N'Data Source=PROD2051\PROD04;Initial Catalog=AllegroProduction_OT;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', NULL, NULL, N'Allegro', 1)

GO

INSERT [dbo].[STAGE_SourceConnection] ([Id], [Name], [SourceType], [Provider], [Connection], [Inserted], [Updated], [Comment], [Active])

VALUES (105, N'Adventure Works', N'DB', N'SQLNCLI11', N'Data Source=PROD2051\PROD04;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', NULL, NULL, N'AdventureWorks2012', NULL)

GO</pre>
&nbsp;