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.


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

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


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

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,
           ''[''+sh.name+''].[''+b.name+'']'' AS ObjectName ,
               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, 
               WHEN s.is_nullable=1 THEN ''NULL'' 
               ELSE ''NOT NULL'' 
           END AS Nullable, 

               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,
               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 

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

        IF @OldTable != @CurrentTable

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

                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

        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


DROP TABLE #Columns'

exec sp_executesql @SQL2

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

  • What we did at the dev shop I worked in was have a different file for each object. When we needed to modify an object the script, already checked into SVN, got updated and then it was ran into the database. Would that workflow work for you? – cfradenburg Oct 11 '12 at 12:44
  • Honestly there are several workflows that I think could work for us. However I'd like to regain the time that is used up in managing these files if at all possible. Especially now because were doing a lot of database design work. so there is a lot of script changing right now. – Lumpy Oct 11 '12 at 12:52

For a repeatable process you can use Powershell to script objects. The following link has more detail but essentially you load the SMO objects, loop through the objects in the database, and call the Script function.


  • looks like I need to learn powershell. Thanks. – Lumpy Oct 11 '12 at 14:20

Right click on the database, go to Tasks and then Generate Scripts. That will give you the option to script it as one file or one file per object.

You can also look into SQL Source Control from Red Gate. I haven't used it but I've heard good things from those that have and it does link with SVN.

  • Can Generateing scripts like that be automated as a job? – Lumpy Oct 11 '12 at 13:05
  • If it can I'm not aware of a way to. Give me a bit and I'll see what else I can find. – cfradenburg Oct 11 '12 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.