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
-
Possible duplicate of SQL Server 2012 copy database without data or 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– underscore_d2017-12-07 13:29:34 +00:00Commented Dec 7, 2017 at 13:29
-
Take a look here and hereHoneyBadger– HoneyBadger2017-12-07 13:31:31 +00:00Commented Dec 7, 2017 at 13:31
-
Thanks for your replay HoneyBadger but this is not what I want.I want something like procedure or a functionNurlan– Nurlan2017-12-07 13:37:34 +00:00Commented Dec 7, 2017 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– Dan Bracuk2017-12-07 13:48:40 +00:00Commented Dec 7, 2017 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 dynamicallyNurlan– Nurlan2017-12-07 17:22:38 +00:00Commented Dec 7, 2017 at 17:22
Add a comment
|
3 Answers
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
1 Comment
Nurlan
Thanks, PingPongOng .I think this is exactly what I need.
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.
1 Comment
Nurlan
This is not what I want. Thanks for your help
Right click on Databases, Tasks, Extract Data-tier Application:
After creating the dacpac file, right click on Databases and click on Deploy Data-tier Application
Select the dacpac created and it's done.
Comments
