Stored Procedure 1:


General script to retrieve structure of individual objects for a database.


CREATE PROCEDURE proc_genscript
        @ServerName varchar(30), 
        @DBName varchar(30), 
        @ObjectName varchar(50), 
        @ObjectType varchar(10), 
        @TableName varchar(50),
        @ScriptFile varchar(255)
AS


DECLARE @CmdStr varchar(255)
DECLARE @object int
DECLARE @hr int
DECLARE @ScriptType int


SET NOCOUNT ON
SET @ScriptType =1|4|32|64|256|262144 
SET @CmdStr = 'Connect('+@ServerName+')'
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT


--Comment out for standard login
EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE


/* Uncomment for Standard Login
EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
*/


EXEC @hr = sp_OAMethod @object,@CmdStr
SET @CmdStr = 
  CASE @ObjectType
    WHEN 'Database'   THEN 'Databases("' 
    WHEN 'Procedure'  THEN 'Databases("' + @DBName + '").StoredProcedures("'
    WHEN 'View'       THEN 'Databases("' + @DBName + '").Views("'
    WHEN 'Table'      THEN 'Databases("' + @DBName + '").Tables("'
    WHEN 'Index'      THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
    WHEN 'Trigger'    THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
    WHEN 'Key'        THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
    WHEN 'Check'      THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
    WHEN 'Job'        THEN 'Jobserver.Jobs("'
  END


SET @CmdStr = @CmdStr + @ObjectName + '").Script(260,"' + @ScriptFile + '")'
EXEC @hr = sp_OAMethod @object, @CmdStr
EXEC @hr = sp_OADestroy @object


GO



This will create scripts for each databases for a given server and path



Stored Procedure 2:


create procedure usp_structure
@server varchar(1000),
@path varchar(1000)
as
declare @dbname varchar(3000)
declare @cmd varchar(3000)
declare @cmd1 varchar(2000)
declare @table varchar(3000)
declare @cmd2 varchar(3000)
declare @cmd3 varchar(3000)
declare @sp varchar(3000)
declare @cmd4 varchar(3000)
declare @cmd5 varchar(3000)
declare @vw varchar(3000)
declare @cmd6 varchar(3000)
declare @cmd7 varchar(3000)
declare @ix varchar(3000)
declare @cmd8 varchar(3000)
declare @cmd9 varchar(3000)
declare @tg varchar(3000)
declare my_database cursor
for select name from master.dbo.sysdatabases
where name not in('master','model','tempdb','msdb')
open my_database
fetch next from my_database into @dbname
while @@fetch_status=0
begin


 set @cmd=''
 set @cmd=('exec master..xp_cmdshell "mkdir '''+@path+''+@dbname+'"')
 exec (@cmd)
 set @cmd1='
declare my_table cursor
for SELECT
 distinct(o.name) AS [Table Name]
FROM '+@dbname+
'.dbo.sysobjects AS o
INNER JOIN
'+@dbname+'.dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
'+@dbname+'.dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE  (
   o.type = ''U''
)'
exec (@cmd1)



open my_table
fetch next from my_table into @table
while @@fetch_status=0
begin


        set @cmd=''
        set @cmd=('exec proc_genscript' +char(10)+
                  '@ServerName = '''+@server+''','  +char(10)+
                  '@DBName ='''+@dbname+''',' +char(10)+
                  '@ObjectName = '''+@table+''',' +char(10)+
                  '@ObjectType = ''Table'',' +char(10)+
                  '@TableName = '''+@table+''',' +char(10)+
                  '@ScriptFile = '''+@path+''+@dbname+'.sql''')
       exec(@cmd)


       set @cmd6='declare my_index cursor
                  for SELECT    i.name AS IndexName
                  FROM '+@dbname+'.dbo.sysobjects o,'+@dbname+'.dbo.sysindexes i
                  WHERE   (o.id = i.id and o.name = '''+@table+''') AND (i.status = 18450 OR   i.status = 2097152)'
       exec(@cmd6)
       open my_index
       fetch next from my_index into @ix
      while @@fetch_status=0
