Generating sql scripts

Hi,
Please guide me in generating sql scripts for table, with the drop
statements in it, from the management studio of sql 2005, Currently only
create statement is generating, and drop statement is not there in it.
Rupesh Mondal.Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
Scripting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> Hi,
> Please guide me in generating sql scripts for table, with the drop
> statements in it, from the management studio of sql 2005, Currently only
> create statement is generating, and drop statement is not there in it.
> Rupesh Mondal.|||But Mr.Tobor which option to set true in the scripting option, inorder to get
the drop statement in it, before the create statement.Please reply me ASAP
Rupesh Mondal.
"Tibor Karaszi" wrote:
> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> Scripting).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> > Hi,
> >
> > Please guide me in generating sql scripts for table, with the drop
> > statements in it, from the management studio of sql 2005, Currently only
> > create statement is generating, and drop statement is not there in it.
> >
> > Rupesh Mondal.
>
>|||Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
find is the "Include IF NOT EXIST clause" which is a bit different.
I think this option is available at the SMO level, but that would require you to code your own
generate script application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> But Mr.Tobor which option to set true in the scripting option, inorder to get
> the drop statement in it, before the create statement.Please reply me ASAP
> Rupesh Mondal.
> "Tibor Karaszi" wrote:
>> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
>> Scripting).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
>> > Hi,
>> >
>> > Please guide me in generating sql scripts for table, with the drop
>> > statements in it, from the management studio of sql 2005, Currently only
>> > create statement is generating, and drop statement is not there in it.
>> >
>> > Rupesh Mondal.
>>|||here's an open source app that will generate scripts - it would be a
starting point for you if you go that route:
http://www.codeplex.com/scriptdb
On Apr 19, 12:59 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
> find is the "Include IF NOT EXIST clause" which is a bit different.
> I think this option is available at the SMO level, but that would require you to code your owngeneratescriptapplication.
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> > But Mr.Tobor which option to set true in the scripting option, inorder to get
> > the drop statement in it, before the create statement.Please reply me ASAP
> > Rupesh Mondal.
> > "Tibor Karaszi" wrote:
> >> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> >> Scripting).
> >> --
> >> Tibor Karaszi,SQLServer MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> >>news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> >> > Hi,
> >> > Please guide me in generatingsqlscripts for table, with the drop
> >> > statements in it, from the management studio ofsql2005, Currently only
> >> > create statement is generating, and drop statement is not there in it.
> >> > Rupesh Mondal.

generating scripts in SqlServer 2005?

Okay, in Sql 2000, when I wanted to generate scripts, I could easily have
both the create and drop commands scripted into the same document with one
step. How do I do that with Sql 2005. I can generate the script for
creating the table and for dropping the table, but I cannot see the flag
where I can have both scripted at the same time.> step. How do I do that with Sql 2005.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1sql

Generating scripts in SQL 2005

In SQL 2000 EM you were able to generate a script that only contained GRANT statements, (by unchecking the CREATE & DROP commands on the 2nd tab, and selecting Object Permissions on the 3rd tab). From what I can see in SQL 2005 MS you can't do this.

All that you can seem to do is generate a sciprt containing all the CREATE statements with the GRANT statement(s) after each object.

This is a bit of a pain as I want to script all the object permissions, without having to wade through hundreds of CREATE TABLE and CREATE PROCEDURE statements.

How can get just a script of GRANT statements?

Thanks.

It is not possible to generate scripts for object permissions through Management Studio like you could in Enterprise Manager. However, you can write a small SMO program to get the information the you need. This example should get you started on how to generate GRANT scripts for stored procedures.

ObjectPermissionInfo[] a = new ObjectPermissionInfo[5];

Database db = srv.Databases["pubs"];

foreach (StoredProcedure sp in db.StoredProcedures)

{

a = sp.EnumObjectPermissions();

foreach (ObjectPermissionInfo op in a)

{

Console.WriteLine("GRANT " + op.PermissionType.ToString() + " ON " + op.ObjectName.ToString() + " TO " + op.Grantee.ToString());

}

}

~Peter

|||Thanks for answering, I'll try what you suggest.

Generating Create Alter Drop Statements

Im using SQL Server 2005 Standard and am trying to get T-SQL to do something that can be done easily through the UI, Genernate SQL Scripts for Create Alter Drop for objects. In the SQL manager its as easy a right clicking a DB Object and going to tasks.

Im wondering if there is a way of doing that through T-SQL.

Thanks in advance!

There is no easy way to do the same from TSQL. Best is to use SMO or SSIS to script out the objects. You could use the built-in metadata function OBJECT_DEFINITION or sp_helptext system stored procedure or query system catalog views. But you will have to code lot of the logic to determine dependencies, generate drop statements or ALTER TABLE etc. So it is best to use one of the methods above.