December 10, 2007 at 9:37 pm
In SQL 2005 or 2000, how can I generate a SQL Server's db objects : tables, stored procedures, views, ... daily and automatically ?
I have this request because I want to keep track the changes of my database.
Manually, it is 100% OK, but is it possible to generate these things automatically, either using SQL Server's functions or outside SQL Server is OK.
Please tell me.
December 10, 2007 at 9:40 pm
I wrote a console app to do exactly this. it's open source also.
http://www.codeplex.com/scriptdb
---------------------------------------
elsasoft.org
December 11, 2007 at 6:05 am
In a case like this, use the tool above or, if you want to roll your own, SMO (SQL Management Objects) allows you generate SQL scripts by object or for the database. It's a great tool for programming directly against SQL Server.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 11, 2007 at 9:22 am
Grant Fritchey (12/11/2007)
In a case like this, use the tool above or, if you want to roll your own, SMO (SQL Management Objects) allows you generate SQL scripts by object or for the database. It's a great tool for programming directly against SQL Server.
the tool I posted uses SMO. 😉
---------------------------------------
elsasoft.org
December 11, 2007 at 9:31 am
Well, see, I'm right!
😀
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 12, 2007 at 6:46 pm
Thanks a lot.
December 12, 2007 at 9:41 pm
As that is an appropriate way of doing it, it is not the ideal way of doing it. One should create their scripts when they are making the database change and not use the design tools to create/alter objects.
December 12, 2007 at 11:53 pm
Dear all,
Thanks for your replies.
I wrote a simple program with SMO, but why the result was different to the script which was generated manually :
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace On_Try_DBScript
{
class Program
{
static void Main(string[] args)
{
ServerConnection sc = new ServerConnection();
Server s = new Server(sc);
sc.ServerInstance = "dev7";
Database mydb01 = s.Databases["test01"];
Table mytable01 = mydb01.Tables["company"];
StringCollection stcol = mytable01.Script();
foreach (string sline in stcol)
Console.WriteLine(sline);
}
}
}
The result of this Console Application :
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[company](
[company_code] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[company_desc] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[company_address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY]
The result generated manually :
USE [test01]
GO
/****** Object: Table [dbo].[company] Script Date: 12/13/2007 14:29:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[company](
[company_code] [char](3) NOT NULL,
[company_desc] [varchar](50) NULL,
[company_address] [varchar](50) NULL,
CONSTRAINT [PK_company] PRIMARY KEY CLUSTERED
(
[company_code] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
December 13, 2007 at 12:24 am
Dear all,
Microsoft said :
SQL Server 2005 Books Online (September 2007)
Script Method
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
The Script method generates a Transact-SQL command batch that can be used to re-create the Microsoft SQL Server component referenced by the SQL Distributed Management Objects (SQL-DMO) object.
How do you feel ?
December 13, 2007 at 6:01 am
It's possible they're remove it in the future. I'm looking at the 2008 documentation. It's there and says the same thing. It's pretty basic functionality, generate a script. Even if the precise method ".script" is removed in the future, another method will take its place.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 13, 2007 at 9:22 am
onlo (12/13/2007)
Dear all,Microsoft said :
SQL Server 2005 Books Online (September 2007)
Script Method
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
The Script method generates a Transact-SQL command batch that can be used to re-create the Microsoft SQL Server component referenced by the SQL Distributed Management Objects (SQL-DMO) object.
How do you feel ?
it's not that the Script() method is deprecated. It's that DMO as a whole is deprecated. You took this quote from a page describing a DMO method.
DMO is the old native api. it is deprecated in 2005.
the new managed api is called SMO. Nothing in SMO is deprecated in 2005 as it first shipped in the 2005 release.
---------------------------------------
elsasoft.org
December 13, 2007 at 9:56 am
onlo (12/12/2007)
Dear all,Thanks for your replies.
I wrote a simple program with SMO, but why the result was different to the script which was generated manually :
there are many options that you can set in SMO to control what is included in the generated scripts. For example, have a look at the ScriptingOptions class in SMO.
For concrete examples, have a look at the source code for scriptdb, the first post I made in this thread.
---------------------------------------
elsasoft.org
December 13, 2007 at 9:16 pm
Dear jezemine,
"ScriptingOptions" works.
Thanks a lot.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply