Post reply

Generate dynamic create table script in SQL Server 2005 stored procedure

  • July 9, 2008 at 3:34 pm

    #189381

    Hi ,

    I would like to pass one table name (ex: sales ) to a procedure. In side the procedure it has to generate create table script for that particular table. Any one knows any stored procedure / query to generate table script ?

    SELECT * INTO SALES2 FROM SALES where 1=0

    will do the job but it will create in the default file group of the database, not in the file group where I want the table to be created..

    Can anyone help?

    Thanks,

    Ganesh

  • July 10, 2008 at 9:13 am

    #840912

    How about something like...

    create procedure dbo.CreateTable

    (

    @TemplateTable varchar(50),

    @NewTable varchar(50),

    @primarykey-2 varchar(50)

    @FileGroup varchar(50)

    )

    as

    declare @SQLCmd as varchar(8000)

    Set @SqlCmd = 'SELECT * INTO ' + @NewTable + ' FROM ' + @TemplateTable + ' where 1=0'

    exec (@SqlCmd)

    --temporarily create unique CLUSTERED index

    Set @SqlCmd = 'Alter table ' + @NewTable + '

    add constraint PK_' + @primarykey-2 + ' primary key clustered (' + @primarykey-2 + ')'

    exec (@SqlCmd)

    --Use index to move data

    Set @SqlCmd = 'CREATE UNIQUE CLUSTERED INDEX PK_' + @primarykey-2 + ' ON ' + @NewTable + '(' + @primarykey-2 + ') WITH DROP_EXISTING ON ' + @FileGroup

    exec (@SqlCmd)

    --You could drop the PK constraint if desired now

    See also http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/11/881.aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply