Generate SQL Create Scripts with Query

Question

I need to be able to get the CREATE scripts for existing tables within SQL Server 2008. I assume I can do this by querying the sys.tables somehow, however this isn't returning me the CREATE script data.

Answer 1

Easiest way is to use the built-in feature of SQL Management Studio.

Right-click the database, go to tasks, Generate Scripts, and walk through the wizard. You can choose what objects to script, and it'll make it all for you.

Now if you are trying to make your OWN script to do the same thing, you're probably up for a lot of work...

Answer 2

do you mean you wish to create a TSQL script which generates a CREATE script, or use the Management tools in SQL SERVER Management Studio to generate a Create script?

If it's the latter, it's a simply matter of right-clicking a table, and selecting Script Table As -> Create To -> New Query Window.

If you want the whole database scripted, then right click the database and select Tasks--> Generate Scripts... and then follow the wizard

otherwise it's a matter of selecting all sorts of fun things out of the various system tables.

Answer 3

Since we're offering alternatives to what you asked..

If you're in .Net, you should look at the Database Publishing Wizard in Visual Studio. Easy way to script your tables/data to a text file.

http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Answer 4

Use the SSMS, easiest way You can configure options for it as well (eg collation, syntax, drop...create)

Otherwise, SSMS Tools Pack, or DbFriend on CodePlex can help you generate scripts

Answer 5

Try sp_helptext Equivalent for Tables?

Answer 6

"Easiest way is to use the built-in feature of SQL Management Studio" but... I have resolved it with a function and a couple of procedures. For example, to obtain the create table for a table named 'table_name', you have to execute just the procedure called sp_ppinScriptTabla:

Exec sp_ppinScriptTabla 'table_name'

Here is the tsql script code:

Use Master
GO

Create Function sp_ppinTipoLongitud
(
    @xtype int,
    @length int,
    @isnullable int
)
Returns Varchar(512)
As 
Begin
    -- Función que a partir de un tipo de datos y una logitud, devuelve el texto del tipo.
    -- Por ejemplo: para xtype=varchar y length=10 devolverá "varchar(10)"
    Declare @ret varchar(512)
    Set @ret = ''

    Select @ret = t.name +
    Case When name in ('varchar', 'nvarchar', 'char', 'nchar') Then '(' + Convert(varchar, @length) + ')' Else '' End + ' ' +
    Case @isnullable When 1 Then 'NULL' Else 'NOT NULL' End
    From systypes t 
    Where t.xtype = @xtype

    Return @ret
End
GO

Create Procedure sp_ppinScriptLlavesForaneas
(
    @vchTabla sysname,
    @vchResultado varchar(8000) output
)
AS 
Begin

    DECLARE @tmpFK table(
     TablaF sysname,
     TablaR sysname,
     ColF sysname,
     ColR sysname,
     FKName sysname)

    -- obtengo las llaves foraneas en @vchForeign
    Declare @vchForeign varchar(8000), @FKName sysname, @vchColumnasF varchar(4000), @vchColumnasR varchar(4000), @ColF sysname, @ColR sysname
    Declare @vchTemp varchar(1000), @TablaR sysname

    Insert into @tmpFK
    Select TablaF.name AS TablaF, TablaR.name AS TablaR, ColF.name AS ColF, ColR.name AS ColR, ofk.name AS FKName
    From sysforeignkeys fk, sysobjects ofk, sysobjects TablaF, sysobjects TablaR, 
    syscolumns ColF, syscolumns ColR
    Where TablaF.name = @vchTabla
    And ofk.id = fk.constid
    And TablaF.id = fk.fkeyid
    And TablaR.id = fk.rkeyid
    And ColF.id = TablaF.id And ColF.colid = fk.fkey
    And ColR.id = TablaR.id And ColR.colid = fk.rkey
    order by FKName

    Set @vchForeign = ''
    While Exists ( Select * From @tmpFK )
    Begin
     Select Top 1 @FKName = FKName From @tmpFK
     Set @vchColumnasF = ''
     Set @vchColumnasR = ''
     While Exists ( Select * From @tmpFK Where FKName = @FKName )
     Begin
      Select Top 1 @ColF = ColF, @ColR = ColR, @TablaR = TablaR From @tmpFK Where FKName = @FKName
      Delete From @tmpFK Where ColF = @ColF And ColR = @ColR And TablaR = @TablaR And FKName = @FKName
      Set @vchColumnasF = @vchColumnasF + @ColF + ', '
      Set @vchColumnasR = @vchColumnasR + @ColR + ', '
     End

     Set @vchColumnasF = LEFT(@vchColumnasF, LEN(@vchColumnasF) - 1)
     Set @vchColumnasR = LEFT(@vchColumnasR, LEN(@vchColumnasR) - 1)
     Set @vchTemp = 'Constraint ' + @FKName + ' Foreign Key (' + @vchColumnasF + ') '
     Set @vchTemp = @vchTemp + 'References ' + @TablaR + ' (' + @vchColumnasR + ')'
     Set @vchForeign = @vchForeign + char(9) + @vchTemp + ',' + char(13) 
    End

    Select @vchResultado = Case When Len(@vchForeign) >=2 Then Left(@vchForeign, Len(@vchForeign) - 2) Else @vchForeign End
