Fastest way to script out all tables to files? - including everything - constraints, indexes, triggers, extended properties, etc

[chadbaldwin]

I have spent weeks on this trying everything I possibly can, reading every article I can, reading the docs, looking at other code examples, etc. So now I'm going to the source to see if anyone can help me.

I have a database with about 3,800 tables. I'm trying to improve a process I have which scripts out each table and saves that script as a snapshot.

For some reason, this takes forever to run. Typically about 4 hours to script out all 3,800 tables (including constraints, indexes, triggers, etc).

I've tested just about every tip and trick I can find online but maybe I'm just not doing something right. I've messed with...

• SetDefaultInitFields
• PrefetchObjects
• ScriptingOptions
• [Object].Script() vs using the Scripter object

I'm not sure what else to try or if maybe I'm doing something wrong. Looking for any and all advice to get this going faster.

I've also tested other methods outside of SMO...such as using DacFx to extract an entire database schema into files and then grabbing the table files. This actually works quite well...but it does not scale well. I need to run this process for hundreds of databases. It worked okay for 1 database, but not 500. So now I'm back to SMO.

---

tl;dr - I want a fast way to script all tables and all dependent objects - Not necessarily out to files. I would prefer the ability to generate the script in memory so that I can do other things with that script (hash, compress, push to document store, etc)