select  so.name, 'create table [' + so.name + '] (' + o.list + ')'
 + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so

cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
                when 'sql_variant' then ''
                when 'text' then ''
                when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.COLUMNS
      where 
        [TABLE_NAME] NOT LIKE 'vw%'
       and [TABLE_NAME] = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name               = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'

cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name        NOT IN ('dtproperties')
and so.Category <> 2

select 'IF OBJECT_ID(''' + so.name + ''', ''U'') IS NOT NULL BEGIN DROP TABLE dbo.'  + so.name + ' END'
from    sysobjects so
where so.name NOT LIKE 'sys%'
and so.xtype = 'U'

quote:

---

Originally posted by yosiasz

try this, it is a work in progress. I have not figured out how to exclude phantom tables and system tables. Also it does not handle ntext and xml data types which will require you to clean the script up. Of course test always on a local db,
unless your local db is your production db which in that case you need some medication :)

select  so.name, 'create table [' + so.name + '] (' + o.list + ')'
 + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so

cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
                when 'sql_variant' then ''
                when 'text' then ''
                when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.COLUMNS
      where 
        [TABLE_NAME] NOT LIKE 'vw%'
       and [TABLE_NAME] = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name               = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'

cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name        NOT IN ('dtproperties')
and so.Category <> 2

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

---

quote:

---

Originally posted by yosiasz

got it in that case you will probably need another scripting language such as powershell to do that maybe. since each RDBMS has it's own syntax the above was specific to ms sql server. I am not sure if other RDBMSs have information_schema tables that keep tracks of database's schema information.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

---

quote:

---

Originally posted by yosiasz

sure I will help you think. what are you RDBMs types?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

---

quote:

---

Originally posted by yosiasz

not impossible but a huge task because these rdmbs might/will have different syntax. You will have to build a DDL definition syntax dictionary , map it to rdbms table, that way you can have a many to many relationship between rdbms_type table and DDLCommand Table. It will require some manual input.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

---