There may be situations when you want to get all of the tables in a SQL Server database and the data they currently hold, but you can’t (because of permissions assigned to your account, for example) or don’t want to backup the whole database. Well, follow the following steps to achieve this:
1- Open SSMS and connect to the correct instance of SQL Server;
2- Right-click on the appropriate database entry under Databases folder, go to Tasks, and then select “Generate Scripts…” . Generate and Publish Scripts wizard launches;
3- Click next in Introduction page;
4- In the next page, Choose Objects, pick out “Select specific database objects” and then check the checkbox beside Tables entry. You can even select the tables for which you want to get the scripts specifically by clicking on the + sign beside Tables entry. Click Next;
5- Here’s the tricky part: you can get either just the tables’ schema only or schema together with the data for the tables selected in the previous step. To do this, click on the Advanced button. Then in the Advanced Scripting Options page find “Type of data to script” property and change it to “Schema and data”. (It is set to “Schema only” by default) Click Ok to go back to Set Scripting Options page;
6– Select “Save to new query window” and hit Next 2 times. When all the actions listed are completed click Finish.
After completing these steps, you’ll get a new query window opened in SSMS which has the script to create all the tables and insert the records they already contain in the original database.
In Data we trust 