June 4, 2012 at 1:54 pm
Subject should have been: "Generate DDL of all objects to file"
Hi,
I am trying to find a way to automate the creation of a file containing the DDL of all database objects. I need to extract the DDLs on a daily basis.
As far as I can see, there is no way to automate the 'Generate and Publish scripts" task.
Does someone has an idea of how to achieve this?
Thanks for your help !
Rem
June 4, 2012 at 2:10 pm
There is not anyway to automate that type of script generation. This stuff is not easy to put together. How do you handle things like foreign keys? They would have to be scripted in the correct order. What about nested procs, views?
Pinal Dave has an article discussing the SQL Publishing Wizard here. http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/[/url]
This would generate the scripts but can't guarantee it would run due to the ordering of objects like I mentioned above.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 2:12 pm
There is no direct way, you need to create first version manually.
Then have DDL trigger setup and capture all DDL events in to a table.
Then you can append file on daily basis from this table.
June 4, 2012 at 2:33 pm
Also look at this other forum post by genehunter on scripting everything out via powershell:
this was one of my first powershells cripts when i was playing wiht powershell; with some minor tweaks, it works great for me.
http://www.sqlservercentral.com/Forums/FindPost1240480.aspx
Lowell
June 4, 2012 at 2:49 pm
Hi,
Thanks for your reply.
Basically what I want it a copy of the DDL in case someone mess with some objects. We don't have a source control and I just want to be able to recover some DDL if needed.
Thank,
- R
June 4, 2012 at 2:59 pm
Rem70Rem (6/4/2012)
Hi,Thanks for your reply.
Basically what I want it a copy of the DDL in case someone mess with some objects. We don't have a source control and I just want to be able to recover some DDL if needed.
Thank,
- R
Implementing source control would not only be faster but also it would be a more robust solution. No dev shop these days runs without source control and few can imagine working without it. When it is so easy to implement with your sql instance why not just get it in place?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 3:11 pm
We are using TortoiseSVN here for source control. But I can't find any plugin to connect this source control to SQL2005/SQL2008/SQL2012.
If someone has succeed, please let me know how to use TortoireSVN with MS_SQL.
- R
June 5, 2012 at 7:20 am
Not a free solution but the one from redgate sounds pretty solid.
http://www.red-gate.com/products/sql-development/sql-source-control/[/url]
Also I googled "sql server tortoisesvn" and got thousands and thousands of hits. I am sure if you dig around a bit you can find what you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2012 at 6:25 am
You could probably call sqlpubwiz with xp_cmdshell (in a stored proc) and run it as a scheduled job..
for more info check this site out...
- Nandu
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply