Solved

Database creation script command line automation

Posted on 2008-10-14
2
620 Views
Last Modified: 2011-04-14
Hello,
  I'm able to script a database (click on db, tasks, generate scripts, etc) no problem.  What I need to be able to do is script the database (and all objects) automatically on a schedule to a file.  I tried to run profiler while I run this via the gui but I wasn't able to see anything useful to see what it's actually doing.  Does anybody know a clever way to make SQL kick out a db script from commnad line?
0
Comment
Question by:skacore
2 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
ID: 22713863
You can call a SQL statement that used the below procedure to generate each table.  You will have to do some looping through the tables.


By default, it will include the database name in the create statement, but that can be removed easily.
set nocount on

use [master]

go

if object_id('[dbo].[sp_ScriptTable]') is not null

     drop procedure [dbo].[sp_ScriptTable]

go

CREATE PROCEDURE [dbo].[sp_ScriptTable]

      @TableName              SYSNAME

     ,@IncludeConstraints     BIT = 1

     ,@IncludeIndexes         BIT = 1

     ,@NewTableName           SYSNAME = NULL

     ,@UseSystemDataTypes     BIT = 0

     ,@CreateStatement        nvarchar(max) = null output

     ,@OutputOnly             bit = 0

     

/*********************************************************

*

*    original author:    Tim Chapman(dew)

*    modified by:        Brandon Galderisi

*    purpose:            Given a table, it outputs the necessary 

*                        SQL to recreate such table.

*

*    last modified:      07-Oct-2008

*

*********************************************************/

AS

set nocount on
 

    DECLARE @MainDefinition TABLE

    (

        FieldValue VARCHAR(1000)

    )
 

    DECLARE @DBName SYSNAME

    DECLARE @ClusteredPK BIT

    DECLARE @TableSchema NVARCHAR(255)

    SET @DBName = DB_NAME(DB_ID())
 

    --//Make sure the table name is the same case.

    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

        

    DECLARE @ShowFields TABLE

    (

        FieldID INT IDENTITY(1,1),

        DatabaseName VARCHAR(100),

        TableOwner VARCHAR(100),

        TableName VARCHAR(100),

        FieldName VARCHAR(100),

        ColumnPosition INT,

        ColumnDefaultValue VARCHAR(100),

        ColumnDefaultName VARCHAR(100),

        is_System_Named bit,

        IsNullable BIT, 

        DataType VARCHAR(100),

        MaxLength INT,

        NumericPrecision INT,

        NumericScale INT,

        DomainName VARCHAR(100), 

        FieldListingName VARCHAR(110), 

        FieldDefinition CHAR(1),

        IdentityColumn BIT, 

        IdentitySeed INT,

        IdentityIncrement INT, 

        IsCharColumn BIT

    )
 

    DECLARE @HoldingArea TABLE

    (

        FldID SMALLINT IDENTITY(1,1),

        Flds VARCHAR(4000), 

        FldValue CHAR(1) DEFAULT(0)

    )
 
 

    DECLARE @PKObjectID TABLE

    (

        ObjectID INT

    )
 

    DECLARE @Uniques TABLE

    (

        ObjectID INT

    )
 

    DECLARE @HoldingAreaValues TABLE

    (

        FldID SMALLINT IDENTITY(1,1),

        Flds VARCHAR(4000), 

        FldValue CHAR(1) DEFAULT(0)

    )
 

    DECLARE @Definition TABLE

    (

        DefinitionID SMALLINT IDENTITY(1,1),

        FieldValue VARCHAR(200)

    )
 

    INSERT INTO @ShowFields

    (        

        DatabaseName,

        TableOwner,

        TableName,

        FieldName,

        ColumnPosition,

        ColumnDefaultValue,

        ColumnDefaultName,

        is_system_named,

        IsNullable, 

        DataType,

        MaxLength,

        NumericPrecision,

        NumericScale,

        DomainName,

        FieldListingName, 

        FieldDefinition, 

        IdentityColumn,

        IdentitySeed, 

        IdentityIncrement, 

        IsCharColumn

    )
 

    SELECT 

        DB_NAME(), 

        TABLE_SCHEMA,

        TABLE_NAME,

        COLUMN_NAME,

        CAST(ORDINAL_POSITION AS INT),

        COLUMN_DEFAULT,

        dobj.name AS ColumnDefaultName,

        skc.is_system_named,

        CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,

        DATA_TYPE,

        CAST(CHARACTER_MAXIMUM_LENGTH AS INT),

        CAST(NUMERIC_PRECISION AS INT),

        CAST(NUMERIC_SCALE AS INT),

        DOMAIN_NAME,

        COLUMN_NAME + ',',

        '' AS FieldDefinition,

        CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn, 

        CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed, 

        CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement, 

        CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn

    FROM 

        INFORMATION_SCHEMA.COLUMNS c

        JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name

        LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name

        JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name

        LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'

        left outer join sys.default_constraints skc on dobj.object_id = skc.object_id

