Is it possible to script a view as a table in SQL Server?

Question

Under the "Script to" options you can choose to script as create to whatever you want. Is there any way to get a matching table structure for a view using a similar method?

Answer 1

Not out of the box. You can do SELECT INTO from the View to create a new empty table then script that as a CREATE TABLE using the usual SSMS methods.

SELECT TOP 0 * INTO NewTable FROM YourView

This might save a bit of typing if that's the motivation for the question.

Answer 2

Here's what we're using to do this.

All credit for the below goes to the top post here and @Zanlok whose revised version I've used and ammended to look at views rather than tables:

    SELECT 
        t.TABLE_CATALOG,
        t.TABLE_SCHEMA,
        t.TABLE_NAME,
        'create table '+QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name) + ' (' + LEFT(o.List, Len(o.List)-1) + ');  ' 
            + CASE WHEN tc.Constraint_Name IS NULL THEN '' 
              ELSE 
                'ALTER TABLE ' + QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name) 
                + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ');  ' 
              END as 'SQL_CREATE_TABLE'
    FROM sysobjects so

    CROSS APPLY (
        SELECT 
              '  ['+column_name+'] ' 
              +  data_type 
              + case data_type
                    when 'sql_variant' then ''
                    when 'text' then ''
                    when 'ntext' 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 exists ( 
                SELECT id 
                FROM syscolumns
                WHERE 
                    object_name(id) = so.name
                    and name = column_name
                    and columnproperty(id,name,'IsIdentity') = 1 
              ) then
                'IDENTITY(' + 
                cast(ident_seed(so.name) as varchar) + ',' + 
                cast(ident_incr(so.name) as varchar) + ')'
              else ''
              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 
            + ','  -- can't have a field name or we'll end up with XML

        FROM information_schema.columns 
        WHERE table_name = so.name
        ORDER BY ordinal_position
        FOR XML PATH('')
    ) o (list)

    LEFT JOIN information_schema.table_constraints tc on  
        tc.Table_name = so.Name
        AND tc.Constraint_Type  = 'PRIMARY KEY'

    LEFT JOIN information_schema.tables t on  
        t.Table_name = so.Name

    CROSS APPLY (
        SELECT QuoteName(Column_Name) + ', '
        FROM information_schema.key_column_usage kcu
        WHERE kcu.Constraint_Name = tc.Constraint_Name
        ORDER BY ORDINAL_POSITION
        FOR XML PATH('')
    ) j (list)

    WHERE
        xtype = 'V'
        AND name NOT IN ('dtproperties')
        -- AND so.name = 'ASPStateTempSessions'
    ;

Answer 3

If you have it installed, you could use the SQL Server Import and Export Wizard. Set your source and destination to the same server, the source being the view, the destination being a new table. This will take the column definitions from the view and create a new table with those definitions.

The obvious downside is that the new table will have the data from the old view, which might be an issue if you have a large number of rows as a result of the view. The easiest way around this and to create a blank table with the definition of the view is to select the option "Write a query to specify the data to transfer" in the wizare and use some code like this:

SELECT * FROM YourView WHERE 1=2

Since 1 does not equal 2, no rows will be returned, but the column definition will be retained, and the new table will be created with that definition.

Hope that helps!

Answer 4

If you mean generate a CREATE TABLE statement based on the columns selected in a View's definition, I'm not aware of any way to do that without intermediate steps, such as what @Martin suggests.

Answer 5

If you have access to SSIS, insert a dataflow. For source, use your view. For destination, use the new table. The entire table will be generated with column and datatypes. If you don't want to transfer data, you can leave it as is. Your table would have already been created when you click Yes to save your dataflow. Now, you can go to the SSMS and generate a script if you wish to see the column names and datatypes for the new table you just generated.