0

I'm aware that to generate the script we need to follow the steps in Management studio;

Right click the database

Select Tasks -> Generate Scripts

(Click next if you get the intro screen)

Select "Select specific database objects"

Pick the objects to generate scripts for (tables, stored procedures, etc...)

Click Next, then specify the output filename

This will generate the schemas only. If you want to do data generating scripts as well, click the Advanced button and scroll down to the "Types of data to script" and change it from "Schema only" to "Data only" or "Schema and data"

Click Finish to generate the script

But I do not want to every time to do this, is there any query or SP or functions to do the same Job, because since my work is to connect remote to client and get the schema to my local and do the job so following the above steps would be tedious and time consuming for me. it would much help full if any ready made script works are available.

Mar1009
  • 721
  • 1
  • 11
  • 27
  • No, as far as I know there is no way to simplify this ad hoc... however, you might consider creating a SSIS package which performs this task for you. Requires a bit of coding but works quite fine... – Tyron78 Jun 11 '18 at 12:28
  • You are basically asking for a restore? – dfundako Jun 11 '18 at 12:29
  • Doing it with queries is possible in principle, but tedious and error prone in practice. For stored procedures it's easy enough (the definitions are in `sys.sql_modules`) but tables are involved. To generate scripts programmatically, you can use SQL Management Objects. In recent versions of SQL, there's also [`DBCC CLONEDATABASE`](https://support.microsoft.com/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only) to produce a schema-only version of your database. Backing up and restoring clones is not *officially* supported, but does actually work. – Jeroen Mostert Jun 11 '18 at 12:30
  • possible dupe: https://stackoverflow.com/questions/483568/how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2 I have been able to take an old sql pub wizard.exe and make it work with newer versions of SQL, but it can be tricky. – sniperd Jun 11 '18 at 12:41

0 Answers0