Auto Generating Scripts in SQL Server 2005
-
Hey All
I have a database that is constantly altered to improve its performance. Stored Procedures and Function gets added and changed on a daily basis.
I am looking for an easy method where I can auto generate scripts op Procedures and Functions that I can store in file backup system.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
In SSMS,
Right Click Database --> Tasks --> Generate Scripts
-
Well the problem is that I want this to be an automated process for all by procedures and functions.
If possible I want to auto schedule this for specific intervals eg: 2x a day.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
Good place to start:
USE YourDBNameSELECT * -- CHECK OUT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
-
Well, I have never ran into such a situation so I might not have the best possible solution but what I can think is a way to use the available tools to carry out.
Use SSIS and create a Package.
Step 1 (Execute SQL Task)
Use Create a Database with Date appended to the DBName such as DB_10302009 etc.
Step 2 (Transfer SQL Server Objects)
Choose only those object types that you want in your case SProcs and Functions etc.
Step 3 (Execute SQL Task)
Take a Backup of the DB which you created in Step1
Step 4 (Execute SQL Task)
Drop the Database which was backed up.
This is a work around only and any one can give a suggestion to improvise the solution
And you can actually customize if you want to copy that backup to a different location before actually dropping the database and so on....
Bru MedishettyBlog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
-
I have a C# program that uses SMO to script out objects into individual files.
I call that program with a batch file and then use SVN to commit changes to a repository... that way I can see the diffs, etc using tortoise SVN.
I do this weekly for production. I might miss something once in a while, but it's not come back to haunt me ever.
I think the original C# program is available somewhere on the internets. I modified it to script out only what I want.
Craig Outcalt
Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258 -
i've done this all via TSQL; I happen to also get all the scripts in FK hierarchy order as well.
this has a dependancy for the procedure sp_GetDDL, which gets any tables CREATE TABLE definition in TSQL.
you could simply create a job that calls this one stored procedure, and store the results of the proc in a table or to file or whatever.
see if this is kind of what you are looking for.
CREATE PROCEDURE sp_export_all(@WithData int = 0)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #MyObjectHierarchy
(
HID int identity(1,1) not null primary key,
ObjectId int,
TYPE int,OBJECTTYPE AS CASE
WHEN TYPE = 1 THEN 'FUNCTION'
WHEN TYPE = 4 THEN 'VIEW'
WHEN TYPE = 8 THEN 'TABLE'
WHEN TYPE = 16 THEN 'PROCEDURE'
WHEN TYPE =128 THEN 'RULE'
ELSE ''
END,
ONAME varchar(255),
OOWNER varchar(255),
SEQ int
)
--our results table
CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
--our list of objects in dependancy order
INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
EXEC sp_msdependencies @intrans = 1
Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
--synonyns are object type 1 Function?!?!...gotta remove them
DELETE FROM #MyObjectHierarchy WHERE objectid in(
SELECT [object_id] FROM sys.synonyms UNION ALL
SELECT [object_id] FROM master.sys.synonyms)
DECLARE
@schemaname varchar(255),
@objname varchar(255),
@objecttype varchar(20),
@FullObjectName varchar(510)
DECLARE cur1 CURSOR FOR
SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
OPEN cur1
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
WHILE @@fetch_status <> -1
BEGIN
SET @FullObjectName = @schemaname + '.' + @objname
IF @objecttype = 'TABLE'
BEGIN
INSERT INTO #Results(ResultsText)
EXEC sp_getddl @FullObjectName
-- IF @WithData > 0
-- INSERT INTO #Results(ResultsText)
-- EXEC sp_export_data @table_name = @FullObjectName,@ommit_images = 1
-- END
ELSE IF @objecttype IN('VIEW','FUNCTION','PROCEDURE')--it's a FUNCTION/PROC/VIEW
BEGIN
--CREATE PROC/FUN/VIEW object needs a GO statement
INSERT INTO #Results(ResultsText)
SELECT 'GO'
INSERT INTO #Results(ResultsText)
EXEC sp_helptext @FullObjectName
END
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
END
CLOSE cur1
DEALLOCATE cur1
SELECT ResultsText FROM #Results ORDER BY ResultsID
END
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Shouldn't you be trying to extract the code from your source control system rather than the database?
Hope this helps,
Rich[p]
[/p] -
I would personally look into the new DDL triggers available in 2005. You should be able to create triggers on create/alter/update for relevant objects to script out whenever they're modified. That way you have true change tracking and not just a million copies of every object.
-
THANKS diamondgm
Your solution works the best.
Problem is guys we are getting source safe next year so for now i need an easy and uncomplicated solution.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
Glad I could help
Gief job in ct plz :hehe:
-
roelofsleroux (10/30/2009)
THANKS diamondgmYour solution works the best.
Problem is guys we are getting source safe next year so for now i need an easy and uncomplicated solution.
A couple things. Were you put off by the amount of work mine sounded like or do you just not believe it would be as useful? Maybe it wouldn't even work like I was suggesting, I haven't used DDL triggers yet. I don't care one way or another, I'm just curious. Also, I would advise you look more into the problems Source Safe has with databases and see if you want to go ahead and begin working on a more permanent solution. Source Safe is great for app code (although many would argue that other SC systems are far superior, that's an argument for another time), but it's pretty crappy for databases... at least by itself. I just tried to implement it here in our database and after only a couple hours of working with it, I realized how very limited it was in its current implementation.
Beyond how clunky and ill suited it is, there's nothing that really ties the code on the database to what you have in source control. It's an extra step that you do not *have* to take. To me, that means that you can never really "trust" what you have in source control, especially if you have multiple people modifying objects. There are many shortcomings with the current SQL implementation and I'll let you read about them for yourself, I just wanted to mention that it's not necessarily a total solution, you should look at sql compare from redgate to go along with it, and Redgate will be having their own source code control system come out next year, so you may want to look at that as well.
-
Garadin ... the easiest way was using INFORMATION_SCHEMA.ROUTINES.
I have also implemented it in such a way that every hour a job runs a procedure that will compare a previous version of each procedure/function from a table with the current version from INFORMATION_SCHEMA.ROUTINES.
If a procedure/function is new it will be added to the table.
If a procedure/function has changed it will be added to the table.
This means I only see changed and additions.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply