将两个表的列调整为一致

USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
CREATE TABLE a(id INT,[name] NVARCHAR(MAX))
CREATE TABLE b(id INT,[name] NVARCHAR(MAX),age INT,n NVARCHAR(50) NOT NULL)

--将 a 的表结构与 b 的结构调整成一致
DECLARE @sql NVARCHAR(MAX)
SELECT @sql =(
SELECT 'ALTER TABLE a ADD '+c.name+' '+p.name
+CASE WHEN p.name IN ('varchar','char') THEN '('+CAST(c.max_length AS VARCHAR(20))+')'
	  WHEN p.name IN ('nvarchar','nchar') THEN '('+CAST(c.max_length/2 AS VARCHAR(20))+')'
	  ELSE '' END
+CASE WHEN c.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END
+'; '
FROM sys.[columns] AS c 
INNER JOIN sys.types AS p ON c.[object_id]=OBJECT_ID('b') AND c.user_type_id=p.user_type_id
AND c.name NOT IN (
	SELECT c.name FROM sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID('a')
)
FOR XML PATH('')
)
/*
PRINT @sql
ALTER TABLE a ADD age int NULL; ALTER TABLE a ADD n nvarchar(50) NOT NULL; 
*/

EXEC (@sql)
--看表结构可知 a,b 两表的结构已完全一致