--

    WHERE c.TABLE_NAME = @TableName

    ORDER BY 

        c.TABLE_NAME, c.ORDINAL_POSITION
 

    SELECT TOP 1 @TableSchema = TableOwner

    FROM @ShowFields

    

    INSERT INTO @HoldingArea (Flds) VALUES('(')
 

    INSERT INTO @Definition(FieldValue)

    VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE '['+@DBName + '].[' + @TableSchema + '].[' + @TableName+']' END)
 

    INSERT INTO @Definition(FieldValue)

    VALUES('(')
 

    INSERT INTO @Definition(FieldValue)

        SELECT 

                '['+FieldName + '] ' + 

                                    CASE 

                                        WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END

                                        ELSE UPPER(DataType) + 

                                            CASE WHEN IsCharColumn = 1 THEN '(' + case maxlength when -1 then 'MAX' else CAST(MaxLength AS VARCHAR(10)) end + ')' ELSE '' END +

                                            CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +

                                            CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +

                                            CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN case is_system_named when 1 then '' else 'CONSTRAINT [' + ColumnDefaultName + '] ' end + 'DEFAULT' + ColumnDefaultValue ELSE '' END

                                    END + 

                                    

                                    CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END

        FROM    @ShowFields 
 

        IF @IncludeConstraints = 1

        BEGIN

            INSERT INTO @Definition(FieldValue)

            SELECT 

            ',CONSTRAINT ' + case when is_system_named = 1 then '' else '[' + name + '] ' end + 'FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'

            FROM

            (

                SELECT is_system_named,

                ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,

                REVERSE(SUBSTRING(REVERSE((

                SELECT cp.name + ','

                FROM

                sys.foreign_key_columns fkc

                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id

                WHERE fkc.constraint_object_id = fk.object_id

                FOR XML PATH('')

                )), 2, 8000)) ParentColumns, 

                REVERSE(SUBSTRING(REVERSE((

                SELECT cr.name + ','

                FROM

                sys.foreign_key_columns fkc

                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id

                WHERE fkc.constraint_object_id = fk.object_id

                FOR XML PATH('')

                )), 2, 8000)) ReferencedColumns

                FROM sys.foreign_keys fk

            ) a

            WHERE ParentObject = @TableName
 

            INSERT INTO @Definition(FieldValue)

            SELECT', ' + case when is_system_named =1 then '' else  'CONSTRAINT [' + name + '] ' end + 'CHECK ' + definition FROM sys.check_constraints

            WHERE OBJECT_NAME(parent_object_id) = @TableName

        

--------------------------------------------------------------------------------------------
 

        INSERT INTO @PKObjectID(ObjectID)

        SELECT DISTINCT 

            PKObject = cco.object_id

        FROM 

            sys.key_constraints cco

            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

        WHERE 

            OBJECT_NAME(parent_object_id) = @TableName    AND    

            i.type = 1 AND 

            is_primary_key = 1
 

        INSERT INTO @Uniques(ObjectID)

        SELECT DISTINCT 

            PKObject = cco.object_id

        FROM 

            sys.key_constraints cco

            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

        WHERE 

            OBJECT_NAME(parent_object_id) = @TableName AND        

            i.type = 2 AND

            is_primary_key = 0 AND

            is_unique_constraint = 1 

            

        SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
 

        INSERT INTO @Definition(FieldValue)

        SELECT    ', ' + case when cco.is_System_Named=1 then '' else 'CONSTRAINT ['+cco.name+']' end + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END 

                            WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +

        REVERSE(SUBSTRING(REVERSE((

            SELECT 

                c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','

            FROM 

                sys.key_constraints ccok

                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

                LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id

                LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

            WHERE 

                i.object_id = ccok.parent_object_id AND 

                ccok.object_id = cco.object_id

            FOR XML PATH('')

        )), 2, 8000)) + ')'

        FROM 

            sys.key_constraints cco

            LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID

            LEFT JOIN @Uniques u ON cco.object_id = u.objectID

        WHERE 

            OBJECT_NAME(cco.parent_object_id) = @TableName

        END

