Sql-server – Auto Script DB Objects for SVN

My Boss requires that we keep up to date scripts of all database objects in svn. This results in constantly trying to find the object in the current script and copy and pasting the changes.

Is there an easy way to set sql server to script database objects and write them to a file on the drive?

If not then I've built the following sql script as a test run for creating a tables file but I'm not sure how to capture the output string that sp_executesql is creating.

DECLARE @SQL2 NVARCHAR(MAX) = ''

CREATE TABLE #Dbs(name varchar(200) NOT NULL)

INSERT INTO #Dbs(name)
SELECT name
  FROM sys.databases
 WHERE name NOT IN ('master','msdb','tempdb', 'model')
 ORDER BY name

WHILE (SELECT COUNT(1) FROM #Dbs) > 0 
BEGIN

SET @SQL2 = N'USE [' + (SELECT TOP 1 #Dbs.name FROM #Dbs ORDER BY name) + N']' + '

DECLARE @SQL NVARCHAR(MAX) = ''''
DECLARE @CurrentTable VARCHAR(200) = '' ''
DECLARE @OldTable VARCHAR(200) = '' ''

SET @SQL = ''''

    SET @SQL = @SQL + N''USE ['' + (SELECT TOP 1 #Dbs.name FROM #Dbs ORDER BY name) + N'']''

    CREATE TABLE #columns (ColumnName VARCHAR(200) NULL,
                           ColumnID INT, 
                           TableName VARCHAR(200) NULL,
                           DataType VARCHAR(50) NULL,
                           Nullable VARCHAR(15) NULL, 
                           MiscInfo VARCHAR(50) NULL,
                           UseAnsiNulls VARCHAR(50) NULL
                           )

    INSERT INTO #columns(columnname, ColumnID, TableName, datatype, Nullable, MiscInfo, UseAnsiNulls)
    SELECT s.name as ColumnName,
           s.column_id,
           ''[''+sh.name+''].[''+b.name+'']'' AS ObjectName ,
           CASE 
               WHEN t.name IN (''char'',''varchar'') THEN t.name+''(''+CASE WHEN s.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),s.max_length) END+'')'' 
               WHEN t.name IN (''nvarchar'',''nchar'') THEN t.name+''(''+CASE WHEN s.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),s.max_length/2) END+'')'' 
               WHEN t.name IN (''numeric'') THEN t.name+''(''+CONVERT(varchar(10),s.precision)+'',''+CONVERT(varchar(10),s.scale)+'')'' 
               ELSE t.name 
           END AS DataType, 
           CASE 
               WHEN s.is_nullable=1 THEN ''NULL'' 
               ELSE ''NOT NULL'' 
           END AS Nullable, 

           CASE 
               WHEN ic.column_id IS NULL THEN '''' 
               ELSE '' identity(''+ISNULL(CONVERT(varchar(10),ic.seed_value),'''')+'',''+ISNULL(CONVERT(varchar(10),ic.increment_value),'''')+'')''
           END + CASE 
                     WHEN sc.column_id IS NULL THEN '''' 
                     ELSE '' computed(''+ISNULL(sc.definition,'''')+'')'' 
                 END + CASE 
                           WHEN cc.object_id IS NULL THEN '''' 
                           ELSE '' check(''+ISNULL(cc.definition,'''')+'')'' 
                       END AS MiscInfo,
           CASE 
               WHEN uses_ansi_nulls = 1 THEN
                    N''SET ANSI_NULLS ON''
               ELSE N''SET ANSI_NULLS OFF''
           END UseAnsiNulls
      FROM sys.columns s 
           JOIN sys.types t ON s.system_type_id=t.system_type_id and t.is_user_defined=0 
           JOIN sys.tables b ON s.object_id=b.object_id 
           JOIN sys.schemas sh on b.schema_id=sh.schema_id 
           LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id 
           LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id 
           LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id 
     ORDER BY sh.name+''.''+b.name,s.column_id 

    WHILE (SELECT COUNT(1) FROM #columns) > 0 
    BEGIN

        SET @CurrentTable = (SELECT TOP 1 Tablename 
                               FROM #columns
                              ORDER BY TableName, columnid)

        IF @OldTable != @CurrentTable
        BEGIN

            IF @SQL IS NOT NULL AND @SQL != '''' AND @SQL !=  N''USE ['' + (SELECT TOP 1 #Dbs.name FROM #Dbs ORDER BY name) + N'']''
            BEGIN
              SET @SQL = (SELECT SUBSTRING(@SQL,1,LEN(@SQL)-2)) + '')''
            END

                SET @SQL = @SQL + '' '' + (SELECT TOP 1 UseAnsiNulls 
                                           FROM #columns
                                          ORDER BY TableName, columnid) +N'' CREATE TABLE '' + (SELECT TOP 1 TableName
                                                                                                                FROM #columns
                                                                                                               ORDER BY TableName, columnid) + ''(''
        SET @OldTable = @CurrentTable
        END

        SET @SQL = @SQL + (SELECT TOP 1 Columnname + '' '' + datatype + '' '' + Nullable + '' '' + MiscInfo + '',''  FROM #columns ORDER BY TableName, columnid)

        DELETE FROM #columns
          FROM #columns c
         WHERE c.ColumnName = (SELECT TOP 1 Columnname FROM #columns ORDER BY TableName, columnid)
           AND c.TableName = (SELECT TOP 1 tablename FROM #columns ORDER BY TableName, columnid)
    END--Loop Columns End

SELECT SUBSTRING(@SQL,1,LEN(@SQL)-2) + '')''

DROP TABLE #Columns'

exec sp_executesql @SQL2

DELETE FROM #Dbs
 WHERE #dbs.name = (SELECT TOP 1 name FROM #Dbs WHERE name NOT IN ('master','msdb','tempdb', 'model') ORDER BY name)
END--LOOP DBS END

DROP TABLE #DBs