Programmatically generate script for an SQl object
-
Hi guys,
Does anybody know how can I generate a script for any SQL object, let it be a stored procedure or function or table? I need to have a SQL job that will daily goes through the database finds all modified objects and generates scripts for them and store these files somewhere in the network. With Job there is no problem, find all modified objects - not a problem too. But how to generate a script for this objects?
Will be very thankful for any ideas.
Thanks,
Alex
-
Alex
Different types of objects have different ways of generating the scripts. Views, stored procedures and functions are similar, but constraints, indexes, tables and users are different. Your best bet is to run Profiler (is it still called that in 2005?) while you generate scripts for different types of object in SSMS, and see how it does that from the metadata views.
John
-
Thanks John.
Hope that'll help.
-
Based on what you're suggesting, you might want to look at doing a little SMO programming. You can quite simply generate a CREATE script for various objects from there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hey Grant!
What do you mean by SMO programming? The problem I have using T-SQL to generate CREATE scripts for those objects, which were modified (or simply saying for the list of objects which could be changed).
Thanks,
Alex
-
Oops, should have added, SMO means SQL Management Objects. It's in the BOL, including decent (if not good) references & samples.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Crud I hate that. My first response went into the ether and my second got posted so I look like a schmuck. Sorry about that. What I said was:
Generating objects strictly through TSQL isn't fun or easy or simple. As you've found, identifying what was modified is pretty easy. Generating code to create objects as varied as a table, a stored proc, a function, an index, a constraint... each one requires different syntax that you'll need to allow for in any kind of creation script, let alone that you'll probably have to reference multiple dynamic management views, etc...
SMO just puts the same tools to work that are used by the SSMS. You can walk the structure to an object and tell it to generate a create script and you'll get a very clean script, no muss, no fuss. It's going to be a lot easier than trying to generate them all using TSQL.
Easier still would be to look at some of the third party code management tools. Idera SQL Change Manager, Embarcadero Change Manager, Red-Gate Schema Compare (although suggesting Red-Gate tools lately has been a good way to get dog-piled) all have a mechanism of tracking changes & generating scripts. This could be a buy vs. build situation since the cost of some of these tools (Red-Gate's in particular) is pretty small.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks, Grant. I'll investigate these tools. May be you're right, and there is no need to spend hours generating some solution which is already built for us:-)
Thanks again!
Alex
-
Hi Alex, Here's how I programmatically script out stored procedures, views, and triggers from Sql-2000 so as to back them up on a nightly basis. I have created an Active-X task within a DTS package and then have the following function. With a little bit if experimenting, you could probably get it to loop through all databases and only script out things that have changed. There's a lot of commented out code in here, but thought it might be useful some day to leave it in. Hopefully this gives you one more option by using tools you already own.
FUNCTION MAIN()
Dim oServer ''As SQLDMO SQLServer
Dim oDatabase ''As SQLDMO Database
Dim oTbl ''As SQLDMO Table
Dim oProc ''As SQLDMO Procedure
Dim oView ''As SQLDMO View
Dim oLogin ''As SQLDMO Logins
Dim oUsr ''As SQLDMO Users
Dim oTrg ''AS SQLDMO Trigger
Dim oPath
Dim sServerName
SET oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect("SERVERNAME\INSTANCE")
sServerName = "SERVERNAME\INSTANCE"
oPath = "\\network\path\goes\here\"
''-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'' The next couple of sections are commented out, but I wanted to leave a reference as to how they could be done.
''------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'' FOR EACH oLogin IN oServer.Logins
'' ''SQLDMOScript_AppendToFile=256
'' ''SQLDMOScript_ToFileOnly=64
'' ''SQLDMOScript_PrimaryObject=4
'' ''SQLDMOScript2_LoginSID=1048576
'' ''SQLDMOScript2_EncryptPWD=128
'' ''SQLDMOScript2_UnicodeFile=4
'' oLogin.Script 256 + 64 + 4, oPath & sServerName & "_LOGINS.txt",1048576 + 128 + 4
'' NEXT
''SET oDatabase = oServer.Databases("DATABASE_NAME")
''FOR EACH oUsr IN oDatabase.Users
'' ''SQLDMOScript_AppendToFile=256
'' ''SQLDMOScript_ToFileOnly=64
'' ''SQLDMOScript_PrimaryObject=4
'' IF oUsr.SystemObject = FALSE THEN
'' oUsr.Script 256 + 64 + 4, oPath & sServerName & "_" & oDatabase.Name & "_USERS.txt"
'' END IF
''NEXT
''FOR EACH oTbl IN oDatabase.Tables
'' ''SQLDMOScript_AppendToFile=256
'' ''SQLDMOScript_Indexes=73736
'' ''SQLDMOScript_OwnerQualify = 262144
'' ''SQLDMOScript_ObjectPermissions=2
'' ''SQLDMOScript_ToFileOnly=64
'' ''SQLDMOScript_PrimaryObject=4
'' IF oTbl.SystemObject = FALSE THEN
'' oTbl.Script 256 + 73736 + 262144 + 2 + 64 + 4, oPath & sServerName & "_" & oDatabase.Name & "_TABLES.txt",,8388608
'' END IF
''NEXT
''--------------------------------------------------------
'' NOW ONTO THE REAL CODE
''---------------------------------------------------------
SET oDatabase = oServer.Databases("DATABASE_NAME")
FOR EACH oProc IN oDatabase.StoredProcedures
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_ObjectPermissions=2
''SQLDMOScript_ToFileOnly=64
''SQLDMOScript_PrimaryObject=4
IF oProc.SystemObject = FALSE THEN
oProc.Script 256 + 2 + 64 + 4, oPath & sServerName & "_" & oDatabase.Name & "_PROCS.txt"
END IF
NEXT
FOR EACH oView IN oDatabase.views
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_ObjectPermissions=2
''SQLDMOScript_ToFileOnly=64
''SQLDMOScript_PrimaryObject=4
IF oView.SystemObject = FALSE THEN
oView.Script 256 + 2 + 64 + 4, oPath & sServerName & "_" & oDatabase.Name & "_VIEWS.txt"
END IF
NEXT
FOR EACH oTbl IN oDatabase.tables
FOR EACH oTrg IN oTbl.triggers
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_ToFileOnly=64
''SQLDMOScript_PrimaryObject=4
IF oTrg.SystemObject = FALSE THEN
oTrg.Script 256 + 64 + 4, oPath & sServerName & "_" & oDatabase.Name & "_TRIGGERS.txt"
END IF
NEXT
NEXT
''clean up
oServer.DisConnect
SET oServer = Nothing
Main = DTSTaskExecResult_Success
END FUNCTION
I can't take the credit though, I did not write this myself. I pulled it off from another formum somewhere, but can't remember where? Thanks to the programmer out there that wrote it.
Good Luck, Dave
-
Thanks Dave.
Will test it right now.
I'm glad I'm not the only one who met this problem:-)
-
To script procedures, triggers and views you can use OBJECT_DEFINITION function in sql 2005 and it is very simpled...
Check BOL for more details...
SELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Object Definition];
OBJECT_DEFINITION applies to the following object types:
C = Check constraint
D = Default (constraint or stand-alone)
P = SQL stored procedure
FN = SQL scalar function
R = Rule
RF = Replication filter procedure
TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
IF = SQL inline table-valued function
TF = SQL table-valued function
V = View
MohammedU
Microsoft SQL Server MVP -
Now that's sweet. I had not known of this one prior to today. Thanks for posting it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks Mohammed.
Except the fact it doesn't script the tables and could not be used with SQL server 2000 it works perfect. I tested it - the easiest way to generate scripts. Don't understand why MS did not provide the same functionality for tables:-)
Hope my project will be migrated to SQL 2005 soon))
-
You might want to check this out. it will generate scripts for ALL objects in any 2000 or 2005 db, a separate file for each. it's useful for getting all your objects under source control.
http://www.elsasoft.org/tools.htm
---------------------------------------
elsasoft.org -
Thanks Jezemine,
Actually I've wrote such programm using in C#(using SQLDMO com object) myself. I think this elsasoft used the same com object:-)
So in case you need it, I can share, but interface is not ready yet:-) Will complete in 2 weeks.
Thanks,
Alex
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply