Generate all database table DDL

,

This script starts in the master database, and then goes to each USER database and creates the DDL for each USER table in every database.  You can then save the results or copy them to a new query window to run on another server.

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

Rate

Share

Share

Rate