September 30, 2007 at 12:16 pm
Hi All,
Is there a way to extract the script for an existing database object (table for example) programatically using T-SQL system objects or procedures? I know I can do that using SMO but I am trying to avoid SMO, as suggested by a few members and also because by the time the process to I generate scripts for every object in a database is executed, I will be old.
Under SSMS, you could right click on a table and select "Script Table as" and write out to a window or file. This is exactly what I want to do, but programatically. Any help please?
Babu.
September 30, 2007 at 12:47 pm
How quickly do you need this to run?
If SMO is too slow it implies that your database structure is very volatile so maybe you should look for a solution that includes the changes rather than scripting after the fact.
Do you want to get the scripts to recreate the objects or just to record the state?
You can get a lot of the data from the information_schema views, syscolumns, sysobjects, syscomments, etc. so you could extract and then create the scripts.
But anything will take time to execute and may be too slow for you.
Maybe something that includes schema triggers and keeps the output up to date.
Cursors never.
DTS - only when needed and never to control.
October 1, 2007 at 1:58 am
Babu Mannaravalappil (9/30/2007)
Hi All,Is there a way to extract the script for an existing database object (table for example) programatically using T-SQL system objects or procedures? I know I can do that using SMO but I am trying to avoid SMO, as suggested by a few members and also because by the time the process to I generate scripts for every object in a database is executed, I will be old.
Under SSMS, you could right click on a table and select "Script Table as" and write out to a window or file. This is exactly what I want to do, but programatically. Any help please?
Babu.
Unlike stored procedures, views, triggers ... tables are not preserved as scripts. They are persisted in a database as metadata only. I.e. you can no longer access the original script, if you had comments in it, they are also lost. All the information is in system tables, in 2005 you can access this information via system views like: sys.objects, sys.tables, sys.columns, sys.default_constraints, sys.indexes, sys.foreign_keys, sys.rules, ....
What Management Studio is doing is querying these views, and putting together a T-SQL script that would recreate the object in question. To do this in SQL is possible, but may not be worth the effort.
Regards,
Andras
October 1, 2007 at 4:25 am
Thanks all for the replies.
I already wrote an application using SMO to iterate through all the objects in a given database and populate a tree. The business logic in my application requires that all the objects be added to the tree before the form is displayed (unlike the Microsoft SMO sample, which populates items only when a tree node is expanded. This approach is fast enough since only the top most collection or exapandable items need to be added initially). I was able to do this using recursive method calls and it works. The only problem is, in my test it took almost two hours to populate this tree with Duo 2 processor and 3gb of RAM. And this does not even inclue the scripts I am talking about.
So, it appears that, if I use a mixture of INFORMATION_SCHEMA, sys objects and SMO, I may be able to reduce the time significantly, but may have to co-exist with SMOs.
Babu.
October 1, 2007 at 4:43 am
Babu Mannaravalappil (10/1/2007)
Thanks all for the replies.I already wrote an application using SMO to iterate through all the objects in a given database and populate a tree. The business logic in my application requires that all the objects be added to the tree before the form is displayed (unlike the Microsoft SMO sample, which populates items only when a tree node is expanded. This approach is fast enough since only the top most collection or exapandable items need to be added initially). I was able to do this using recursive method calls and it works. The only problem is, in my test it took almost two hours to populate this tree with Duo 2 processor and 3gb of RAM. And this does not even inclue the scripts I am talking about.
So, it appears that, if I use a mixture of INFORMATION_SCHEMA, sys objects and SMO, I may be able to reduce the time significantly, but may have to co-exist with SMOs.
Babu.
Hi Babu,
everything in INFORMATION_SCHEMA is available in the sys system views. mixing the two might not be nicest style. The disadvantage of using INFORMATION_SCHEMA is that it does not contain all the information about the schema you may be interested in. The advantage of INFORMATION_SCHEMA is that it is part of the SQL standard. If you want to make your application portable this is an important consideration.
Andras
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply