Scripting "Create to File" for multiple objects

What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database?

I would like a separate file for each object (table, view, or stored procedure).

Could someone point me in the right direction?

Thanks,

Paul
Seperate File - Use SMO programming using .NET and create it.

Scriptifying in SQL 2005 Express

How does one scriptify all database objects using SQL Server 2005 Express / Server Management Studio Express?

I can see how to scriptify individual objects, but I'm not so keen on doing that for 200+ objects!

Thanks in advance

Dave

davebartlett.net

1. Right button on database

2. Select Tasks

3. select Generate Scripts

4. follow the instructions

Madhu

|||

Thanks, that saved a lot of time.

Do you know how to script all objects, but generate 1 script file per object?

The Wizard doesn't seem to allow that mode, it seems to put all generated scripts in a single file, which is difficult to navigate through when editing.

|||

for that you should apply SP2 ... sp2 have all the feature like in SQL 2000... in "OutPut Option window you have all these features... if it is not there in your machine means, yours is not SP2

Madhu

|||

Yes, I've installed SP2 and I can now scriptify each object to a separate file.

This is a great feature, thanks for your help.

Scriptifying in SQL 2005 Express

How does one scriptify all database objects using SQL Server 2005 Express / Server Management Studio Express?

I can see how to scriptify individual objects, but I'm not so keen on doing that for 200+ objects!

Thanks in advance

Dave

davebartlett.net

1. Right button on database

2. Select Tasks

3. select Generate Scripts

4. follow the instructions

Madhu

|||

Thanks, that saved a lot of time.

Do you know how to script all objects, but generate 1 script file per object?

The Wizard doesn't seem to allow that mode, it seems to put all generated scripts in a single file, which is difficult to navigate through when editing.

|||

for that you should apply SP2 ... sp2 have all the feature like in SQL 2000... in "OutPut Option window you have all these features... if it is not there in your machine means, yours is not SP2

Madhu

|||

Yes, I've installed SP2 and I can now scriptify each object to a separate file.

This is a great feature, thanks for your help.

Scripter and Database Mail

I currently have VB.Net program that scripts out all objects in a server for DR purposes. I just realized that I don't have DB Mail included. I have not been able to find a way to use the Scripter for this. Does anyone know if the Scripter can do DB Mail or will I need to resort to writing queries?

Hi greenie,

DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.

|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery file

Scripter and Database Mail

I currently have VB.Net program that scripts out all objects in a server for DR purposes. I just realized that I don't have DB Mail included. I have not been able to find a way to use the Scripter for this. Does anyone know if the Scripter can do DB Mail or will I need to resort to writing queries?

Hi greenie,

DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.

|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery filesql

script to drop all 'user' objects

Can someone point me to some sql script that drops all objects for a given
user ?
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael,
Why do you want to drop all of the objects? Maybe you'd prefer to just
change the ownership of the object? sp_changeobjectowner.
HTH
Jerry
"Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
news:exFyHU1yFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Can someone point me to some sql script that drops all objects for a given
> user ?
> --
> Michael Tissington
> http://www.oaklodge.com
> http://www.tabtag.com
>
>|||Jerry,
Thanks but I need to drop all the objects for a given users. Too complicated
to explain the details but partially to do with running DTS packages and
limitations in TRUNCATE
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u37AVc1yFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Michael,
> Why do you want to drop all of the objects? Maybe you'd prefer to just
> change the ownership of the object? sp_changeobjectowner.
> HTH
> Jerry
> "Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
> news:exFyHU1yFHA.3720@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to drop all objects
that related to one specified user. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, I am afriad we cannot do this directly. However here
are some thoughts
1. get UID from sysusers
2. get object names from sysobjects with this UID
3. according the the name column of Step 2, and delete them separately.
Admittedly, there is a lot of jobs to do. Also, let's wait to see whether
others have the same experience.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Script to delete all objects

