September 11, 2003 at 3:59 am
I would like to generate a SQL script of an object with a SQL script. Is it possible or is it a build in function into EM
Manie
September 11, 2003 at 4:08 am
Hi Manie,
quote:
I would like to generate a SQL script of an object with a SQL script. Is it possible or is it a build in function into EM
in EM right-click on the db in question select All tasks -> Generate SQL Script. You might have to click also on the Show All button.
Now you can select the object you want. Also take a look at the option tab
HTH
Frank
Wenn Englisch zu schwierig ist?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 11, 2003 at 4:11 am
Hi Frank,
I know how to do that. I want to creat a procedure that can do it for me. I want actual SQL code. for expl. select * from....
Thanks
Manie
September 11, 2003 at 4:13 am
Hi Frank,
I've been reading the thread about 100 times, and I'm still not sure. I think what Manie wants is to know if there is some kind of sp/xp that returns a script for a given object, like SP_GENERATESCRIPT(database_name, object_name, ...).
If it is not Manie's question, it is mine. Do you know about any procedure that might do the trick ?
Thanks.
CVM.
September 11, 2003 at 4:14 am
Hi CVM
It is exactly what i want.
Manie
September 11, 2003 at 4:16 am
Ok, my mistake...
What about taking a look at sp_helptext and what this procedure does and maybe mimic it?
Frank
Wenn Englisch zu schwierig ist?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 11, 2003 at 4:19 am
Thanx, will try it
September 11, 2003 at 5:08 am
Here is the Answer...
---------------------------------------------------------------
DECLARE @objDMO int
DECLARE @objDatabase int
DECLARE @dbname varchar(200)
DECLARE @tablename varchar(200)
DECLARE @cmd varchar(300)
DECLARE @temp varchar(8000)
SET @dbname = 'PUBS'
SET @tablename = 'Authors'
EXEC sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
EXEC sp_OASetProperty @objDMO, 'loginsecure', 'true'
EXEC sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
SET @cmd = 'Databases("' + @dbname + '").Tables("' + @tablename + '").Script'
EXEC sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
PRINT @temp
EXEC sp_OADestroy @objDMO
---------------------------------------------------------------------
I found this at http://www.sqlmag.com/Forums/messageview.cfm?catid=29&threadid=18602 really good site..
William O'Malley
September 11, 2003 at 5:08 am
Here is the Answer...
---------------------------------------------------------------
DECLARE @objDMO int
DECLARE @objDatabase int
DECLARE @dbname varchar(200)
DECLARE @tablename varchar(200)
DECLARE @cmd varchar(300)
DECLARE @temp varchar(8000)
SET @dbname = 'PUBS'
SET @tablename = 'Authors'
EXEC sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
EXEC sp_OASetProperty @objDMO, 'loginsecure', 'true'
EXEC sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
SET @cmd = 'Databases("' + @dbname + '").Tables("' + @tablename + '").Script'
EXEC sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
PRINT @temp
EXEC sp_OADestroy @objDMO
---------------------------------------------------------------------
I found this at http://www.sqlmag.com/Forums/messageview.cfm?catid=29&threadid=18602 really good site..
William O'Malley
September 11, 2003 at 5:09 am
Just MIGHT be possible, but could get very messy.
A simpler way would be to write a little VB applet and add a reference to the SQLDMO object library to do the hard work for you. I think there's a SCRIPT method on several of the classes in SQLDMO.
Take a look at the help file sqldmo80.hlp in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\.
September 11, 2003 at 5:11 am
Sorry, I defer to the previous 2 posters, pipped at the post!
September 11, 2003 at 5:18 am
womalley
Thanx this really great
Manie
September 11, 2003 at 5:28 am
quote:
womalleyThanx this really great
Manie
I got one for ya that will make you wana get up and dance
the cursor is named GetTriggers but in this code I am scripting all the Procedures.
Sorry about that.. Little naming thing
-----------------------------------------------------------------------
declare @cmd2 varchar(255)
declare GetTriggers cursor fast_forward
for
select 'sp_helptext [' + name + ']' from sysobjects where type = 'P'
open GetTriggers
fetch next from GetTriggers into @cmd2
while @@fetch_status = 0
begin
exec (@cmd2)
print 'GO'
fetch next from GetTriggers into @cmd2
end
close GetTriggers
deallocate GetTriggers
-------------------------------------------------------------------
This works Just change the type to match what ever you want to script.
If you are not sure of the Type then run this first
SELECT * FROM SYSOBJECTS
William O'Malley
Edited by - womalley on 09/11/2003 05:29:25 AM
September 11, 2003 at 5:36 am
womalley,
This one is much faster and better. Can you manage to get something that scripts JOBS? I tried it with these but to no avail. I know that the jobs information gets stored in the tables
sysjobservers sysjobsteps sysjobschedules
sysjobs
On the DB MSDB. and these select from Sysobjects
Thanx for your help so far it is much appreciated.
Manie
September 11, 2003 at 5:59 am
quote:
womalley,This one is much faster and better. Can you manage to get something that scripts JOBS? I tried it with these but to no avail. I know that the jobs information gets stored in the tables
sysjobservers sysjobsteps sysjobschedules
sysjobs
On the DB MSDB. and these select from Sysobjects
Thanx for your help so far it is much appreciated.
Manie
Do you mind a little VB ??
You can use SQL Distributed Management Objects (SQL-DMO) to obtain a list of jobs. Use the JobServer object and iterate through the Jobs collection. To obtain each job's script, you can call the Scripts method for that job. Here's what you should do:
Start Visual Basic (VB) 6.0.
Start a standard EXE project.
Add a command button.
Double-click the new command button to get the click event code window.
From the main VB menu, select Project, References. Scroll through the References list to find the Microsoft SQLDMO Object Library and add it. Try to use the .dll file that shipped with the version of SQL Server you're running the .dll against. You can find the .dll version by looking at the location the .dll comes from in the window you have open.
CODE:
' Use oServer to connect to the appropriate SQL Server.
Dim oServer AS New SQLDMO.SQLServer ' Server object
Dim oJob AS SQLDMO.Job ' Holds each job object
Dim myOutFile AS Integer ' Holds file handle
'Connect to the server using trusted authentication.
oServer.LoginSecure = True
oServer.Connect ("(local)") ' Change if required
' Should check oServer.Status
'Open up an output file and write header line.
myOutFile = FreeFile
Open "C:\ALLJOBS.SQL" FOR Output Shared AS #myOutFile ' Change if required
Print #myOutFile, "-- Start of SQL to recreate all jobs" + vbCr
' Iterate through all the jobs; get name or script.
FOR Each oJob IN oServer.JobServer.Jobs
' Print #myOutFile, oJob.Name ' Get job name only and send to file.
Print #myOutFile, oJob.Script ' Get CREATE script and send to file.
NEXT
'Closing comment in file
Print #myOutFile, "-- End of SQL to recreate all jobs" + vbCr
' Clean up
Set oServer = Nothing
Close #myOutFile
' End of code to paste
If you want to script jobs interactively, you can do so from Enterprise Manager by right-clicking jobs and choosing All Tasks, Generate SQL Script.
Hope this helps
William O'Malley
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply