1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nurlan
  • 105
  • 1
  • 10
  • Possible duplicate of [SQL Server 2012 copy database without data](https://stackoverflow.com/questions/21880974/sql-server-2012-copy-database-without-data) or [MS SQL 2008 - Create a copy of the database without the data](https://stackoverflow.com/questions/3912221/ms-sql-2008-create-a-copy-of-the-database-without-the-data) or probably more. If those don't answer your question, presumably because of _"I need some transact SQL script doing this dynamically"_, you should explain what that is supposed to mean. – underscore_d Dec 07 '17 at 13:29
  • Take a look [here](https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query) and [here](https://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table) – HoneyBadger Dec 07 '17 at 13:31
  • Thanks for your replay HoneyBadger but this is not what I want.I want something like procedure or a function – Nurlan Dec 07 '17 at 13:37
  • How about creating another database with no data. Then, when you generate the script, use it to create a stored procedure in this db with no data. You might need some input parameters for the database name and location. – Dan Bracuk Dec 07 '17 at 13:48
  • Thanks, Dan Bracuk this is a great idea but later if I had some changes in the schema of Database I need recreate script and alter my procedure.I don't want this. I want this happen dynamically – Nurlan Dec 07 '17 at 17:22

3 Answers3

6

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

PingPongOng
  • 96
  • 3
0

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.

Katherine Elizabeth Lightsey
  • 853
  • 5
  • 12
0

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.

Renan Araújo
  • 3,533
  • 11
  • 39
  • 49