Can anyone point me to script to delete all objects owned by a user before I
delete the user from a database?
Thanks.
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comHello
We can run the following code to find the objects owned by a user:
declare @.usrname as varchar(30)
set @.usrname='sqluser'
select name,xtype from sysobjects where uid = user_id(@.usrName)
However, I am afraid that we may need to delete the objects manually one by
one.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Script to copy permissions for all objects given to a user or a role

How would I, using a sql script, copy permissions assigned to a user
or a role in one or more databases to another user or a role in their
respective databases?

Help appreciatedgudia97@.yahoo.com (gudia) wrote in message news:<2130f7ff.0407071438.275b76f3@.posting.google.com>...
> How would I, using a sql script, copy permissions assigned to a user
> or a role in one or more databases to another user or a role in their
> respective databases?
> Help appreciated

First of all, I would avoid granting permissions to users, since this
becomes difficult to manage - if you always use roles, then things are
much easier. Even if you have only one user in a role today, at least
you won't have any extra work when you need to add a second one. Also,
you should keep a permissions script for each role, so you know which
permissions are correct, and you can run it for multiple roles.

To solve your issue right now, you could add one role to another, if
that's appropriate. If not, then you can reverse-engineer a
permissions script using a query like this:

select
case protecttype
when 205 then 'grant '
when 206 then 'revoke '
end +
case action
when 26 then ' references '
when 193 then ' select '
when 195 then ' insert '
when 196 then ' delete '
when 197 then ' update '
when 224 then ' execute '
end +
' on ' + object_name(id) + ' to TargetRole '
+ case when protecttype = 204 then ' with grant option' else '' end
from
sysprotects
where
uid = user_id('SourceRole')

See sysprotects in Books Online for more information. Note that this
query doesn't handle SELECT or UPDATE permissions on explicit columns,
but only where the permissions are on the whole table. It also does
not handle statement permissions (CREATE TABLE etc.), but you can
easily write a similar query for that.

Simon|||Simon:

Thanks. This will help as a starting point. I do use roles as oppossed
to users for permissioning.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Script to change all database object owners at one time

Hi:
Does any one know where I can find a script that will change ownership of
all database objects at once. I took over control of a large database and
changing owners using sp_changeobjectowner one at a time will take forever.
Thanks,
CharlieYou could use a cursor to retrieve the objects that you want to change
(tables, views, stored procedures, user defined functions...) and call
sp_changeobjectowner on each object.
You can find examples of cursors within Books Online.
Keith
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||Since this is a one time thing, look at the UNdocumented stored procedure
sp_MSforeachtable.
This will give you an idea on how to use it:
http://www.databasejournal.com/feat...cle.php/3441031
http://www.dbazine.com/sql/sql-articles/larsen5
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||This will generate the script for you for all user-defined views, tables,
stored procedures and functions that aren't already owned by dbo:
SELECT 'EXEC sp_changeobjectowner
'''+TABLE_SCHEMA+'.'+TABLE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),
'IsMsShipped')=0
AND TABLE_SCHEMA != 'dbo'
SELECT 'EXEC sp_changeobjectowner
'''+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
You can copy the results to the top pane and execute. The only potential
issue is if you have a situation like this:
userA.tableFoo
dbo.tableFoo
Or
userA.tableFoo
userB.tableFoo
Because it will crap out when you try to force dbo to own two objects with
the same name...
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi:
> Does any one know where I can find a script that will change ownership of
> all database objects at once. I took over control of a large database and
> changing owners using sp_changeobjectowner one at a time will take
> forever.
> Thanks,
> Charlie
>|||Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uTuvD0yzFHA.904@.tk2msftngp13.phx.gbl...
> This will generate the script for you for all user-defined views, tables,
> stored procedures and functions that aren't already owned by dbo:
> SELECT 'EXEC sp_changeobjectowner
> '''+TABLE_SCHEMA+'.'+TABLE_NAME+''',''dbo'''
> FROM INFORMATION_SCHEMA.TABLES
> WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),
> 'IsMsShipped')=0
> AND TABLE_SCHEMA != 'dbo'
> SELECT 'EXEC sp_changeobjectowner
> '''+ROUTINE_SCHEMA+'.'+ROUTINE_NAME+''',''dbo'''
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMsShipped')=0
> AND ROUTINE_SCHEMA != 'dbo'
> You can copy the results to the top pane and execute. The only potential
> issue is if you have a situation like this:
> userA.tableFoo
> dbo.tableFoo
> Or
> userA.tableFoo
> userB.tableFoo
> Because it will crap out when you try to force dbo to own two objects with
> the same name...
>
>
> "Charlie@.CBFC" <charle1@.comcast.net> wrote in message
> news:ueFBZXyzFHA.2076@.TK2MSFTNGP14.phx.gbl...
of
and
>sql

