How to exec stored proc dynamically

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub(@.listOnly bit=0, @.maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_RebuildIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMain(@.dbName sysname, @.listOnly bit=0,
@.maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @.dbName

Declare @.execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @.dbName
If (@.@.FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @.dbName + ''''
Return -1
End

While(@.@.FETCH_STATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @.dbName
Print Char(13)
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
EXEC sp_executesql @.execstr, N'@.listOnly bit, @.maxFrag Decimal',
@.listOnly, @.maxFrag
Fetch crDbs INTO @.dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@.netzero.comI believe if you change:
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
to
Set @.execstr = '[' + @.dbName + '..sp_RebuildIndexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub 0, 30.0
END'

Be sure not to run "exec master..sp_RebuildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.|||Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL..sp_RebuildIndexesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.developersdex.com ***|||Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@.execstr).

SET @.execstr = 'USE ' + @.dbname + ' exec sp_RebuildIndexesSub ' +
RTRIM(@.listOnly) + ',' + RTRIM(@.maxFrag)
EXEC (@.execstr)|||Got it. Had to change to this

Set @.execstr = @.dbName + '..sp_RebuildIndexesSub'
Exec @.execstr @.listOnly, @.maxFrag

thanks
Sunit|||You are right. Your code is much cleaner :)

How to Encrypt the Stored Procedures in SQL 2005 Express

Dear All,

I am using SQL 2005 Express, and i need to Encrypt all my Stored Procedure while deploying in my Production Server.

Help me out to do.

Hi,

specify the procedure command with WITH ENCRYPTION and SQL Server will create the procedures encrypted.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to encrypt sp

We have a product which uses a lot of stored procedures as our business
logic layer.we don't want our customers see the boday of the stored
procedures or probably change it.I know that we can encrypt the sps but I
also know that there is a very easy method to decrypt it as well.Is there a
better way to protect our sps from being viewed and changed?
Is there any new thing in Yokun version in this regards?
ThanksHi
See today's thread "Encryption problem" in
microsoft.public.sqlserver.programming
Nothing new in SQL Server 2005.
Reards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ray5531" <RayAll@.microsft.com> wrote in message
news:ufEfqX0SFHA.3556@.TK2MSFTNGP10.phx.gbl...
> We have a product which uses a lot of stored procedures as our business
> logic layer.we don't want our customers see the boday of the stored
> procedures or probably change it.I know that we can encrypt the sps but I
> also know that there is a very easy method to decrypt it as well.Is there
> a better way to protect our sps from being viewed and changed?
> Is there any new thing in Yokun version in this regards?
> Thanks
>

How to encrypt and decrypt stored procedures?

I encrypt my procedures using with encryption clause, but I do not how to decrypt again.

Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?

Thanks

Haydee

Decryption is weak and can be cracked by searching on google for the specific algorithms, there was a thread sometime ago, which might be useful to you:

http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/34b309b76ba574b4

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens is right, the procedure encryption is actually referred to as obfuscation in Books Online. Also, there is no SQL Server command for decrypting it back.

Thanks
Laurentiu

|||

Thanks for your comments

and is there a tool in Sql Server 2005 in order to protect the code? What can I do? I need to install a project in the customer, and I would like to protect it.

Thanks again for your help.

Haydee

|||You could use third party components to accomplish this, there sure can be found some by searching in google for them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI,

I am using MSDE 2000 and I will be deploying it with my software application. I have invested a good bit into my database schema and I don't want it to be viewed by others.

I can not see why some user can not take the .mdf (multiple mdf's actually) and sp_AttachDB or attach them to their instanced SQL server using EM. I of course do not want this.

Maybe someone can clear up the limitations and types of SQL security that can assure no one can simply attach the MDF to see the structure, let alone the data.

As far as I can see there is Network security as to authentication for a live/instanced SQL server and this has no ability to prevent an MDF from being re-attached and viewed/queried.

I also see EncryptByPassPhrase which I can use prior to executing a query (if I understand this process which is data remains in encrypted state until its about to be used, then decrypted in memory (I presume ? otherwise someone could grab a snapshot of the mdf while it's in decrypted state ? {or SQL server has a temp region when using encryption where it places the decrypted data I take it}) and then I have to encrypt it again after processing.

Neither of these look like they can obfuscate or lock the db schema information, such as table names, structures, fields, field types/attributes etc.

Sooooooo.....

How can I prevent a user from seeing the underlying table structures and does anyone know if column encryption will cost me 10 years off my life time wise on large data sets ?

Thanks

|||

See this recent thread for a discussion of this topic:

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

Thanks
Laurentiu

|||

The only feature for protecting code in SQL Server is the WITH ENCRYPTION clause that we discussed so far. It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution. This is a general problem and for any solution you consider, you should look at how easy it is for someone to find the encryption key.

This is basically a DRM solution, and I have talked about the difficulty of creating an unbreakable DRM solution on other threads, more recently in:

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

Thanks
Laurentiu

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
What is a better solution?We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
>
> What is a better solution?
>|||I see there is a way to do this by adding the user to the
TargetServersRole in MSDB, although it has a downside. See here:
http://www.mcse.ms/message638764.html
Daniel Jameson wrote:
> We got around this by using a queue to disconnect the security.
> 1) create a queue table that takes the name of a job and a status value.
> 2) create a stored procedure that can read the queue, find any pending jobs,
> run sp_startjob for any pending jobs, mark started jobs as complete.
> 3) create SQL Agent job owned by SA that runs once every minute and runs
> that stored procedure.
> 4) user inserts job name in queue and waits ~1 min.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
> "Laurence" <laurencen@.eurostop.co.uk> wrote in message
> news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
> >I thought I could do this by granting permission to a few MSDB
> > procedures:
> >
> > grant execute on sp_help_jobhistory to UserRole -- view job history
> > grant execute on sp_help_job to UserRole -- view job
> > grant execute on sp_start_job to UserRole -- start job
> >
> > but I get an error:
> >
> > Server: Msg 14262, Level 16, State 1, Line 1
> > The specified @.job_name ('Name Of Job') does not exist.
> >
> > I looked at the sysjobs_view in MSDB (below) it looks like only the
> > owner, SysAdmins and TargetServersRole (what is this?) can view the
> > jobs. If I alter this view to include my UserRole this allows them to
> > start the job, but I don't want to do this because it is a hack.
> >
> > SELECT *
> > FROM msdb.dbo.sysjobs
> > WHERE (owner_sid = SUSER_SID())
> > OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> > OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
> >
> >
> > What is a better solution?
> >

How to edit Stored Procedures ?

I have two questions:

1) Is it possible to rename a SQL table –or- copy the content of a table into a new table ?

2) How to replace the renamed or replaced table name inside the code of Stored Procedures that references it ?

1.

Copy only schema
Select * into NewTable from table1 where 1=2;

Copy schema with data
Select * into NewTable from table1;

2.

use "sp_rename" stored procedure
http://doc.ddart.net/mssql/sql70/sp_ra-rz_11.htm

|||Thank you very much,Girijesh.

How to dynamically assign database name in query or store procedure?

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @.MyDatabaseName varchar(30)

set @.MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

declare @.cmd nvarchar (2000)

declare @.MyDatabaseName nvarchar(30)

select @.MyDatabaseName = 'northwind'
select @.cmd = 'SELECT * from '+ @.MyDatabaseName +'.dbo.employees'

exec (@.cmd)

|||

It is much easier and cleaner if you simply deploy your stored procedure in each database. Using dynamic SQL has lot of security implications and you don't really simplify your code.

For a future version of SQL Server, we are looking at features that will enable you to parameterize identifiers without using dynamic SQL. And also the ability to deploy SPs in one module & resolve objects in the execution context database.

|||

Thanks Uma and Joeydj

I can't use dynamic query like Joeydj, simply because my stps are huge; but I am curious about Uma's statement about the security implication in Dynamic query. Could you elaborate a little Uma? thanks

|||

For one, you need to grant more permissions to users on your SPs if you use dynamic SQL. So you increasing the attack surface area of your database by using dynamic SQL. Additionally, if you don't protect against malicious inputs then you are vulnerable to SQL injection attacks which can compromise the database, entire server or network. Lastly, there is also the performance and maintainence aspect of dynamic SQL depending on the usage. So there are many risks in using dynamic SQL. However, for some problems in SQL Server there is no way other than using dynamic SQL (like parameterizing DDL statements, running DDLs against multiple dbs etc). But you can do these carefully by protecting your code against SQL injection attacks, using QUOTENAME for values that can be used as identifiers etc.

Below is a good link on the issues of using dynamic SQL in SQL Server. Also, search the WWW for "SQL Injection" and you will find lot of articles.

http://www.sommarskog.se/dynamic_sql.html

|||

Thank you Uma for your explaination and thanks for the link about dynamic sql. It's a great article!

appreciated.

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||What if he doesn't have EM? He didn't say what version he was on. I only
have 2k5 with SMS on my dev box. This version seems to have resorted to
dumping procs out as strings submitted to sp_executesql for some ungodly
reason. This has frustrated me to no end!!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148392984.143435.54080@.j33g2000cwa.googlegroups.com...
> do you mean the source code?
> right click somewhere in the procedures window in Enterprise Manager
> all tasks-->generate sql script, select the procs you want to
> script,click on the options tab and select one file per object
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> samtil...@.gmail.com wrote:
>|||For SQL Server 2005 check out Bill graziano's scripting tool
http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
and
http://weblogs.sqlteam.com/billg/ar...12/24/8613.aspx
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Your best bet is to use one of the API's which has such functionality built-
in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/in...ip
t.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroups
.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||Oh, yes!! Thanks...that fits the bill perfectly!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148395150.393418.229440@.j33g2000cwa.googlegroups.com...
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/ar...12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>

>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO"
& vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO"
& vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFile
Number
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProc
s, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "G
O"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFi
leNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & v
bCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO
" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't kno
w how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only r
eturns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) cat
alog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not refle
ct that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed proc
edures - make sure you
test it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegroup
s.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||Your best bet is to use one of the API's which has such functionality built-
in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/in...ip
t.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroup
s.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>

>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO"
& vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO"
& vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFile
Number
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProc
s, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "G
O"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFi
leNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & v
bCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO
" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't kno
w how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only r
eturns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) cat
alog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not refle
ct that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed proc
edures - make sure you
test it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegrou
ps.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||What if he doesn't have EM? He didn't say what version he was on. I only
have 2k5 with SMS on my dev box. This version seems to have resorted to
dumping procs out as strings submitted to sp_executesql for some ungodly
reason. This has frustrated me to no end!!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148392984.143435.54080@.j33g2000cwa.googlegroups.com...
> do you mean the source code?
> right click somewhere in the procedures window in Enterprise Manager
> all tasks-->generate sql script, select the procs you want to
> script,click on the options tab and select one file per object
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> samtil...@.gmail.com wrote:
>> I would like to dump each of the stored procedures in one database to a
>> separate file.
>> Programmatically would be great (e.g., start with SELECT * FROM
>> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>> My last resort is to use some tool already written by somebody else
>> that achieves this same goal, especially if I could get the source code
>> on how to do it myself.
>> Any ideas?
>|||For SQL Server 2005 check out Bill graziano's scripting tool
http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
and
http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Your best bet is to use one of the API's which has such functionality built-in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroups.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||Oh, yes!! Thanks...that fits the bill perfectly!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148395150.393418.229440@.j33g2000cwa.googlegroups.com...
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>
>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO" & vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO" & vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProcs, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & vbCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't know how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only returns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) catalog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not reflect that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed procedures - make sure you
test it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegroups.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>|||It's absolutely braindead that they left this out of the 2005 Management
Studio.
"SQL" wrote:
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>