Copy All tables with Data in Another Database

CREATE PROCEDURE [dbo].[uspCreateCopyTables]-- GOALTEST,GOAL
-- parameters for the stored procedure here
@toDatabase VARCHAR(100)
,@fromDatabase VARCHAR(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
DECLARE @fullTableList VARCHAR(8000);
DECLARE @idx INT;
DECLARE @tableName VARCHAR(8000);
DECLARE @SQLQuery NVARCHAR(500);
DECLARE @ParameterDefinition NVARCHAR(100);
-- this query gives the list of table name existing in the database.
SELECT @fullTableList = ISNULL(@fullTableList + ',' + TABLE_NAME, TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
SELECT @idx = 1
/* this section splits the table name from comma separated string and copies that table name from
one database to another database*/
IF LEN(@fullTableList) > 1
OR @fullTableList IS NOT NULL
WHILE @idx != 0
BEGIN
SET @idx = CHARINDEX(',', @fullTableList)
IF @idx != 0
SET @tableName = LEFT(@fullTableList, @idx - 1)
ELSE
SET @tableName = @fullTableList
IF (LEN(@tableName) > 0)
SET @SQLQuery = 'SELECT * INTO [' + @toDatabase + '].[dbo].[' + @tableName + '] FROM [' + @fromDatabase + '].[dbo].[' + @tableName + ']'
EXEC (@SQLQuery)
SET @fullTableList = RIGHT(@fullTableList, LEN(@fullTableList) - @idx)
IF LEN(@fullTableList) = 0
BREAK
END
END
GO

Up Next