Script to "Generate Scripts"

Ok, guys this is a weird question.

my boss wants script the database objects in a daily basis.

he is too lazy to do the "tasks -> generate scripts -> select objects -> and click finish."

Is there a way to automate this task?

I appreciate your help.

Scriptio is a good way to learn this; http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

Script Table, Index, Stored Procedures from Table list

I have a list of approximately 2000 objects that need to create a sql script
to generate these objects. The script will be applied to another datbase to
generate these objects.
The 2000 objects name have been loaded into a Table A.
Please help put together a program that will read the object names from
Table A and generate a sql script for objects.
Thank You,Option#1
Try from Enterprise Manager (Tools -> Generate SQL Scripts)
Option#2
Try Import\Export utility from Enterprise Manager
Thanks,
RK
"Joe K." wrote:

> I have a list of approximately 2000 objects that need to create a sql scri
pt
> to generate these objects. The script will be applied to another datbase
to
> generate these objects.
> The 2000 objects name have been loaded into a Table A.
> Please help put together a program that will read the object names from
> Table A and generate a sql script for objects.
> Thank You,
>|||I would like to way read from a table to "Generate SQL Scripts".
I would like to automate the "Generate SQL Scripts" procedure.
Thanks,
"Ram Kumar Koditala" wrote:
> Option#1
> Try from Enterprise Manager (Tools -> Generate SQL Scripts)
> Option#2
> Try Import\Export utility from Enterprise Manager
> Thanks,
> RK
> "Joe K." wrote:
>|||You'll have to use SQL DMO to do this. See if the following links help:
http://www.karaszi.com/SQLServer/in...rate_script.asp
http://www.databasejournal.com/feat...cle.php/1480901
Anith

Script Out T-SQL Code Programmatically

I'm looking for a way to programmatically (ideally using T-SQL) script
out code objects in order of precedence .
I must make clear that I'm quite satisfied that I have found a way of
listing the names Views, Stored Procs & Functions that need to be
changed in order of precedence (eg if SP spEmployeeUpdate depends on
view vselEmployee which in turn depends on table tblEmployee then if
tblEmployee has been updated you need to recompile (?) vselEmployee
then spEmployeeUpdate). OK, so that's what this post is not about!
So . . . I have a temporary T-SQL table which comprises in order the
code objects which need to be recompiled - all well and good but how
can I script out the objects so I can run the script once and it's
done?
Using the above example, after tblEmployee has been updated I would
like to generate code like this . . .
ALTER VIEW vselEmployee
AS
BEGIN
|
|
END
GO
ALTER PROCEDURE spEmployeeUpdate
AS
BEGIN
|
|
END
GO
I've tried using SysComments.Text (which contains the code) but without
success (truncation of text, loss of formatting etc).
I know that Enterprise Manager allows me to script out a selection of
code objects but 1) I can't control it programmatically; 2) it's not in
any useful order; 3) It uses "DROP" & "CREATE" rather than the
preferred "ALTER" etc.
I've seen several posts on this topic but they tend to get bogged down
on the bit I've already solved.
Any ideas?Hi
You may want to look at using DMO to do this, similar to
http://www.nigelrivett.net/DMOScriptAllDatabases.html
John
"Pete Nurse" wrote:

