generate table script

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

Answer 1

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

Answer 2

hmm... so there is no way?  I thought we can get these text from meta data or something.
maybe sql 2008 can do that?

 

Answer 3

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!!!

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

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?

Answer 9

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.

Answer 10

Thanks Adam..

That's good.  Although I have to slightly modify your query by add a DISTINCT keyword.
otherwise it returns duplicate rows. 

Answer 11

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

Answer 12

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.

Answer 13

I have my own version of sp_helpindex: sp_indexinfo:
http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

---

Tibor Karaszi