Geek Answers Handbook

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

In the "Script to" options, you can choose a script to create whatever you want. Is there a way to get the appropriate table structure for presentation using a similar method?

+5
source share
5 answers

You can make a SELECT INTOview into a new table, and then a script.

SELECT TOP 0 * INTO NewTable FROM YourView

You can save a little input if this is the motivation for the question.

+28
source

Here is what we use for this.

@Zanlok, , , , :



    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'
    ;
+7

CREATE TABLE , , , , , @Martin.

+2

, SQL Server. , , - . .

, , , . - " , " wizare, :

SELECT * FROM YourView WHERE 1=2

1 2, , , .

, !

+2

If you have access to SSIS, insert the data stream. Use your view for the source. Use the new table for the assignment. The entire table will be created using columns and data types. If you do not want to transfer data, you can leave it as is. Your table would be created when you click Yes to save the data stream. Now you can go to SSMS and generate a script if you want to see the column names and data types for the newly generated new table.

0
source

More articles:


All Articles