SQL Command for generating schema text (similar to CreateTo or AlterTo)

Question

SQL Server 2005. Is there a sql query that will return a text field containing the same type of schema info as you would find in doing a right click table -> Script Table As -> Create To (or Alter To) from SQL Server Management Studio ?

I'm looking for a single/flat format that describes the entire table, including constraints, indices, etc.

I am aware of:

sp_help table_name

but that doesn't provide the single flat format I'm looking for. Ideally it would be in a scriptable format, such as the AlterTo result that could be executed against the server.

This is for a scheduled process that documents table schemas on a nightly basis for checking in to version control (SVN).

Answer 1

Not really - you can either use C# (or VB.NET) and SMO (SQL Management Objects) to script out your database objects (tables and all), or you can use SQL to get the list of columns for a table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table Name here'

But I don't know of any easy way in SQL itself to create Create/Alter scripts for database objects, sorry.

Marc

Answer 2

Not really. A table def is a collection of columns, constraints etc.

There is an SVN plugin that may help called ScriptDB4SVN. I've not used it personally, I'm going on hearsay.

Answer 3

Was searching the 'net again for an answer to this, and came across this SO question. It doesn't accurately capture all the same data as SQL Management Studios Create-to, but enough for my purposes (scripting the database structure for version control purposes).

Answer 4

There is no such command in SQL Server. This is primarily because the Scripting facilitiy is actually in SMO and not in SQL Server itself. There are a number of free console command-line tools that can do it that you could call via xp_CmdShell.

However, if you really want to do this from T-SQL, then you will need a script or stored procedure that enumerates all of the tables attributes, columns, column datatypes, defaults, nullabilty, etc. etc. and then reassembles it into a CREATE TABLE script. This is a Huge task. That's the bad news. The good news is that someone (Lowell Izaguirre) has already done this and posted it in this article (http://www.sqlservercentral.com/scripts/Miscellaneous/30730/) at SQLServerCentral.Com.

Enjoy.