question

imrankasuri avatar image
imrankasuri asked

How to generate create table script

i want to generate create table script without using the management studio and right clicking on the table name and then generating the script. Is there any other option like transact sql statement available to generate script for create or alter table commands ? i also want to generate the scripts for stored procedures. Please guide me how to do that. Thanks in Advance.
script
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered

For generating script of SP,Function and Trigger you can use SQL function:-

SELECT OBJECTDEFINITION (OBJECTID(N'DBO.uAddress'))

EXEC sp_helptext ''

Generating Script for table--

declare @table varchar(100)
set @table = '<table_name>'
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

    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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
I can recommend using PowerShell for this task, here is a [link to an example script][1] [1]: http://www.mssqltips.com/sqlservertip/1842/generating-sql-scripts-using-windows-powershell/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The SMO Scripter object is probably the easiest way for scripting. It is possible to use PowerShell or write for example a CLR function or stored proc for that purposes and than it can be easily called using query.
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
**Using PowerShell and SMO** Well, it all depends. Do you want your tables each saved in separate files, would you like them all together in a single script, in dependency order? Do you want all the DRI and other dependent objects such as triggers? If you look at my new article here... [Automated Script-generation with Powershell and SMO][1] ...you should be able to get precisely what you want, by using the second-from-last script and tweaking it a bit. I'll be doing the dependency stuff in another article but it is just another scripter method **Using TSQL** Yes, Amardeep's SQL is clever, but the Information_Schema doesn't have quite enough information to do the job exactly. Have a look at the rather terrifying stored procedure towards the end of my article ... [Exploring SQL Server table metadata with SSMS and TSQL][2] ...which will script out a table, or any other database object. I think it scripts out most of the obvious features of a table. (If I've missed anything, let me know please!). One drawback of this is that I haven't yet added the option to include all the dependent objects. [1]: http://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/ [2]: http://www.simple-talk.com/sql/t-sql-programming/exploring-sql-server-table-metadata-with-ssms-and-tsql/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
This is just another resource to check on using PowerShell to script out objects in a database...includes a video too. [MidnightDBA Video on Scripting DB Objects in PowerShell][1] [Jen McCown (better half of the MidnightDBA) on Scripting objects in PowerShell.][2] [1]: http://midnightdba.itbookworm.com/VidPages/PowershellScriptDBObjects/PowershellScriptDBObjects.aspx [2]: http://www.midnightdba.com/Jen/2010/05/script-sql-objects-with-powershell/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.