> I'm looking for a way to programmatically (ideally using T-SQL) script
> out code objects in order of precedence .
> I must make clear that I'm quite satisfied that I have found a way of
> listing the names Views, Stored Procs & Functions that need to be
> changed in order of precedence (eg if SP spEmployeeUpdate depends on
> view vselEmployee which in turn depends on table tblEmployee then if
> tblEmployee has been updated you need to recompile (?) vselEmployee
> then spEmployeeUpdate). OK, so that's what this post is not about!
> So . . . I have a temporary T-SQL table which comprises in order the
> code objects which need to be recompiled - all well and good but how
> can I script out the objects so I can run the script once and it's
> done?
> Using the above example, after tblEmployee has been updated I would
> like to generate code like this . . .
> ALTER VIEW vselEmployee
> AS
> BEGIN
> |
> |
> END
> GO
> ALTER PROCEDURE spEmployeeUpdate
> AS
> BEGIN
> |
> |
> END
> GO
> I've tried using SysComments.Text (which contains the code) but without
> success (truncation of text, loss of formatting etc).
> I know that Enterprise Manager allows me to script out a selection of
> code objects but 1) I can't control it programmatically; 2) it's not in
> any useful order; 3) It uses "DROP" & "CREATE" rather than the
> preferred "ALTER" etc.
> I've seen several posts on this topic but they tend to get bogged down
> on the bit I've already solved.
> Any ideas?
>|||Thanks John, that's excellent code - very clear and well written. I'm
busy now trying to work out what it's doing!

Script out objects

Is there any command to script out tables/stored procedures?
I want to make a process to script out tables every night.

thanks,"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311201526.39f956fb@.posting.google.c om...
> Is there any command to script out tables/stored procedures?
> I want to make a process to script out tables every night.
> thanks,

There's no TSQL command to do this, but you can use the SQLDMO interface to
generate the scripts. Although you could do it from a procedure using
sp_OACreate, it would probably be better to write an external script in your
preferred scripting language - VBScript, Perl, etc. - because it will be
much easier to work with the output files.

Simon|||any sample code in perl or VBScript?

Thanks,|||second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311211101.32d01304@.posting.google.com>...
> any sample code in perl or VBScript?
> Thanks,

Here's a snippet of VBScript (untested) to script all the tables in a
DB - check the docs for the constants available with the Script
method:

Set oSQL = WScript.CreateObject("SQLDMO.SQLServer2")
oSQL.Name = "MyServer"
oSQL.LoginSecure = True
oSQL.Connect

'Script all tables
For Each oTable In oSQL.Databases("MyDatabase").Tables
If Not oTable.SystemObject Then
oTable.Script 4 Or 262144 Or 520093696 Or 131072 Or 2 Or 64,
"C:\OutputFolder\" + oTable.Name + ".sql"
End If
Next

Simon

Script Objects on SQL Server

Hi..

I need a script to export all the objects from my DB, I can't use DTS because this server is on another private LAN , so the only thing that i can do is drop a *.sql file on a common sharing server and my other server will execute it from that location.

What do you recommend? I know i can do it from My Enterprise Manager but i need to run it on a schedule time ....

Anybody has a script for it ? Any clue?

Thanks a lot.
:DIf you don't plan to change your schema, then you can script your DB using EM and save the script as a file.

If changes are anticipated, you can look into using SQL-DMO and write your own script. You can also google "Script database" and am sure you'll find a couple of hits there.|||The schema is changing always ...

It's for the QA guys but they work on a private LAN ... They can't reach our Production LAN..|||Look up this file in your directory structure (scptxfr.exe). My personal edition has it in C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade directory.

Also, check http://database.ittoolbox.com/code/d.asp?d=1691&a=s|||In case you still wonder, this is a sample command line that would generate all objects of PUBS database:

