This script creates an external table using the schema of a normal table. Give this proc the name/schema of the table, the location/file format for the data and it will create an external table using these details.

create_external_table.sql

create PROC [dbo].[create_external_table_from_table] @tbname [varchar](200),@schema [varchar](200),@storageacc [varchar](200),
@datafolder [varchar](400),@fileformat [varchar](400),@external_Table_Name [varchar](200),@external_Table_Schema_Name [varchar](200) AS
BEGIN
    SET NOCOUNT ON
/*
This proc creates an external table based on the shape of the table we want to import into.

Usage:
exec [dbo].[create_external_table_from_table]] 'tablename','schema','MyAzureStorage','/path/path/file.csv','FormatCSV','external_tbl_name','ext_schema'

@tbname [varchar](200),  -- Table we wish to load into , i.e. 'sales'
@schema [varchar](200),  -- schema we wish to load into, i.e. 'dbo'

@storageacc [varchar](200) External Data Source name i.e. 'MyAzureStorage'
--https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest
--CREATE EXTERNAL DATA SOURCE [MyAzureStorage] WITH (TYPE = HADOOP, LOCATION = N'wasbs://[email protected]/', CREDENTIAL = [AzureStorageCredential])

@datafolder [varchar](400), -- Path to the folders or file we want to import, i.e. '/folder/folder/file.csv' or '/folder/' 

@fileformat [varchar](400),
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=azure-sqldw-latest

*/
declare @folderpath [varchar](700)
declare @procname varchar(200)
declare @rcount bigint;
set @procname = 'create_external_table_from_table'

declare @Rundate datetime2
set @Rundate = getdate()

    -- Insert statements for procedure here
 DECLARE @intColumnCount  INT, 
        @intProcessCount INT, 
        @varColList      VARCHAR(max) ,
		@varFileColList      VARCHAR(max) ,
		@varFileColListwithDateTypes      VARCHAR(max) ,
		@varDestColList      VARCHAR(max) ,
		@varSQL VARCHAR(max),
		@varExtTableName VARCHAR(max),
		@colaname VARCHAR(max),
		@colanamereplace VARCHAR(max),
		@thiscol VARCHAR(max),
		@actualcol VARCHAR(max),
		@indent int

SET @varColList = '' 
SET @varFileColList = ''
SET @varFileColListwithDateTypes = ' '
SET @varDestColList = ' '
SET @colaname = ''
SET @colanamereplace = ''
set @actualcol = ''

set @varExtTableName= @external_Table_Name

IF Object_id('tempdb.dbo.#tempColumnNames') IS NOT NULL 
  BEGIN 
      DROP TABLE #tempcolumnnames; 
  END 

CREATE TABLE #tempcolumnnames 
  ( 
     intid          INT, 
     colname VARCHAR(512) ,
	 colaname VARCHAR(512) ,
	 actualcol VARCHAR(512)
  ) 


BEGIN

	INSERT INTO #tempcolumnnames 
		   select  c.column_id,  c.NAME + ' varchar(4000)' , c.NAME as colaname, '[' + c.NAME + '] ' +
                CASE WHEN c.system_type_id != c.user_type_id   
                    THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'   
                    ELSE '[' + UPPER(tp.name) + ']'   
                END  +   
                CASE   
                    WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
                        THEN '(' + CASE WHEN c.max_length = -1   
                                        THEN 'MAX'   
                                        ELSE CAST(c.max_length AS VARCHAR(5))   
                                    END + ')'  
                    WHEN tp.name IN ('nvarchar', 'nchar')  
                        THEN '(' + CASE WHEN c.max_length = -1   
                                        THEN 'MAX'   
                                        ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                                    END + ')'  
                    WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
                        THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
                    WHEN tp.name = 'decimal'  
                        THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
                    ELSE ''  
                END as columnss
               
    FROM sys.columns c WITH(NOLOCK)  
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
	inner join sys.tables t on t.object_id = c.object_id 
	  inner join sys.schemas s on s.schema_id = t.schema_id 
			   where t.name = @tbname
		   and s.name = @schema 
 


	select * from #tempcolumnnames 
	SET @intProcessCount = 1 
	SET @intColumnCount = (SELECT Count(*) 
						   FROM   #tempcolumnnames) 

	WHILE ( @intProcessCount <= @intColumnCount ) 
	  BEGIN 

	   SELECT @thiscol = colaname
							   FROM   #tempcolumnnames 
							   WHERE  intid = @intProcessCount 

		  SET @varFileColList = @varFileColList + ',
		  ' 
							+ (SELECT colname
							   FROM   #tempcolumnnames 
							   WHERE  intid = @intProcessCount) 
					

set @colaname = @colaname + ',
		  ' 
							+ (SELECT colaname
							   FROM   #tempcolumnnames 
							   WHERE  intid = @intProcessCount) 

set @actualcol = @actualcol + ',
		  ' 
							+ (SELECT actualcol
							   FROM   #tempcolumnnames 
							   WHERE  intid = @intProcessCount) 

SET @colanamereplace= @colanamereplace + ',  
  case ' + @thiscol + '
  WHEN ''NULL'' THEN NULL
  ELSE ' + @thiscol + ' END as ' + @thiscol + ''

		  SET @intProcessCount +=1 
	  END 

	set @folderpath  = @datafolder

	set @varFileColList = substring( @varFileColList,2, len(@varFileColList))
	set @colaname = substring( @colaname,2, len(@colaname))
	set @colanamereplace =  substring( @colanamereplace,2, len(@colanamereplace))
	set @actualcol =  substring( @actualcol,2, len(@actualcol))

	print @varFileColList
	print @actualcol;
	set @varFileColList = @actualcol;


		set @varSQL = '
			 if exists(select * from  sys.tables t 
		   inner join sys.schemas s on s.schema_id = t.schema_id where t.name ='''+ @external_Table_Name + '''
		   and s.name = ''' + @external_Table_Schema_Name  + ''')
		   begin
			 drop external table [' + @external_Table_Schema_Name  + '].[' + @external_Table_Name  + ']
		   end
		   else
			begin
			print ''y''
		   end

		CREATE EXTERNAL TABLE [' + @external_Table_Schema_Name  + '].[' + @external_Table_Name  + ']
	( ' + @varFileColList + ' )
	WITH (
	DATA_SOURCE = [' + @storageacc  + '],
	LOCATION = N'''+ @folderpath + ''',
	FILE_FORMAT = [' + @fileformat + '],    
	REJECT_TYPE = VALUE,
	REJECT_VALUE = 99999);
	'

	PRINT 'Create External table'
	print @varSQL
	
	begin try
	EXEC(@varSQL)
	end try
	begin catch
	print ERROR_MESSAGE();
	end catch

end
end

making public.