Home How to create a database from existing Database without any data?
Reply: 2

How to create a database from existing Database without any data?

Nurlan
1#
Nurlan Published in 2017-12-07 13:25:01Z

I want to create a database from existing one without taking any data from it (only schema). I know, I can create a script for this purpose using SQL Server Management Studio, but I need some transact SQL script doing this dynamically

PingPongOng
2#
PingPongOng Reply to 2017-12-07 15:11:42Z

Try this DBCC command: DBCC CLONEDATABASE https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only

Katherine Elizabeth Lightsey
3#
Katherine Elizabeth Lightsey Reply to 2017-12-07 14:27:23Z

Were I to need to do this, this is the approach I'd use. This is not a trivial task and the built in scripting tools are better suited as has been noted. However, this is the approach I'd use if I had to do it.

Step 1 - Build tables - Build a cursor for all your tables using:

SELECT object_schema_name([object_id]), [name]
FROM   [sys].[objects]
WHERE  [type] IN ( N'U' )
ORDER  BY [name];

Step through each and build dynamic SQL to "SELECT * INTO .. from ..

Step 2 - Build procedures and functions - Build a cursor using:

SELECT [name]
FROM   [sys].[objects]
WHERE  [type] IN ( N'P', N'TF', N'FN', N'IF' )
ORDER  BY [name];

Step through the objects and run "exec sp_helptext '.'" on each. Catenate the resultant lines using a COALESCE string builder and execute the result. Execute the result on your target database.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.33087 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO