generate create table script

Question

Hi,

        Is there a build-in system procedure in SQL 2005 which can generate "create table scripts" as output?

I know how to do in UI level, but I'm not sure if it's possible to do on query level.

 

for example,

 

   exec   sp_get_create_table_script @tablename='employees',@include_index='Y',@Include_trigger='Y'

 

 

 

Thanks

 

 

 

 

Answer 1

 

Nope. Unfortunately not. You have to use SMO. I am not familiar with it but there are a lots of example.

http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=ADBS,ADBS:2006-48,ADBS:en&q=SMO+SQL+generate+scripts

 

A dedicated forum for it:

 

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

 

And it gets easier in SQL 2008:

 

http://weblog.infoworld.com/dbunderground/archives/2008/07/22_characters_t.html

Answer 2

So you can use 'powershell' to connect to SQL server 2005 to generate scripts ?

Do you use it?  if yes, how do you connect to sql 2005?   is that something I need to setup in powershell setting?

 

Answer 3

In SQL 2008 it is integrated with its own provider and cmdlets. However, you can use powershell and SMO on SQL Server 2005. I have just started messing around with it in SQL 2008. Here is a 10 part series on it and the have samples on object scripting with SMO and powershell.
http://www.databasejournal.com/features/mssql/article.php/3681061