SqlServer: 根据表头，生成表结构

-- =============================================
-- Author:		yenange
-- Create date: 2021-04-28
-- Description:	根据表头，生成表结构
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[Proc_CreateTableByHeader]
	@columnNames NVARCHAR(MAX)=N'a,b,c'		--数据字符串
	,@tableName  NVARCHAR(MAX)=N't'
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @t TABLE (
		rowNum INT PRIMARY KEY,
		columnName NVARCHAR(MAX)	
	)
	INSERT INTO @t
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)),[value] 
	FROM string_split(@columnNames,',');
	
	DECLARE @i INT,@imax INT,@columnName NVARCHAR(MAX),@sql NVARCHAR(MAX),@cols NVARCHAR(MAX)=''
	SELECT @i=1,@imax=MAX(rowNum) FROM @t
	SET @sql =N'USE tempdb
GO
IF OBJECT_ID(''@tableName'') IS NOT NULL 
	DROP TABLE @tableName
GO
CREATE TABLE @tableName(
@cols	
)
GO
'
	--	
	WHILE @i<=@imax
	BEGIN
		SELECT @columnName = columnName FROM @t WHERE rowNum=@i
		SET @cols= @cols + CASE WHEN @i=1 THEN '' ELSE + '
' END +@columnName+' nvarchar(max)'+CASE WHEN @i=@imax THEN '' ELSE ',' END
		SET @i=@i+1
	END
	--replace
	SET @sql=REPLACE(@sql,'@cols',@cols)
	SET @sql=REPLACE(@sql,'@tableName','dbo.['+@tableName+']')
	PRINT @sql;
END
GO