T-SQL to generate create table script?

  • I know there have to be at least a few out there...

    Does anyone have a T-SQL script to generate the create table statement? It's one of the few operations for which I've always used the GUI. But this time around I have around 1,000 tables to script. That's way too much pointing and clicking for my tastes.

    I did search but apparently my laziness extends to my google-fu today.

  • This should do the trick. All you have to do is load some table variables with the name of tables you want created and the assoicated columns. This is just a basic create table shell, but you can easily customize it to suit your needs.

    SET NOCOUNT ON

    BEGIN TRY

    BEGIN TRANSACTION

    --===================================================

    --Table to hold table names

    --===================================================

    declare @tables table

    (

    id int identity(1,1),

    tablename varchar(25)

    )

    --insert table names into table

    insert into @tables values('Test')

    insert into @tables values('Test2')

    insert into @tables values('Test3')

    --===================================================

    --===================================================

    --Table to hold column names

    --===================================================

    declare @ColumnTable table

    (

    id int,

    TableId int,

    ColName varchar(255),

    ColType varchar(25)

    )

    --====================================================

    --to insert use the following format:

    -- Field Number | TableID | Column Name | Column Type

    --because we have 2 controls for the first table we

    --give the first 1 and the second 2. The second table

    --has 3 controls so we label them 1-3.

    --====================================================

    insert into @ColumnTable Values(1,1,'Field1','varchar(25)')

    insert into @ColumnTable Values(2,1,'Field2','INT')

    insert into @ColumnTable Values(1,2,'Field1','XML')

    insert into @ColumnTable Values(2,2,'Field2','INT')

    insert into @ColumnTable Values(3,2,'Field3','money')

    insert into @ColumnTable Values(1,3,'Field1','varchar(25)')

    insert into @ColumnTable Values(2,3,'Field2','smalldatetime')

    insert into @ColumnTable Values(3,3,'Field3','XML')

    insert into @ColumnTable Values(4,3,'Field4','INT')

    insert into @ColumnTable Values(5,3,'Field5','money')

    --===================================================

    --hold dynamic sql statement

    declare @sql nvarchar(max)

    set @sql = ''

    --hold current table name

    declare @tablename varchar(100)

    set @tablename = ''

    --hold column data

    declare @columns varchar(max)

    set @columns = ''

    --loop through table names

    declare @TableCounter int

    set @Tablecounter = 1

    --loop through columns

    declare @ColCounter int

    --total number of tables to create

    declare @NbrTables int

    --total number of columns to create

    declare @NbrColumns int

    --get the total number of table names

    set @NbrTables = (select count(*) from @tables)

    while @TableCounter <= @NbrTables

    begin

    --reset column counter for loop

    set @ColCounter = 1

    set @columns = ''

    --get table name

    set @tablename = (select tablename from @tables where id = @Tablecounter)

    --get the number of columns for the table being created

    Set @NbrColumns = (select count(ct.id) from @ColumnTable ct where tableid = @tablecounter)

    --while there are more columns add them

    while @ColCounter <= @NbrColumns

    begin

    --if we are inserting the last column they syntax much change - see else

    if @ColCounter <> @NbrColumns

    begin

    set @columns = @columns + (select ColName from @ColumnTable where tableid = @tableCounter and id = @Colcounter) + ' '

    set @columns = @columns + (select ColType from @ColumnTable where tableid = @tableCounter and id = @Colcounter) + ' , '

    end

    else

    begin

    set @columns = @columns + (select ColName from @ColumnTable where tableid = @tableCounter and id = @Colcounter) + ' '

    set @columns = @columns + (select ColType from @ColumnTable where tableid = @tableCounter and id = @Colcounter)

    end

    set @ColCounter = @ColCounter + 1

    end

    --put together sql statement

    set @sql = N'

    create table ' + @tablename + '(

    ' + @columns + '

    )

    '

    --execute sql statement

    execute sp_executesql @sql

    --go to next table

    set @tableCounter = @TableCounter + 1

    end

    PRINT CONVERT(VARCHAR(10),@NbrTables) + ' tables created sucessfully.'

    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

    PRINT 'Error Detected'

    SELECT

    ERROR_NUMBER() ERNumber,

    ERROR_SEVERITY() Error_Severity,

    ERROR_STATE() Error_State,

    ERROR_PROCEDURE() Error_Procedure,

    ERROR_LINE() Error_Line,

    ERROR_MESSAGE() Error_Message

    ROLLBACK TRANSACTION

    END CATCH

    SET NOCOUNT OFF

  • I have a really simple solution, but I'm just to lazy to post it.

  • Any reason you don't want to use the GUI to do the scripting? If you right click on the database name and select Tasks\Generate Scripts you will be in the scripting wizard. Its not as good as it was in 2000 but you can easily script out all or some of the tables or other objects. You have to put all of the scripts in the same file (which is a royal pain if you ask me) but it does a decent job of it.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • I know about the GUI - use it all the time. However, in this case I have 916 out of 3200+ tables to script. That's a lot of pointing and clicking.

    I did find some examples using SMO but my VB is a bit too rusty to be immediately useful.

    I am going to create a script to my likings (and soon), but things are a bit too time-sensitive to rely upon my rusty skills.

  • How to script each table in a separate file ?

  • Use information_schema.columns table you get most of from it

    like table_name,column_name,max of length,is_nullable through which u can generate script easily..

  • I mean from SSMS / database/tasks/generate scripts ?

  • select table_name,column_name,data_type,maximum_character_length,is_nullable from information_schema.columns

    this query gives you details and from them you can generate script as per your requirement..

    but I think u r choosing laborious work.

  • mjarsaniya (6/12/2009)


    Use information_schema.columns table you get most of from it

    like table_name,column_name,max of length,is_nullable through which u can generate script easily..

    In SSMS, right-click on the database, select Tasks.. Generate Scripts..

    In the Script Wizard forms, the next to last one is "Output Option", under "Script to file" is an option to generate a file per object.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • OK, but my question was to generate to separate files per table, I could not find it.

  • SQL Guy (6/12/2009)


    OK, but my question was to generate to separate files per table, I could not find it.

    Yes, that is what it will do. I have it right in front of me now. What version is your SSMS (get it from the About.. window)?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I think the original version of SQL 2005 did not let you script one file per table, but everyone should be on at least SP2 by now.

    Is there a way to permanently change the defaults for table scripting at the beginning of the wizard ?

  • homebrew01 (6/12/2009)


    I think the original version of SQL 2005 did not let you script one file per table, but everyone should be on at least SP2 by now.

    Is there a way to permanently change the defaults for table scripting at the beginning of the wizard ?

    Not sure what you mean by "at the beginning", but AFAIK there is no way to make a script file per object the default.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Why not setup profiler to run while you run the "Generate Script" from Studio? That should give you all the needed pieces. Then you would need to replace the "hard paths", like databasename.owner.table with a variable. You could then put that into a SProc and call sp_msforeachtable.

    /* Anything is possible but is it worth it? */

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

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