begin
       set @cmd7=''
       set @cmd7=('exec proc_genscript' +char(10)+
                   '@ServerName = '''+@server+''','  +char(10)+
                   '@dbname = '''+@dbname+''',' +char(10)+
                   '@objectname = '''+@ix+''',' +char(10)+
                   '@ObjectType = ''Index'',' +char(10)+
                   '@Tablename = '''+@table+''',' +char(10)+
                   '@ScriptFile = '''+@path+''+@dbname+'.sql''')
       exec(@cmd7)


fetch next from my_index into @ix
end
close my_index
deallocate my_index


set @cmd8='declare my_trigger cursor
  for select 
       ''Trigger''     = left(tr.name,50)
         from '+@dbname+'.dbo.sysobjects      so
  left join '+@dbname+'.dbo.sysusers   su  on su.uid        = so.uid
  join '+@dbname+'.dbo.sysobjects      tr  on tr.type       = ''TR''
                              and tr.parent_obj = so.id
 where(so.type=''U'' or so.type=''S'')
   and so.name like '''+@table+''''


exec(@cmd8)


open my_trigger
fetch next from my_trigger into @tg
while @@fetch_status=0
begin


        set @cmd9=''
        set @cmd9=('exec proc_genscript' +char(10)+
                  '@ServerName = '''+@server+''','  +char(10)+
                  '@DBName =''' + @dbname+''',' +char(10)+
                  '@ObjectName = '''+@tg+''',' +char(10)+
                  '@ObjectType = ''Trigger'',' +char(10)+
                  '@TableName = '''+@table+''',' +char(10)+
                  '@ScriptFile = '''+@path+''+@dbname+'.sql''')
       exec(@cmd9)
fetch next from my_trigger into @tg
end
close my_trigger
deallocate my_trigger


fetch next from my_table into @table
end
close my_table
deallocate my_table


set @cmd2='declare my_sp cursor 
           for select o.name
           from
           sysobjects o,syscomments c
           where c.colid = 1
           and  o.type in (''P'',''FN'',''TF'',''IF'')          /* Object type of Procedure, scalar UDF, table UDF */
           and c.id = o.id
           and permissions (o.id)&32 <> 0'


exec (@cmd2)
open my_sp
fetch next from my_sp into @sp
while @@fetch_status=0
begin
       set @cmd3=''
       set @cmd3=('exec proc_genscript' +char(10)+
                   '@ServerName = '''+@server+''','  +char(10)+
                   '@dbname = '''+@dbname+''',' +char(10)+
                   '@objectname = '''+@sp+''',' +char(10)+
                   '@ObjectType = ''Procedure'',' +char(10)+
                   '@Tablename = '''+@sp+''',' +char(10)+
                   '@ScriptFile = '''+@path+''+@dbname+'.sql''')
      exec(@cmd3)



fetch next from my_sp into @sp
end
close my_sp
deallocate my_sp



set @cmd4='declare my_vw cursor 
           for SELECT     
 distinct(o.name)
 FROM        
 '+@dbname+'.dbo.sysobjects AS o 
INNER JOIN
 '+@dbname+'.dbo.syscolumns AS c 
  ON 
   c.id = o.id 
INNER JOIN
 '+@dbname+'.dbo.systypes AS s 
  ON 
   c.xtype = s.xtype
WHERE  (
   o.type = ''V'' and o.name not in (''sysconstraints'',''syssegments''))'


exec (@cmd4)
open my_vw
fetch next from my_vw into @vw
while @@fetch_status=0
begin
       set @cmd5=''
       set @cmd5=('exec proc_genscript' +char(10)+
                   '@ServerName = '''+@server+''','  +char(10)+
                   '@dbname = '''+@dbname+''',' +char(10)+
                   '@objectname = '''+@vw+''',' +char(10)+
                   '@ObjectType = ''View'',' +char(10)+
                   '@Tablename = '''+@vw+''',' +char(10)+
                   '@ScriptFile = '''+@path+''+@dbname+'.sql''')
       exec(@cmd5)


fetch next from my_vw into @vw
end
close my_vw
deallocate my_vw


fetch next from my_database into @dbname
end
close my_database
deallocate my_database
GO