Update all tables schema from another database

Question

I have two databases - my old database as DB1, and my new and edited database as DB2.

I need a query to search in all tables in DB2 and find what is new, then update columns schema in DB1 based on DB2.

How can I do that with queries (not Management Studio)?

I don't have any idea about that.

Answer 1

I tried to d that by this steps and I hope this answer help you . 1- Capture All Columns in DB2 by this procedure .

USE [LastDatabase]
GO

/****** Object:  StoredProcedure [dbo].[CaptureMyColumns]    Script Date: 01/10/2022 05:33:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create proc [dbo].[CaptureMyColumns]
as
IF OBJECT_ID(N'dbo.LastColumns', N'U') IS NOT NULL  
begin 
   DROP TABLE [dbo].[LastColumns];  
 SELECT    ROW_NUMBER() OVER (
    ORDER BY T.name
   ) id, T.name AS Table_Name ,
       C.name AS Column_Name ,
       P.name AS Data_Type ,
      c.collation_name ,
     p.is_nullable ,
c.max_length,object_definition(c.default_object_id)[defaultValue],c.is_identity 
into newcolumns
FROM   sys.objects AS T
       JOIN sys.columns AS C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE' and p.name <> 'sysname'
end
else 
begin 
 SELECT    ROW_NUMBER() OVER (
    ORDER BY T.name
   ) id, T.name AS Table_Name ,
       C.name AS Column_Name ,
       P.name AS Data_Type ,
      c.collation_name ,## Heading ##
     p.is_nullable ,
c.max_length,object_definition(c.default_object_id)[defaultValue],c.is_identity 
FROM   sys.objects AS T
       JOIN sys.columns AS*strong text* C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE' and p.name <> 'sysname'
end 


GO

2- generate Script only Check this Table [LastColumns] (Schema And Data) from DB2 [New Database] and execute this script in DB1 (old database) script will be like this according your Database [last database]

CREATE TABLE [dbo].[Lastcolumns](
[id] [bigint] NULL,
[Table_Name] [sysname] NOT NULL,
[Column_Name] [sysname] NULL,
[Data_Type] [sysname] NOT NULL,
[collation_name] [sysname] NULL,
[is_nullable] [bit] NULL,
[max_length] [smallint] NOT NULL,
[defaultValue] [nvarchar](max) NULL,
[is_identity] [bit] NOT NULL

) ON [PRIMARY] GO

INSERT [dbo].[newcolumns] ([id], [Table_Name], [Column_Name], [Data_Type], [collation_name], [is_nullable], [max_length], [defaultValue], [is_identity]) VALUES (1, N'accounts', N'id', N'int', NULL, 1, 4, NULL, 1)

3 - in Database to upgrade

Right Click on you Last Updated database and generate scripts:

Don't forget If not exist option:

3 - now you can add columns if not found in old DB by this script

DECLARE @Counter INT 
SET @Counter=1
DECLARE @Counter2 INT 
SET @Counter2=1
DECLARE @tbCount INT=(select COUNT(0) from newColumns )
WHILE ( @Counter <= @tbCount)
BEGIN
declare @tb_name nvarchar(50)=(select table_name from newColumns where id=@counter);
declare @col_name nvarchar(50)=(select column_name from newColumns where id=@counter);
declare @col_typ nvarchar(50)=(select 
case when data_type='nvarchar' then 'nvarchar('+convert(nvarchar(50),max_length)+')'
 when data_type='char' then 'char('+convert(nvarchar(50),max_length)+')'
else data_type end [ee]

 from newColumns where id=@counter);
 
declare @nu nvarchar(50)=(select case when is_nullable='False' then 'not null default ' else 'null' end [es] from newColumns where id=@counter);
declare @defaultValue nvarchar(50)=(select defaultValue from newColumns where id=@counter);

IF not EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].['+@tb_name+']') 
         AND name = @col_name
)
BEGIN
exec ( 'ALTER TABLE '+@tb_name+' ADD '+@col_name+' '+
 @col_typ +' '+ @nu +' '+ @defaultValue +';')
print ( 'ALTER TABLE '+@tb_name+' ADD '+@col_name+' '+
 @col_typ +' '+ @nu +' '+ @defaultValue +';') 
 
  SET @Counter2  = @Counter2  + 1 
END


    SET @Counter  = @Counter  + 1
END
   print  'End Of Loop'
  

4 - finally Drop and Create al views and function by generate script from Last DB and execute it in old DB

Check all but Tables:

Drop And Create:

======================= this may not perfect way but it usually help me effectively

Thank you,,