SQLServer根据视图名称生成创建表语句

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