You can use below select query to create a table’s ddl script in sql server.
SQL Server ≥ 2017 & Azure SQL
SELECT tmp.TABLE_SCHEMA, tmp.TABLE_NAME, 'TABLE' type_desc,
'CREATE TABLE ' + tmp.TABLE_SCHEMA + '.' + tmp.TABLE_NAME + '(' + CHAR(10) +
tmp.COLUMNS + CHAR(10) +
')' object_defination
FROM (
SELECT TABLE_SCHEMA, TABLE_NAME,
STRING_AGG(
CAST(
(' '+COLUMN_NAME + ' ' + DATA_TYPE) +
(CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+ CONVERT(nvarchar(10), CHARACTER_MAXIMUM_LENGTH) +')' ELSE '' END) +
(CASE WHEN COLLATION_NAME IS NOT NULL THEN ' COLLATE '+ COLLATION_NAME ELSE '' END) +
(CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END)
as NVARCHAR(MAX)
),
','+ CHAR(10)) WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
--WHERE TABLE_SCHEMA = '<Schema name>' AND TABLE_NAME = '<Table name>'
GROUP BY TABLE_SCHEMA, TABLE_NAME
) tmp
SQL Server ≤ 2016
WITH create_table_defination AS (
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, IS_NULLABLE, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
)
SELECT
t0.TABLE_SCHEMA,
t0.TABLE_NAME,
'TABLE' type_desc,
'CREATE TABLE ' + t0.TABLE_SCHEMA + '.' + t0.TABLE_NAME + '(' + CHAR(10) +
REPLACE(STUFF((
SELECT
CAST(',' +
(' ' + t1.COLUMN_NAME + ' ' + t1.DATA_TYPE) +
(CASE WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(nvarchar(10), t1.CHARACTER_MAXIMUM_LENGTH) + ')' ELSE '' END) +
(CASE WHEN t1.COLLATION_NAME IS NOT NULL THEN ' COLLATE ' + COLLATION_NAME ELSE '' END) +
(CASE WHEN t1.IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END)
as NVARCHAR(MAX)
)
FROM create_table_defination t1
WHERE t1.TABLE_SCHEMA = t0.TABLE_SCHEMA AND t1.TABLE_NAME = t0.TABLE_NAME
ORDER BY t1.ORDINAL_POSITION
FOR XML PATH('')), 1, 1, ''),
',',
',' + CHAR(10)) +
CHAR(10)+ ')'
AS object_defination
FROM
create_table_defination t0
GROUP BY
t0.TABLE_SCHEMA, t0.TABLE_NAME