----------------------------------------------------------------------------------------------

        INSERT INTO @Definition(FieldValue)

        VALUES(')')
 

        IF @IncludeIndexes = 1

        BEGIN

            INSERT INTO @Definition(FieldValue)

            SELECT 

                'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' + 

                REVERSE(SUBSTRING(REVERSE((

                    SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','

                    FROM 

                        sys.index_columns sc

                        JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id

                    WHERE 

                        OBJECT_NAME(sc.object_id) = @TableName AND

                        sc.object_id = i.object_id AND 

                        sc.index_id = i.index_id

                    ORDER BY index_column_id ASC

                    FOR XML PATH('')

            )), 2, 8000)) + ')'

            FROM sys.indexes i

            WHERE 

                OBJECT_NAME(object_id) = @TableName

                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1

                AND is_unique_constraint = 0

                AND is_primary_key = 0

                and i.Type <> 0

        END

            INSERT INTO @MainDefinition(FieldValue)

            SELECT FieldValue FROM @Definition 

            ORDER BY DefinitionID ASC
 

        SELECT @CreateStatement= isnull(@CreateStatement +char(13),'') + FieldValue FROM @MainDefinition
 

if @OutputOnly=0

     select @CreateStatement
 
 

go
 

exec sp_MS_marksystemobject '[dbo].[sp_ScriptTable]'
 

--Sample data below

--

--if object_id('[dbo].[abcd]') is not null

--  exec ('drop table [ABCd]')

--go

--create table [ABCd] (ident int identity(1,2) not null primary key nonclustered

--     ,timCh nvarchar(max)

--     ,timC varchar(1) default 'a' 

--     --,timb varchar(1) unique 

--     )

----create unique clustered index [cidx_abc-d_timc] on [ABCd](timc)

--go

--

--

--declare @CreateStatement nvarchar(max)

--exec [dbo].[sp_ScriptTable]

--

--      @TableName              ='ABCd'

--     ,@IncludeConstraints     = 1

--     ,@IncludeIndexes         = 1

--     ,@NewTableName           = NULL

--     ,@UseSystemDataTypes     = 0

--     ,@CreateStatement        = @CreateStatement output

--     ,@OutputOnly             = 1

--

--select @CreateStatement

--

--go

--

--

--if object_id('[dbo].[abcd]') is not null

--  exec ('drop table [ABCd]')

--go

--create table [ABCd] (ident int identity(1,2) not null primary key nonclustered

--     ,timCh nvarchar(max)

--     ,timC varchar(1) default 'a' 

--     --,timb varchar(1) unique 

--     )

--create unique clustered index [cidx_abc-d_timc] on [ABCd](timc)

--go

--

--

--declare @CreateStatement nvarchar(max)

--exec [dbo].[sp_ScriptTable]

--

--      @TableName              ='ABCd'

--     ,@IncludeConstraints     = 1

--     ,@IncludeIndexes         = 1

--     ,@NewTableName           = NULL

--     ,@UseSystemDataTypes     = 0

--     ,@CreateStatement        = @CreateStatement output

--     ,@OutputOnly             = 1

--

--select @CreateStatement

--

--go

--

--

--if object_id('[dbo].[abcd]') is not null

--  exec ('drop table [ABCd]')

--go

--create table [ABCd] (ident int identity(1,2) not null primary key nonclustered

--     ,timCh nvarchar(max)

--     ,timC varchar(1) default 'a' 

--     ,timb varchar(1) unique 

--     )

--create unique clustered index [cidx_abc-d_timc] on [ABCd](timc)

--go

--

--

--declare @CreateStatement nvarchar(max)

--exec [dbo].[sp_ScriptTable]

--

--      @TableName              ='ABCd'

--     ,@IncludeConstraints     = 1

--     ,@IncludeIndexes         = 1

--     ,@NewTableName           = NULL

--     ,@UseSystemDataTypes     = 0

--     ,@CreateStatement        = @CreateStatement output

--     ,@OutputOnly             = 1

--

--select @CreateStatement

--

--

--

Open in new window

0
 
LVL 1

Accepted Solution

by:
skacore earned 0 total points
ID: 22714162
Ah, I think I found what I needed:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3680585&SiteID=1

And I wrote this:

"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" script -schemaonly -nodropexisting -d pubs  -S 192.168.1.200 C:\db.sql

And it kicks out my db script.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now