End
GO

Create Procedure sp_ppinScriptTabla
(
    @vchTabla sysname
)
AS

Set nocount on

-- Obtengo las foreign keys
Declare @foreign varchar(8000)
Exec sp_ppinScriptLlavesForaneas @vchTabla, @foreign output

-- SELECT que devuelve el script de Create Table de la tabla
Select 'Create ' + 
Case o.xtype When 'U' Then 'Table' When 'P' Then 'Procedure' Else '??' End + ' ' +
@vchTabla + char(13) + '('
From sysobjects o
Where o.name = @vchTabla
Union all
-- Campos + identitys + DEFAULTS
select char(9) + c.name + ' ' +         -- Nombre
dbo.sp_ppinTipoLongitud(t.xtype, c.length, c.isnullable) +    -- Tipo(longitud)
Case When c.colstat & 1 = 1          -- Identity (si aplica)
    Then ' Identity(' + convert(varchar, ident_seed(@vchTabla)) + ',' + Convert(varchar, ident_incr(@vchTabla)) + ')' 
    Else '' 
End + 
Case When not od.name is null           -- Defaults (si aplica)
    Then ' Constraint ' + od.name + ' Default ' + replace(replace(cd.text, '((', '('), '))', ')')
    Else ''
End + ', '
from sysobjects o, syscolumns c
LEFT OUTER JOIN sysobjects od On od.id = c.cdefault LEFT OUTER join syscomments cd On cd.id = od.id, 
systypes t
where o.id = object_id(@vchTabla)
and o.id = c.id
and c.xtype = t.xtype
Union all
-- Primary Keys y Unique keys
select char(9) + 'Constraint ' + o.name + ' ' +
Case o.xtype When 'PK' Then 'Primary Key' Else 'Unique' End + ' ' +
dbo.sp_ppinCamposIndice (db_name(), @vchTabla, i.indid) + ', '
from sysobjects o, sysindexes i
where o.parent_obj = object_id(@vchTabla)
and o.xtype in ('PK','UQ')
and i.id = o.parent_obj
and o.name = i.name
Union all
-- Check constraints
select char(9) + 'Constraint ' + o.name + ' Check ' + c.text + ', '
from sysobjects o, syscomments c
where o.parent_obj = object_id(@vchTabla)
and o.xtype in ('C')
and o.id = c.id
Union all
-- Foreign keys
Select @foreign
Union all
Select ')'

Set nocount off
GO

Answer 7

You forgot to include the stored procedure or function script for 'sp_ppinCamposIndice'