| Author |
Topic |
|
KlausJensen
Starting Member
14 Posts |
 Posted - 2004-07-16 : 05:22:06
|
| Hi!I would like to be able to get the output of a "Generate SQL Script"-action in code (T-SQL or via COM-objects - or whatever!).Perhaps it is eaiser to explain in code, what I want. :D[pseudo .Net code]Dim scripter as SqlMagic.ScripterDim tSql as stringtSql = scripter.GetScript("stp_MyProc")...and tSql would contain the complete create-script for stp_MyProcIs that possible? I tried monitoring a "generate script"-manual action from enterprise manager (using Profiler), but that seemed like a lot of workj (using a lot of system stored procs).I thought maybe there was an easier way. :)Thanks in advanceKlaus Jensen---Klaus JensenDeveloper |
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2004-07-16 : 06:11:39
|
| Quick note: I do know sp_HelpText for stored procedures and triggers, but it does not work for tables.---Klaus JensenDeveloper |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-16 : 06:23:48
|
| http://www.nigelrivett.net/DMOScripting.htmlKristen |
|
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2004-07-16 : 08:08:03
|
| Thanks for your link, Kristen!I was hoping for a less t-sql heavy approach to the subject...---Klaus JensenDeveloper |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-16 : 09:04:19
|
| OK, what's the actual problem you want to solve?Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-07-18 : 12:37:04
|
| SQLDMO is the right choice for this problem.Search www.codeproject.com which has a few examples where SQLDMO is used in .NET for various purposes.One for Script generating with SourceSafe integration for example.Unfortunately I don't have a more concrete example for You, but maybe this info will help./rockmoose |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-18 : 13:20:08
|
| Why don't you do what you want to do in Enterprise Manager with Profiler running. :) You can then easily capture the statements it's using to do your scripting for you and just modify them. Plus, it's a great way to learn what's kicking in there.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-07-18 : 15:00:02
|
| I, just had to do it...select c.name, null, r.name, user_name(r.uid), d.name, user_name(d.uid), null from dbo.sysobjects d right outer join ( dbo.sysobjects r right outer join dbo.syscolumns c on c.domain = r.id ) on c.cdefault = d.id and d.category & 0x0800 = 0 where c.id = object_id(N'[dbo].[BM_OBJECTTYPES]') and COLUMNPROPERTY(c.id, c.name, N'isIdentity') <> 1 order by c.name This one looked interesting for a couple of seconds: master.dbo.sp_MSSQLDMO80_versionIn case someone wondered.../rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-18 : 17:00:40
|
"master.dbo.sp_MSSQLDMO80_version"Do you think Derrick didn't know EM was going to do that?  Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-07-18 : 20:19:36
|
Do you think Derrick didn't know EM was going to do that?No I don't think Derreck wondered what was happening.. just wanted to double check what was really happening  Thought that Derreck's suggestion was a bit so so...So just to make sure there were no secrets lurking around EM and QA, I double checked their scripting code...No magic MS procs there no siree.The only place where you get a free scriptng programming interface is SQLDMO  ./rockmoose |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-18 : 20:59:07
|
| Thought that Derreck's suggestion was a bit so so...:) Well, at least you're honest. lol You might as well use DMO because it is just that.....FREE. My thought process was that it would help you see exactly how it works and how you should use it if you see it running.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 04:39:08
|
| Not forgetting that rockmoose didn't ask the original question, eh Derrick?!Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-07-19 : 05:07:31
|
Klaus J - I want to automate scripting, here is some .NET PseudoCodeDerreck - Start Profiler *lol* Kirsten - 2 posts in the process  You guys are really funny /rockmoose |
|
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2004-07-19 : 07:01:39
|
| Thanks a lot for your replies! :) I used DMO to script tables, stored procedures etc - and it works brilliantly - and it's so easy! :)The goal was to be able to track changes to database objects and access the previous versions.I accomplish that by scripting the database objects, and check the code into Visual Sourcesafe if there is a change from earlier. That way we suddenly have automated version history of tables, stored procedures, user defined functions etc. I am not done yet, but have all the tricky stuff worked out. :)---Klaus JensenDeveloper |
|
|
|
|
Generate script objects from code