Generate sql string - create table with additional columns if necessary

[dbo].[gsp_generate_table_desc].sql

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[gsp_generate_table_desc]
@tablename SYSNAME,
@db_name SYSNAME,
@create_with_identity BIT = 1, 
@table_prefix VARCHAR(20) = '',
@Additional_attr VARCHAR(1000) = '', 
@debug BIT = 0
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT,
	    QUOTED_IDENTIFIER,
	    ANSI_NULLS,
	    ANSI_PADDING,
	    ANSI_WARNINGS,
	    ARITHABORT,
	    CONCAT_NULL_YIELDS_NULL ON;
	SET NUMERIC_ROUNDABORT OFF;
	
	BEGIN TRY
		DECLARE @sql_cmd          NVARCHAR(4000),
		        @str_identity     NVARCHAR(1000) = '';
		
		SET @sql_cmd = 'USE ' + @db_name + ';';
		
		IF @create_with_identity = 1
		BEGIN
		    SELECT @str_identity = 'CASE  ' + CHAR(10)
		           + '                        WHEN EXISTS ( ' + CHAR(10)
		           + '                                 SELECT id ' + CHAR(10)
		           + '                                 FROM   syscolumns ' + CHAR(10)
		           + '                                 WHERE  OBJECT_NAME(id) = so.name ' + CHAR(10)
		           + '                                        AND NAME = column_name ' + CHAR(10)
		           + '                                        AND COLUMNPROPERTY(id ,NAME ,''IsIdentity'') = 1 ' + CHAR(10)
		           + '                             ) THEN ''IDENTITY(''+ ' + CHAR(10)
		           + '                             CAST(IDENT_SEED(so.name) AS VARCHAR)+'',''+ ' + CHAR(10)
		           + '                             CAST(IDENT_INCR(so.name) AS VARCHAR)+'')'' ' + CHAR(10)
		           + '                        ELSE '''' ' + CHAR(10)
		           + '                   END+';
		END;
		
		SET @sql_cmd = @sql_cmd + 'SELECT ''create table [''+''' + @table_prefix + '''+so.name+''] (''+o.list+''' +
		    @Additional_attr +
		    ')'' ' + CHAR(10)
		    + '        FROM   sysobjects so ' + CHAR(10)
		    + '               CROSS APPLY ( ' + CHAR(10)
		    + '            SELECT ''  [''+column_name+''] ''+ ' + CHAR(10)
		    + '                   data_type+ ' + CHAR(10)
		    + '                   CASE data_type ' + CHAR(10)
		    + '                        WHEN ''sql_variant'' THEN '''' ' + CHAR(10)
		    + '                        WHEN ''text'' THEN '''' ' + CHAR(10)
		    + '                        WHEN ''ntext'' THEN '''' ' + CHAR(10)
		    + '                        WHEN ''xml'' THEN '''' ' + CHAR(10)
		    +
		    '                        WHEN ''decimal'' THEN ''(''+CAST(numeric_precision AS VARCHAR)+'', ''+CAST(numeric_scale AS VARCHAR)+ ' 
		    + CHAR(10)
		    + '                             '')'' ' + CHAR(10)
		    + '                        ELSE COALESCE( ' + CHAR(10)
		    + '                                 ''(''+ ' + CHAR(10)
		    + '                                 CASE  ' + CHAR(10)
		    + '                                      WHEN character_maximum_length=-1 THEN ''MAX'' ' + CHAR(10)
		    + '                                      ELSE CAST(character_maximum_length AS VARCHAR) ' + CHAR(10)
		    + '                                 END+'')'' ' + CHAR(10)
		    + '                                ,'''' ' + CHAR(10)
		    + '                             ) ' + CHAR(10)
		    + '                   END+'' ''+ ' + CHAR(10)
		    + '                ' + @str_identity +
		    '   '' '' + (CASE WHEN IS_NULLABLE=''No'' THEN ''NOT '' ELSE '''' END)+''NULL ''+ ' + CHAR(10)
		    + '                   CASE  ' + CHAR(10)
		    + '                        WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ''DEFAULT ''+ ' +
		    CHAR(10)
		    + '                             information_schema.columns.COLUMN_DEFAULT ' + CHAR(10)
		    + '                        ELSE '''' ' + CHAR(10)
		    + '                   END+'', '' ' + CHAR(10)
		    + '            FROM   information_schema.columns ' + CHAR(10)
		    + '            WHERE  table_name = so.name ' + CHAR(10)
		    + '            ORDER BY ' + CHAR(10)
		    + '                   ordinal_position ' + CHAR(10)
		    + '                   FOR XML PATH('''') ' + CHAR(10)
		    + '        ) o(list) ' + CHAR(10)
		    + '        WHERE  xtype = ''U'' ' + CHAR(10)
		    + '               AND NAME = ''' + @tablename + '''';
		
		IF @debug = 1
		    SELECT @sql_cmd;
		
		EXEC (@sql_cmd);
	END TRY
	
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		
		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity     = ERROR_SEVERITY(),
		       @ErrorState        = ERROR_STATE();
		
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH;
END;

GO

Example invoke:
EXEC generate_table_desc @tablename='NAMECHECK',
@db_name = 'staging_namecheck',
@create_with_identity = 0
,@table_prefix='arch_data_'
Or with a prefix, we'll get a temporary table #ModelImpl
EXEC generate_table_desc @tablename = 'ModelImpl',
@db_name = 'plib_namecheck',
@table_prefix='#',
@create_with_identity = 0,
@Additional_attr =
'LoggingOnly int,[SESSION_USER] nvarchar(128),
[SYSTEM_USER] nvarchar(128), [EventDate] datetime,
RowNum int identity(1, 1), LogID UNIQUEIDENTIFIER'

/************************************************************

• Code formatted by SoftTree SQL Assistant © v11.2.246
• Time: 9/15/2021 1:47:52 AM
  ************************************************************/

USE tempdb;
SELECT 'create table [' + '' + so.name + '] (' + o.list + ')'
FROM sysobjects so
CROSS APPLY (
SELECT ' [' + column_name + '] ' + data_type + CASE data_type
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
WHEN 'xml' THEN ''
WHEN 'decimal' THEN '(' + CAST(numeric_precision AS VARCHAR) +
', ' + CAST(numeric_scale AS VARCHAR) + ')'
ELSE COALESCE(
'(' + CASE
WHEN character_maximum_length = -1 THEN
'MAX'
ELSE CAST(character_maximum_length AS VARCHAR)
END + ')',
''
)
END + ' ' + ' ' + (CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END)
+ 'NULL ' + CASE
WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT ' +
information_schema.columns.COLUMN_DEFAULT
ELSE ''
END + ', '
FROM information_schema.columns
WHERE table_name = so.name
ORDER BY
ordinal_position FOR XML PATH('')
) o(list)
WHERE xtype = 'U'