Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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

2 Answers 2

up vote 0 down vote accepted

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.

http://philergia.wordpress.com/2011/02/28/using-powershell-and-smo-to-script-sql-server-database-objects-3/

share|improve this answer
    
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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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