select 'create table v_view_name([id] numeric(18) IDENTITY(1,1) NOT NULL,' sqls
union all
SELECT ( ', ' +
'[' + COLUMN_NAME + '] ' +
DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN DATA_TYPE = 'datetime2' THEN '(6)'
WHEN DATA_TYPE = 'decimal' THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
WHEN DATA_TYPE IN ('float', 'real') AND NUMERIC_PRECISION > 0 THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ')'
WHEN DATA_TYPE = 'money' THEN '(20)'
WHEN DATA_TYPE = 'smallmoney' THEN '(10)'
WHEN DATA_TYPE IN ('bigint', 'bit', 'date', 'int', 'smalldatetime', 'smallint', 'time', 'tinyint', 'uniqueidentifier')
OR (DATA_TYPE = 'numeric' AND NUMERIC_PRECISION = 0 AND NUMERIC_SCALE = 0)
THEN ''
ELSE '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ')'
END +
CASE WHEN IS_NULLABLE = 'No' THEN ' NOT NULL' ELSE ' NULL' END +
CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT ' + COLUMN_DEFAULT
WHEN IS_NULLABLE = 'No' AND DATA_TYPE IN ('datetime', 'datetime2', 'smalldatetime', 'timestamp')
THEN ' DEFAULT GETDATE()'
ELSE ''
END) sqls
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'v_view_name'
union all
select ')' sqls
