locked
generate table script RRS feed

  • Question

  • hi, 


    I'm familiar with sp_helptext , you can view the content of procedure scripts.
    I wonder in Sql 2005,  is there a microsoft procedure that I can use to view the table script "create table ...."
    I know I can right click on the object explorer to generate table script, but I don't know if it's possible to do it in the script level.

    for example:

    exec sp_helptabletext <tablename>, <showindexscript>=Y


    Thanks





    Thursday, May 7, 2009 5:04 PM

Answers

  • You can use SSMO to generate a create script for a table pretty easily...or you can use the following script (which I don't vouch for btw):

    declare @table varchar(100)
    set @table = 'MyTable' -- set your table name here
    declare @sql table(s varchar(1000), id int identity)
    
    insert into  @sql(s) values ('create table [' + @table + '] (')
    insert into @sql(s)
    select 
        '  ['+column_name+'] ' + 
        data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
        case when exists ( 
            select id from syscolumns
            where object_name(id)=@table
            and name=column_name
            and columnproperty(id,name,'IsIdentity') = 1 
        ) then
            'IDENTITY(' + 
            cast(ident_seed(@table) as varchar) + ',' + 
            cast(ident_incr(@table) as varchar) + ')'
        else ''
        end + ' ' +
        ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
        coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
    
     from information_schema.columns where table_name = @table
     order by ordinal_position
    
    declare @pkname varchar(100)
    select @pkname = constraint_name from information_schema.table_constraints
    where table_name = @table and constraint_type='PRIMARY KEY'
    
    if ( @pkname is not null ) begin
        insert into @sql(s) values('  PRIMARY KEY (')
        insert into @sql(s)
            select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
            where constraint_name = @pkname
            order by ordinal_position
        -- remove trailing comma
        update @sql set s=left(s,len(s)-1) where id=@@identity
        insert into @sql(s) values ('  )')
    end
    else begin
        update @sql set s=left(s,len(s)-1) where id=@@identity
    end
    
    insert into @sql(s) values( ')' )
    select s from @sql order by id
    

    masher
    • Proposed as answer by masher2 Thursday, May 7, 2009 5:51 PM
    • Marked as answer by sqlmania Friday, May 8, 2009 9:24 PM
    Thursday, May 7, 2009 5:21 PM
  • NewWorld,

    To the original question. There is no easy way to script a tables definition programatically.  You can use sample scripts but a lot of these have to be adjusted.  For example, the TSQL script posted does not account for new data types, like XML, Spatial etc.  So it tries to create an XML index with a length of -1.  You will need to add a case statement to resolve this issue.  Don't get me wrong a table generator script is not a bad thing, you just have to make sure you get it working properly, for your environment. 

    You do have a method to tell if an index uses include; however, you cant obtain it as simply as using sp_helpindex or sp_help.  You will have to query the metadata and specifically look for the column of data that has includes.  I have posted a script on my blog that generates all indexes including xml should you want to look at it.

    Here is a simple way to get indexes with inlcude.

    SELECT i.* 
    
    FROM sys.indexes i
    
    INNER JOIN sys.[index_columns] ic 
    
    ON ic.[index_id] = i.[index_id] AND 
    
       ic.[object_id] = i.[object_id]
    
    WHERE
    
        ic.[is_included_column] = 1

    Here is a link to my blog.
    http://jahaines.blogspot.com/

    I hope this helps.

    • Edited by Adam Haines Thursday, May 7, 2009 6:08 PM syntax
    • Marked as answer by sqlmania Friday, May 8, 2009 9:23 PM
    Thursday, May 7, 2009 6:07 PM
  • You can script with DMO procedures 



    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_genscript]') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[proc_genscript]
    GO
    
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    /****** Object:  Stored Procedure dbo.proc_genscript    Script Date: 5/8/2003 11:06:53 AM ******/
    
    CREATE PROCEDURE proc_genscript 
    	@ServerName varchar(30), 
    	@DBName varchar(30), 
    	@ObjectName varchar(50), 
    	@ObjectType varchar(10), 
    	@TableName varchar(50),
    	@ScriptFile varchar(255)
    AS
    
    DECLARE @CmdStr varchar(255)
    DECLARE @object int
    DECLARE @hr int
    
    SET NOCOUNT ON
    SET @CmdStr = 'Connect('+@ServerName+')'
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    
    --Comment out for standard login
    EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
    
    /* Uncomment for Standard Login
    EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
    EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
    */
    
    EXEC @hr = sp_OAMethod @object,@CmdStr
    SET @CmdStr = 
      CASE @ObjectType
        WHEN 'Database' 	THEN 'Databases("' 
        WHEN 'Procedure'	THEN 'Databases("' + @DBName + '").StoredProcedures("'
        WHEN 'View' 	THEN 'Databases("' + @DBName + '").Views("'
        WHEN 'Table'	THEN 'Databases("' + @DBName + '").Tables("'
        WHEN 'Index'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
        WHEN 'Trigger'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
        WHEN 'Key'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
        WHEN 'Check'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
        WHEN 'Job'	THEN 'Jobserver.Jobs("'
      END
    
    SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
    EXEC @hr = sp_OAMethod @object, @CmdStr
    EXEC @hr = sp_OADestroy @object
    GO
    
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    --Run this query results to text and copy paste output to new query window and run.
    ----For table
    select 'exec proc_genscript ''' + @@SERVERNAME  + ''',''' + DB_NAME()  + ''','''
    + table_schema + '.' + table_name + ''','  
    + '''Table'',''' + table_name + ''',''' + 'c:\table\' + table_name + '.sql ''' + CHAR(10) + 'GO' + CHAR(10)
     from information_schema.tables
     
    go
    --You have to enable Ole Automation procedure
    
    sp_configure 'show advanced option',1
    reconfigure
    go
    sp_configure 'Ole Automation Procedures',1
    reconfigure
    go
    --Disable Ole Automation Procedure after running script.
    Link


    No idea what this script does but i have used this script and it works..

    Thanks,
    Nimit
    • Marked as answer by sqlmania Friday, May 8, 2009 9:24 PM
    Thursday, May 7, 2009 6:49 PM
  • I have my own version of sp_helpindex: sp_indexinfo:
    http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp
    Tibor Karaszi
    • Marked as answer by sqlmania Friday, May 8, 2009 9:40 PM
    Friday, May 8, 2009 5:40 AM

