Home Forums Article Discussions Article Discussions by Author Discuss content posted by Kaifi Copy Table Structure Without Data Using 1 Line Query RE: Copy Table Structure Without Data Using 1 Line Query

  • April 23, 2010 at 4:33 am

    #1156698

    use tempdb

    go

    create table test (var1 int identity(1,1) constraint pk_test primary key clustered, var2 varchar(100))

    go

    create index ix_test on test(var2)

    go

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    go

    select * into test2 from test where 1 = 2

    go

    sp_help 'test'

    go

    sp_help 'test2'

    go

    print 'bite me'

    edit : Just checked for version behaviour differences, on both sql 2000 and 2008, the PK is lost along with the index, the identity is reseeded. I've not bothered to demonstrate filegroup behaviour because there's not much point. Personally, I only use such an approach mid-batch to create temp objects, not persistent ones.