locked
create database base on another database .but. without data RRS feed

  • Question

  • Hi :

    I have a database DB1, I want to create another database DB2 having all the tables of DB1 but with NO data. All tables should be blank.

    I have around 80 tables in DB1, can you please suggest me a way do this.

    Thanks

    Wednesday, December 17, 2014 10:48 AM

Answers

All replies

  • use "generate script" option in SSMS.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Proposed as answer by Rishabh K Wednesday, December 17, 2014 11:04 AM
    • Marked as answer by Charlie Liao Wednesday, December 24, 2014 9:49 AM
    Wednesday, December 17, 2014 10:49 AM
  • Check this link. In the advanced options instead of "Schema and Data" just use Schema Only and then you could use the generated script to create new db without data.

    http://blog.sqlauthority.com/2011/05/07/sql-server-2008-2008-r2-create-script-to-copy-database-schema-and-all-the-objects-data-schema-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Rishabh K Wednesday, December 17, 2014 11:04 AM
    • Marked as answer by Charlie Liao Wednesday, December 24, 2014 9:49 AM
    Wednesday, December 17, 2014 11:00 AM
  • 1. Right click on DB1, Script Database as --> Create Database - -> New Query Window
        Set Name='DB2' and FileName (Location of MDF, LDF) as per your requirement and execute script

    2. Right Click on DB1, Tasks --> Generate Scripts --> Tick required Tables and Click Next --> Save script to New Query Window. You get all CREATE TABLE scripts.


    -Vaibhav Chaudhari

    • Marked as answer by Charlie Liao Wednesday, December 24, 2014 9:49 AM
    Wednesday, December 17, 2014 11:10 AM
  • HI, Umesh98

    pls try this

    --create a new database 
    CREATE DATABASE sss_replica
    GO
    
    
    --and then execute bellow script
    --use the source database
    USE [sss]  --★Do
    
    
    DECLARE @fromdb VARCHAR(100)
    DECLARE @todb VARCHAR(100)
    DECLARE @tablename VARCHAR(100)
    DECLARE @columnnames NVARCHAR(max)
    DECLARE @isidentity NVARCHAR(30)
    DECLARE @temsql NVARCHAR(max)
    DECLARE @sql NVARCHAR(max)
    SET @fromdb = 'SSS' --source  database name
    SET @todb = 'sss_replica' --target database name
    
    IF (OBJECT_ID('#MyTempTable') IS NOT NULL)
    drop table #MyTempTable
    
    
    CREATE TABLE #MyTempTable (names varchar(500))
    insert into #MyTempTable
    SELECT name from sys.tables WHERE type='U' AND name not in (select OBJECT_NAME(parent_object_id) 'name' from sys.objects where type='F') 
    
    insert into #MyTempTable
    select OBJECT_NAME(parent_object_id) 'name' from sys.objects where type='F' order by object_id
    
    
    --游标
    DECLARE @itemCur CURSOR
    SET @itemCur = CURSOR FOR 
        SELECT names from #MyTempTable
    
    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @tablename
    WHILE @@FETCH_STATUS=0
    
    BEGIN
    	
    	SET @sql = ''
    
    	PRINT ('--'+@tablename)
    	PRINT ('--表名 '''+@tablename+'''')
    
    
    	--INSERT
    	SET @sql = @sql+'SELECT * INTO ['+@todb+'].[dbo].['+@tablename+']
        FROM ['+@fromdb+'].[dbo].['+@tablename+'] WHERE 1=0'
    
    
    	--返回SQL
    	PRINT(@sql)PRINT('GO')+CHAR(13)
    
        FETCH NEXT FROM @itemCur INTO @tablename
    END 
    
    CLOSE @itemCur
    DEALLOCATE @itemCur
    


    copy the result to the new tab window

    at last,Performing  the copied code

    USE [sss_replica] --use the target database name 
    
    --MSsubscription_agents
    --表名 'MSsubscription_agents'
    SELECT * INTO [sss_replica].[dbo].[MSsubscription_agents]
        FROM [SSS].[dbo].[MSsubscription_agents] WHERE 1=0
    GO
    
    --myTable
    --表名 'myTable'
    SELECT * INTO [sss_replica].[dbo].[myTable]
        FROM [SSS].[dbo].[myTable] WHERE 1=0
    GO
    
    --dba_replicationMonitor
    --表名 'dba_replicationMonitor'
    SELECT * INTO [sss_replica].[dbo].[dba_replicationMonitor]
        FROM [SSS].[dbo].[dba_replicationMonitor] WHERE 1=0
    GO

    you will find the table already lying in the target  database

    thanks

    Wednesday, December 17, 2014 11:35 AM