将两个表的列调整为一致

USE tempdbGOIF OBJECT_ID('a') IS NOT NULL DROP TABLE aIF OBJECT_ID('b') IS NOT NULL DROP TABLE bCREATE 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_idAND c.name NOT IN (SELECT c.name FROM sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID('a'))FOR XML PATH(''))/*PRINT @sqlALTER TABLE a ADD age int NULL; ALTER TABLE a ADD n nvarchar(50) NOT NULL; */EXEC (@sql)--看表结构可知 a,b 两表的结构已完全一致