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: https://stackoverflow.com/questions/12140422/generating-sql-code-programtically — Nick.Mc, Aug 30 '17 at 06:49
Possible duplicate of [Generating sql code programtically](https://stackoverflow.com/questions/12140422/generating-sql-code-programtically) — Nick.Mc, Aug 30 '17 at 06:50

score 0 · Answer 1 · answered Mar 13 '14 at 14:10

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 '14 at 14:10

ScubaManDan

809
8
22

How to programmatically create a 'database create script'?

Question

What I've tried

1 Answers1