CODEWITHSUNDEEP
sql-servert-sql
Nurlan
Nurlan

Reputation: 103

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

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

Upvotes: 1

Views: 4798

Answers (3)

Renan Araújo
Renan Araújo

Reputation: 3641

Right click on Databases, Tasks, Extract Data-tier Application:

enter image description here

After creating the dacpac file, right click on Databases and click on Deploy Data-tier Application

enter image description here

Select the dacpac created and it's done.

Upvotes: 0

PingPongOng
PingPongOng

Reputation: 96

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

Upvotes: 6

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

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.

Upvotes: 0

Related Questions