Script out all SQL objects under particular schema

Question

Is there any way I can script out all the SQL Server objects (tables, SP, functions etc) under a schema?

In our database we have a table containing name of all the schemas and there are more than 500 schema. Some of them are for dev and some are prod. I need to script out all the objects under dev schema and create a new database.

Answer 1

ApexSQL Script is the tool which can be very helpful in this situation. It is the tool which creates SQL scripts for database objects, and it can script all objects into one script or all objects separately.

For this situation here is what you should do:

1. Select server and the database which you want to script and load them.
2. Go to the View tab and click the “Object filter” button, then select the “Edit filter” button:

3. In the Filter editor for all objects select the “Include if:” and “Click here to add filter criteria”:

4. Select the “Schema”, “Equals” and Enter the desired schema name, then click OK:

5. Click on the Home tab, check all objects and Click the “Script” button:

6. In the third step of the Synchronization wizard, under the Script file tab, select if you want to create one script for all objects or for each object individually from the Granularity drop down menu:

7. In the last step of the Script wizard click the Create button and check out the results – you will have the script which can be executed in the SQL Server Management Studio.