Answered by:
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
ThanksThursday, 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
masherThursday, 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
Linkif 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.
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 KarasziThursday, 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
masherThursday, 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
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.
masherThursday, 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
Linkif 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.
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