"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s <SERVER_NAME> /I /d pubs /r /f C:\test.sql

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.
Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProce dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.
|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer. SetDefaultInitFields(GetType(StoredProce
dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database wit
h
> about a hundred tables and a few hundred derived objects - views, function
s,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

Script objects

Is there anyway to automate the scripting of tables,views, functions, and
procedures for a database to run nightly?
Thanks,
Doug Stiershttp://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Doug Stiers" <doug@.nospam.com> wrote in message news:epYawYoEEHA.3976@.TK2MSFTNGP12.phx.gbl
..
> Is there anyway to automate the scripting of tables,views, functions, and
> procedures for a database to run nightly?
> Thanks,
> Doug Stiers
>

Script needed to edit open objects in Sybase

Currently I must manually complete the following commands to set a new value
for the "open objects" and "open indexes". I need a way script this process
in a batch file.

COMMANDS RAN: This is nothing to script, these commands obtain the current
values for open objects and open indexes.

isql -Usa -Pmanager -SCOP1_DS
execute sp_countmetadata "open objects"
execute sp_countmetadata "open indexes"
go

RESULTS: below are the results, there are 5124 user objects and 2122 user
indexes. I need a way to be able to multiple these numbers by a set value
and then use the new value and reset the user objects and user indexes. The
question is how do I isolate the results for each result so I can run the
calculations below. Here is the calculation I need:

current user objects * 1.05 = new value
current user indexes * 1.1 = new value

There are 5124 user objects in all database(s), requiring 6985 Kbytes of
memory.

The 'open objects' configuration parameter is currently set to 500.
(return status = 0)
There are 2122 user indexes in all database(s), requiring 5902 Kbytes of
memory.

The 'open indexes' configuration parameter is currently set to 5000.
(return status = 0)

SETTING THE NEW VALUES: Once I have the new values, I can set the ojects and
indexes as seen below.

1> execute sp_configure "open objects", 5380
2> execute sp_configure "open indexes", 2334
3> goAre you perhaps running Sybase? In that case, you'll probably get a more
help by asking this question in a Sybase forum (e.g. comp.databases.sybase)
rather than one dedicated to Microsoft SQL Server. We Microsoft SQL Server
DBAs are spoiled and don't need to deal with those config options anymore
:-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"News East" <Methu969@.yahoo.com> wrote in message
news:bY7Lf.171258$WH.51367@.dukeread01...
> Currently I must manually complete the following commands to set a new
> value for the "open objects" and "open indexes". I need a way script this
> process in a batch file.
> COMMANDS RAN: This is nothing to script, these commands obtain the current
> values for open objects and open indexes.
>
> isql -Usa -Pmanager -SCOP1_DS
> execute sp_countmetadata "open objects"
> execute sp_countmetadata "open indexes"
> go
>
>
> RESULTS: below are the results, there are 5124 user objects and 2122 user
> indexes. I need a way to be able to multiple these numbers by a set value
> and then use the new value and reset the user objects and user indexes.
> The question is how do I isolate the results for each result so I can run
> the calculations below. Here is the calculation I need:
> current user objects * 1.05 = new value
> current user indexes * 1.1 = new value
>
> There are 5124 user objects in all database(s), requiring 6985 Kbytes of
> memory.
> The 'open objects' configuration parameter is currently set to 500.
> (return status = 0)
> There are 2122 user indexes in all database(s), requiring 5902 Kbytes of
> memory.
> The 'open indexes' configuration parameter is currently set to 5000.
> (return status = 0)
>
> SETTING THE NEW VALUES: Once I have the new values, I can set the ojects
> and indexes as seen below.
>
> 1> execute sp_configure "open objects", 5380
> 2> execute sp_configure "open indexes", 2334
> 3> go|||News East (Methu969@.yahoo.com) writes:
> Currently I must manually complete the following commands to set a new
> value for the "open objects" and "open indexes". I need a way script
> this process in a batch file.

I guess that you should be able to read these from syscurconfigs or
sysconfigure. Provided that is, that Sybase has not changed the
location for the configuration parameters since last I saw it, and
that was loooong ago.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Script generation for Objects - Management Studio

How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.

I want to have separate file for each of the objects.

In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.

Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.

Clarity Consulting (http://www.claritycon.com)

|||

Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.

It applies to other objects like procs, funcs, triggers, constraints, FKs etc

|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.