I'm using SSMS and I was wondering if there is a script that can generate tables creation, constraints, indexes, keys and triggers for a given table names as a single result and another script that will get the INSERT statement for all the data in the tables with the names in a list? I know that I could use right-Click on the database in object explorer, choose Tasks -> Generate Scripts... but I want to do this for multiple tables in the same time, as a script. Is it possible to achieve this?
-
You should see an option to select multiple objects from the database in the wizard you described. You can use SSMS settings to define what gets scripted with a table (indexes, triggers, constraints, etc).– LowlyDBA - John MJan 21, 2019 at 13:29
Add a comment
|
1 Answer
To clarify. You want to generate a script for all objects in a database and then for a select set of tables, have it generate the script to create the data?
Just do it in two steps. One pass with the Generate Scripts as you have described, selecting all the objects you want to generate the create scripts for.
Then do it again, this time for just the tables you want, but make sure to select the Advanced (on the "Set Scripting Options" section) --> Types of data to script == "Data Only"
You can then further select either all scripts to a single file or a single file per object.
If you want to do this on a regular basis (or more dynamcially), you can interact with the Generate Scripts engine through scripts. This is a good article on getting started. https://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/
answered Jan 21, 2019 at 13:31
