CODEWITHSUNDEEP
sql-serversql-server-2005schema
Matt
Matt

Reputation: 41842

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

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

Upvotes: 2

Views: 2002

Answers (4)

RBarryYoung
RBarryYoung

Reputation: 56785

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.

Upvotes: 1

Matt
Matt

Reputation: 41842

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

Upvotes: 1

gbn
gbn

Reputation: 432662

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.

Upvotes: 1

marc_s
marc_s

Reputation: 755451

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

Upvotes: 0

Related Questions