1

I am running SQL Server 2014 and using SSMS.

Is there a way to script out all objects in a specific schema (stored procedures, views, tables etc.) so that I can generate the objects in another database?

Is there a way to narrow it down to scripting out only all the tables in a certain schema?

3

There's no automatic way to do that through the UI. You can selectively script just those objects, but it relies on manually checking a bunch of checkboxes:

enter image description here

If you need something better than that, you'll have to let go of the UI, roll up your sleeves, and invest some time in Powershell / SMO.

  • 1
    In SSMS, under your database, highlight the Tables folder in Object Explorer, then go to View > Object Explorer Details. You can sort by schema, highlight all with a shift-click and right-click > Script Table As... from there. Easier than clicking checkboxes. – FilamentUnities Sep 8 '15 at 21:23
  • @Skagalak while true, with Object Explorer Details you'd have to do views separately, and stored procedures separately, and tables separately. I was assuming the OP wanted to perform a single task and have a single script as output, but feel free to add that as an answer. – Aaron Bertrand Sep 8 '15 at 21:27
2

It's a bit out of the box and it's not perfect but here is a way you can do it: Use Tasks -> Generate Scripts and script out the entire database but instead of scripting out all of the objects to a single file you can script them out to individual files (one file per object).

enter image description here

The file names include the schema name in front. That being the case you can sort out the files and copy out the ones you want (or delete the ones you don't).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.