Table DDL

Question

Does anybody know how to get table DDL using TSQL or .Net?

I'm writing an application that will backup a huge log table - by renaming the current table and re-creating the table so i need something like sp_helptext or select * from syscomments.

I've tried searching the net but so far drawn a blank....

Thanks
Dale

Answer 1

By the way this is on SQL Server 2005 and maybe even SQL Server 2000.

Thanks

Answer 2

The SMO managed API has scripting functionality.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 3

Great thanks, i'll give it a try.

Answer 4

I'm writing an application that will backup a huge log table - by renaming the current table and re-creating the table so i need something like sp_helptext or select * from syscomments.

Dale,

Sounds like a good fit for an SSIS package. This is what SSIS packages do. And they really love it! Good error control too!

However, if you don't want to walk down SSIS Blvd., in addition to Tibor's, suggestion there is another option to get database metadata: INFORMATION_SCHEMA views.  The following link illustrates metadata usage: http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 5

Thanks SQL USA - I did look at SSIS but the problem i have is that i need to create new tables based on date names that are in the lo tables etc so it got a bit tricky - that's when i broke out Visual Studio :)