SQL:Generate Script for Table Data

Today, one of my colleague asked me, how to generate a Script for a table-schema as well as data-Schema?

Whatever I explained to him, I thought to share the same in my blog as well, hope it helps to someone!!!

Most of you are aware that we can generate T-SQL scripts for multiple or individual objects by using the "Generate Scripts” Wizard thru Object Explorer.

You can also generate a script for data as well, the following steps will explain you, how to generate the Script for Table Schema with Data.

1. Right Click on Database (in which you want to extract schema for Data) >> Tasks >> Generate Scripts >>

2. “Generate Scripts” wizard will be appearing with 5 Tabs like

• Introduction
• Choose Objects
• Set Scripting Options
• Summary
• Save or Publish Scripts

By default, it is pointing to Introduction Tab

Note:  if you want to skip the Introduction Page from next usage of this wizard, then you can check the option “Do Not Show this Page Again”. After this setting, if you open the “Generate Scripts” wizard, by default it will point to
“Choose Objects” Tab.

3. Click on the button “Next” to redirect to “choose Objects” Tab.“Choose Objects” Page has following two options

• Script entire database and all database objects (Default Option)
• Select Specific Objects

By Default, it will be pointing to Entire Database Option (#1). If you want to create a script for specific table then check the second option as exhibit below.

4. After Specific SQL Objects, Click on the “Next” button to redirect to “Set Scripting Options” Tab, here follow the 2 steps

• Specify the output path of the script file in File name Text box (refer the exhibit which is marked with maroon color box)
• Click on the Advanced Button

Model popup will appear with list of options which are categorized as General and Table/View Options

Under General category, Find the option labeled as “Type of data to Script” (see the exhibit below), this option has following 3 values

• Schema Only (Default Option)
• Schema and Data
• Data Only

Select it accordingly based on your requirement. Once you select the appropriate option, click on the OK button in the model popup window

Note: Choose “Schema and Data” option to generate the script for Schema as well as Data.

5.Click on Next Button to redirect Next Tab which is named as “Summary”, here you can review/verify all your selection and settings.

6.After review your setting, Select Next generates the script.

7.Click on the Finish Button after the status of selected action says success.
 

8.Check the Path which you specified on step 4(a), you can see file with “.sql” extension.
Note: If there is no record in the selected table then wizard will extract only schema.

Sample Output File: