Generate all database table DDL

set nocount on 
use master 

declare @DBNAME varchar(128) 
declare @TabName varchar(128) 
declare @TabID int 
declare @DDLDEF VARCHAR(8000) 
declare @ColName varchar(1000)
declare @Count int
declare @FileGroup varchar(128)

declare DBCur cursor 
for 
  select name 
  from sysdatabases 
  where name not in ('tempdb', 'master', 'pubs', 'Northwind', 'msdb', 'model')
  order by name 

OPEN DBCur 

fetch next from DBCur into @DBNAME 
  
while @@FETCH_STATUS =0 
begin 
 exec('use ' + @DBNAME) 
  PRINT '/*Scripting for ' + @DBNAME + ' done on ' + CAST(GetDate() as varchar(50)) 
        + '*/' 
 declare TabNameCur cursor for 
   select '[' + su.name + '].[' + so.name + ']', so.id , sfg.groupname
   from sysusers su inner join sysobjects so 
    on su.uid = so.uid 
   inner join sysindexes si on si.id = so.id
   inner join sysfilegroups sfg on sfg.groupid = si.groupid
   where so.type = 'U' 
   order by su.name, so.name 

 open TabNameCur 
  
 fetch next from TabNameCur into @TabName, @TabID, @FileGroup
 while @@FETCH_STATUS = 0 
  begin 
   set @DDLDef =  Char(13) + 'CREATE TABLE ' + @TabName + '(' + Char(13)
    /* Must incorporate all datatypes */   declare ColNameCur cursor for
   select '[' + sc.name + ']' + ' ' + st.name +   
          CASE when st.name = 'char' then 
                   '(' + CAST(sc.LENGTH as varchar(50)) + ')' 
   when st.name = 'nchar' then 
                   '(' + CAST(sc.LENGTH as varchar(50)) + ')' 
               when st.name = 'nvarchar' then 
                   '(' + CAST(sc.LENGTH as varchar(50)) + ')' 
               when st.name = 'varbinary' then 
                   '(' + CAST(sc.LENGTH as varchar(50)) + ')' 
               when st.name = 'binary' then 
                   '(' + CAST(sc.LENGTH as varchar(50)) + ')'
               when st.name = 'varchar' then 
                   '(' + CAST(sc.LENGTH as varchar(50)) + ')' 
               when st.name = 'decimal' then 
                    '(' + CAST(sc.LENGTH as varchar(50)) + 
                   ',' + CAST(sc.PREC as varchar(50)) + ')' 
               when st.name = 'numeric' then 
                    '(' + CAST(sc.LENGTH as varchar(50)) + 
                   ',' + CAST(sc.PREC as varchar(50)) + ')' 
           else '' end + ' ' + 
          CASE when sc.ISNULLABLE = 1 then 'NULL' else 'NOT NULL' end 

   from syscolumns sc 
     INNER JOIN systypes st on sc.xtype = st.xtype 
  
   where sc.id = @TabID   

   order by sc.colid 

   open ColNameCur
   fetch next from ColNameCur into @ColName
   set @Count = 1

   while @@FETCH_STATUS = 0 -- ColNameCur
    begin
     if @Count = 1
      begin
       set @DDLDef = @DDLDef + '  ' + @ColName + Char(13)
       set @Count = @Count + 1
      end
     else
      begin
       set @DDLDef = @DDLDef + ', ' + @ColName + Char(13)
       set @Count = @Count + 1
      end
     fetch next from ColNameCur into @ColName
    end /* ColNameCur @@FetchStatus */  
   set @DDLDef = @DDLDef + ') ON [' + @FileGroup + ']' + Char(13) + 'GO'
 
    PRINT @DDLDEF 

    set @DDLDef = ''

    close ColNameCur
    deallocate ColNameCur

   fetch next from TabNameCur into @TabName, @TabID, @FileGroup        
  end -- TabNameCur 

 close TabNameCur 
 deallocate TabNameCur 
 fetch next from DBCur into @DBNAME 

end --DBCur 

close DBCur 

deallocate DBCur