SQL Server - is there option to generate insert script just to not null columns (required columns)?

Question

I have a big table and for tests I would like to generate a script from SQL Server just for these columns. I think there isn't this option... just a full script and I'll need to remove each one.

Just to confirm.

Thanks! who knows.

Answer 1

You can set up the statement querying the system tables - probably more trouble than it's worth, but still doable:

DECLARE @tablename VARCHAR(255) = 'UnprocessedQueueData';
WITH    columns
          AS ( SELECT   STUFF(( SELECT  ',' + c.name
                                FROM    sys.columns c
                                        INNER JOIN sys.tables t ON t.object_id = c.object_id
                                WHERE   t.name = @tablename
                                        AND c.is_nullable = 0
                                        AND c.is_identity = 0
                                ORDER BY c.name
                              FOR
                                XML PATH('')
                              ), 1, 1, '') col
             )
    SELECT  'INSERT INTO ' + t.name + ' ( ' + columns.col
            + ' ) SELECT * FROM OtherTable;'
    FROM    sys.tables t
            CROSS JOIN columns
    WHERE   t.name = @tablename