How to programmatically create a 'database create script'?

Question

Question

In SQL Server Manager 2008/2012 it is possible to create a a complete clone of an existing database.
Is there a way to generate this kind of script programmatically?

[Database] -> Right Click -> Tasks -> Generate Script

What I've tried

I already tried to recreate my database programmatically and delete all rows from all tables. But it seems not to be possible to disable all foreign key constraints. I've found some scripts on SO that can drop and recreate constraints but they won't work with clustered constraints.

-- disable trigger / fk constraints
EXEC sp_MSForEachTable "ALTER TABLE ? DISABLE TRIGGER ALL"
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

-- truncate all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable trigger / constraints
EXEC sp_MSForEachTable "ALTER TABLE ? ENABLE TRIGGER ALL"
EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

Third in my google search: stackoverflow.com/questions/12140422/… — Nick.Mc
– Nick.Mc, Commented Aug 30, 2017 at 6:49

ScubaManDan · Accepted Answer · 2014-03-13 14:10:01Z

0

Not sure what you mean by programatically, but the Generate scripts you mentioned above above should do the job. I will create the script to recreate your database and its schema without any data in it.

answered Mar 13, 2014 at 14:10

ScubaManDan

8399 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to programmatically create a 'database create script'?

Question

What I've tried

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Question

What I've tried

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related