Category: SQL SERVER

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