How to transfer all table schema from one database to another database using stored procedure

See more:

SQL

SQL-Server

database

, +

I want to compare/transfer table schema of all tables of one database(lets say Database2) to another(lets say Database1) and i want this to happen through a stored procedure, could you please help me in it?

For eg. if database 1 has a table employee in which empname column is varchar(3) and database 2 has a table employee in which empname column is varchar(5), then after running the script the empname column of database1 table employee should also become varchar(5). I have written the below stored procedure for the same but dont know whether this will work or not

What I have tried:

CREATE PROCEDURE [dbo].[CompareSchemaScript] ( @ix_sys_error int OUTPUT ) AS BEGIN TRY DECLARE @LogError INT, @PrimaryKey INT,@ErrorMessage nvarchar(2000) EXEC log_ins @LogError, @PrimaryKey,5,1,NULL,1,'Script Started' ALTER SCHEMA Database1.tables TRANSFER Database2.tables;
EXEC log_ins @LogError, @PrimaryKey,7,1,NULL,1,'Script Completed Successfully' END TRY

BEGIN CATCH
SELECT @ix_sys_error = ERROR_NUMBER()
SELECT @ErrorMessage = ERROR_MESSAGE()
EXEC ix_sys_event_log_ins @LogError, @PrimaryKey,6,1,NULL,1,@ErrorMessage,NULL
END CATCH

IF (@ix_sys_error !=0)
RETURN 1
ELSE
RETURN 0
END