All replies

  • I'm afriaid not. The CREATE TABLE command is not stored as text anywhere. SSMS script functionality uses code in SMO, something you can do as well, from for instance C#.
    Tibor Karaszi
    Thursday, May 7, 2009 5:13 PM
  • hmm... so there is no way?  I thought we can get these text from meta data or something.
    maybe sql 2008 can do that?

     

    Thursday, May 7, 2009 5:20 PM
  • Hi newworld,

    you can use scptxfr (a command line tool) to generate script
    look the link:
    http://www.sqlservercentral.com/articles/Administration/howtoscheduleasqlserverdatabasecreationscript/1834/

    att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    • Proposed as answer by masher2 Thursday, May 7, 2009 5:51 PM
    Thursday, May 7, 2009 5:20 PM
  • You can use SSMO to generate a create script for a table pretty easily...or you can use the following script (which I don't vouch for btw):

    declare @table varchar(100)
    set @table = 'MyTable' -- set your table name here
    declare @sql table(s varchar(1000), id int identity)
    
    insert into  @sql(s) values ('create table [' + @table + '] (')
    insert into @sql(s)
    select 
        '  ['+column_name+'] ' + 
        data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
        case when exists ( 
            select id from syscolumns
            where object_name(id)=@table
            and name=column_name
            and columnproperty(id,name,'IsIdentity') = 1 
        ) then
            'IDENTITY(' + 
            cast(ident_seed(@table) as varchar) + ',' + 
            cast(ident_incr(@table) as varchar) + ')'
        else ''
        end + ' ' +
        ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
        coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
    
     from information_schema.columns where table_name = @table
     order by ordinal_position
    
    declare @pkname varchar(100)
    select @pkname = constraint_name from information_schema.table_constraints
    where table_name = @table and constraint_type='PRIMARY KEY'
    
    if ( @pkname is not null ) begin
        insert into @sql(s) values('  PRIMARY KEY (')
        insert into @sql(s)
            select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
            where constraint_name = @pkname
            order by ordinal_position
        -- remove trailing comma
        update @sql set s=left(s,len(s)-1) where id=@@identity
        insert into @sql(s) values ('  )')
    end
    else begin
        update @sql set s=left(s,len(s)-1) where id=@@identity
    end
    
    insert into @sql(s) values( ')' )
    select s from @sql order by id
    

    masher
    • Proposed as answer by masher2 Thursday, May 7, 2009 5:51 PM
    • Marked as answer by sqlmania Friday, May 8, 2009 9:24 PM
    Thursday, May 7, 2009 5:21 PM
  • You can use SSMO to generate a create script for a table pretty easily...or you can use the following script (which I don't vouch for btw):

    declare @table varchar(100)
    
    
    
    set @table = 'MyTable' -- set your table name here
    
    
    
    declare @sql table(s varchar(1000), id int identity)
    
    
    
    
    
    
    
    insert into  @sql(s) values ('create table [' + @table + '] (')
    
    
    
    insert into @sql(s)
    
    
    
    select 
    
    
    
        '  ['+column_name+'] ' + 
    
    
    
        data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    
    
    
        case when exists ( 
    
    
    
            select id from syscolumns
    
    
    
            where object_name(id)=@table
    
    
    
            and name=column_name
    
    
    
            and columnproperty(id,name,'IsIdentity') = 1 
    
    
    
        ) then
    
    
    
            'IDENTITY(' + 
    
    
    
            cast(ident_seed(@table) as varchar) + ',' + 
    
    
    
            cast(ident_incr(@table) as varchar) + ')'
    
    
    
        else ''
    
    
    
        end + ' ' +
    
    
    
        ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    
    
    
        coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
    
    
    
    
    
    
    
     from information_schema.columns where table_name = @table
    
    
    
     order by ordinal_position
    
    
    
    
    
    
    
    declare @pkname varchar(100)
    
    
    
    select @pkname = constraint_name from information_schema.table_constraints
    
    
    
    where table_name = @table and constraint_type='PRIMARY KEY'
    
    
    
    
    
    
    
    if ( @pkname is not null ) begin
    
    
    
        insert into @sql(s) values('  PRIMARY KEY (')
    
    
    
        insert into @sql(s)
    
    
    
            select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
    
    
    
            where constraint_name = @pkname
    
    
    
            order by ordinal_position
    
    
    
        -- remove trailing comma
    
    
    
        update @sql set s=left(s,len(s)-1) where id=@@identity
    
    
    
        insert into @sql(s) values ('  )')
    
    
    
    end
    
    
    
    else begin
    
    
    
        update @sql set s=left(s,len(s)-1) where id=@@identity
    
    
    
    end
    
    
    
    
    
    
    
    insert into @sql(s) values( ')' )
    
    
    
    select s from @sql order by id
    
    
    
    

    masher
    hmmm...... You don't vouch for that? you mean it doesn't work sometimes?
    I think it would be great if your script can actually return indexes script as well.



    Thursday, May 7, 2009 5:28 PM
  • You can create your own sp_helptabletext as a CLR stored procedure. I'm not sure if you can use the context connection with SMO.

    Thursday, May 7, 2009 5:30 PM
  • It works, but there may be some odd combination of table/column attributes that it doesn't capture correctly.  Verify it works well in your own environment.
    masher
    Thursday, May 7, 2009 5:32 PM
  • btw,  i have a question about the index. 

    SQL 2005 have a new feature in index "INCLUDES", I like it a lot.

    However, when I do sp_help "tablename"  or sp_helpindex "tablename",
    it doesn't return the "include" column.. So I have no way to tell which indexes have INCLUDE features.

    anyone have the same problem?

    Thursday, May 7, 2009 5:38 PM
  • NewWorld,

    To the original question. There is no easy way to script a tables definition programatically.  You can use sample scripts but a lot of these have to be adjusted.  For example, the TSQL script posted does not account for new data types, like XML, Spatial etc.  So it tries to create an XML index with a length of -1.  You will need to add a case statement to resolve this issue.  Don't get me wrong a table generator script is not a bad thing, you just have to make sure you get it working properly, for your environment. 

    You do have a method to tell if an index uses include; however, you cant obtain it as simply as using sp_helpindex or sp_help.  You will have to query the metadata and specifically look for the column of data that has includes.  I have posted a script on my blog that generates all indexes including xml should you want to look at it.

    Here is a simple way to get indexes with inlcude.

    SELECT i.* 
    
    FROM sys.indexes i
    
    INNER JOIN sys.[index_columns] ic 
    
    ON ic.[index_id] = i.[index_id] AND 
    
       ic.[object_id] = i.[object_id]
    
    WHERE
    
        ic.[is_included_column] = 1

    Here is a link to my blog.
    http://jahaines.blogspot.com/

    I hope this helps.

    • Edited by Adam Haines Thursday, May 7, 2009 6:08 PM syntax
    • Marked as answer by sqlmania Friday, May 8, 2009 9:23 PM
    Thursday, May 7, 2009 6:07 PM
  • Thanks Adam..

    That's good.  Although I have to slightly modify your query by add a DISTINCT keyword.
    otherwise it returns duplicate rows. 
    Thursday, May 7, 2009 6:40 PM
  • You can script with DMO procedures 



    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_genscript]') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[proc_genscript]
    GO
    
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    /****** Object:  Stored Procedure dbo.proc_genscript    Script Date: 5/8/2003 11:06:53 AM ******/
    
    CREATE PROCEDURE proc_genscript 
    	@ServerName varchar(30), 
    	@DBName varchar(30), 
    	@ObjectName varchar(50), 
    	@ObjectType varchar(10), 
    	@TableName varchar(50),
    	@ScriptFile varchar(255)
    AS
    
    DECLARE @CmdStr varchar(255)
    DECLARE @object int
    DECLARE @hr int
    
    SET NOCOUNT ON
    SET @CmdStr = 'Connect('+@ServerName+')'
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    
    --Comment out for standard login
    EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
    
    /* Uncomment for Standard Login
    EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
    EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
    */
    
    EXEC @hr = sp_OAMethod @object,@CmdStr
    SET @CmdStr = 
      CASE @ObjectType
        WHEN 'Database' 	THEN 'Databases("' 
        WHEN 'Procedure'	THEN 'Databases("' + @DBName + '").StoredProcedures("'
        WHEN 'View' 	THEN 'Databases("' + @DBName + '").Views("'
        WHEN 'Table'	THEN 'Databases("' + @DBName + '").Tables("'
        WHEN 'Index'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
        WHEN 'Trigger'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
        WHEN 'Key'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
        WHEN 'Check'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
        WHEN 'Job'	THEN 'Jobserver.Jobs("'
      END
    
    SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
    EXEC @hr = sp_OAMethod @object, @CmdStr
    EXEC @hr = sp_OADestroy @object
    GO
    
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    --Run this query results to text and copy paste output to new query window and run.
    ----For table
    select 'exec proc_genscript ''' + @@SERVERNAME  + ''',''' + DB_NAME()  + ''','''
    + table_schema + '.' + table_name + ''','  
    + '''Table'',''' + table_name + ''',''' + 'c:\table\' + table_name + '.sql ''' + CHAR(10) + 'GO' + CHAR(10)
     from information_schema.tables
     
    go
    --You have to enable Ole Automation procedure
    
    sp_configure 'show advanced option',1
    reconfigure
    go
    sp_configure 'Ole Automation Procedures',1
    reconfigure
    go
    --Disable Ole Automation Procedure after running script.
    Link


    No idea what this script does but i have used this script and it works..

    Thanks,
    Nimit
    • Marked as answer by sqlmania Friday, May 8, 2009 9:24 PM
    Thursday, May 7, 2009 6:49 PM
  • Yeah it will do that because it will return a row for each column.  Distinct or group by will take care of that, as you stated.
    • Marked as answer by sqlmania Friday, May 8, 2009 9:23 PM
    • Unmarked as answer by sqlmania Friday, May 8, 2009 9:23 PM
    Thursday, May 7, 2009 7:20 PM
  • I have my own version of sp_helpindex: sp_indexinfo:
    http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp
    Tibor Karaszi
    • Marked as answer by sqlmania Friday, May 8, 2009 9:40 PM
    Friday, May 8, 2009 5:40 AM