2

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).

Matt
  • 41,216
  • 30
  • 109
  • 147

4 Answers4

1

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
1

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).

Community
  • 1
  • 1
Matt
  • 41,216
  • 30
  • 109
  • 147
1

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Fixed the hyperlink now that I have enough points :-). Check out this proc, it really will generate the table script for you – RBarryYoung Jun 01 '09 at 17:31
0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459