Tuesday, 6 December 2016

Copy All tables with Data in Another Database

  1. CREATE PROCEDURE [dbo].[uspCreateCopyTables]-- GOALTEST,GOAL     
  2.     -- parameters for the stored procedure here    
  3.     @toDatabase VARCHAR(100)    
  4.     ,@fromDatabase VARCHAR(100)    
  5. AS    
  6. BEGIN    
  7.     -- SET NOCOUNT ON added to prevent extra result sets from    
  8.     SET NOCOUNT ON;    
  9.      
  10.     DECLARE @fullTableList VARCHAR(8000);    
  11.     DECLARE @idx INT;    
  12.     DECLARE @tableName VARCHAR(8000);    
  13.     DECLARE @SQLQuery NVARCHAR(500);    
  14.     DECLARE @ParameterDefinition NVARCHAR(100);    
  15.      
  16.     -- this  query gives the list of table name existing in the database.    
  17.     SELECT @fullTableList = ISNULL(@fullTableList + ',' + TABLE_NAME, TABLE_NAME)    
  18.     FROM INFORMATION_SCHEMA.TABLES    
  19.     WHERE TABLE_TYPE = 'BASE TABLE';    
  20.      
  21.     SELECT @idx = 1    
  22.      
  23.     /* this section splits the table name from comma separated string and copies that table name from      
  24.         one database to another database*/    
  25.     IF LEN(@fullTableList) > 1    
  26.         OR @fullTableList IS NOT NULL    
  27.         WHILE @idx != 0    
  28.         BEGIN    
  29.             SET @idx = CHARINDEX(',', @fullTableList)    
  30.      
  31.             IF @idx != 0    
  32.                 SET @tableName = LEFT(@fullTableList, @idx - 1)    
  33.             ELSE    
  34.                 SET @tableName = @fullTableList    
  35.      
  36.             IF (LEN(@tableName) > 0)    
  37.                 SET @SQLQuery = 'SELECT  * INTO [' + @toDatabase + '].[dbo].[' + @tableName + '] FROM [' + @fromDatabase + '].[dbo].[' + @tableName + ']'    
  38.      
  39.             EXEC (@SQLQuery)    
  40.      
  41.             SET @fullTableList = RIGHT(@fullTableList, LEN(@fullTableList) - @idx)    
  42.      
  43.             IF LEN(@fullTableList) = 0    
  44.                 BREAK    
  45.         END    
  46. END    
  47. GO   

4 comments:

  1. sir G how many table to be create in database

    ReplyDelete
    Replies
    1. Hello Mukesh , Transfer all table (data also) to another database.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete

STEPS TO SAVE YOUR EYES WHILE WORKING ON COMPUTER

STEPS TO SAVE YOUR EYES WHILE WORKING ON COMPUTER This is the report for all you people who works more and more on computer for long t...