SQL Server parallels to Oracle DBMS_METADATA.GET_DDL ?

Question

I'm looking for command line or scripted solutions to pull the DDL out of SQL Server 2005+ for all database objects: tables, stored procs, views, indices/indexes, constraints, etc. GUI tools are not of interest.

Preference is for built-in tools, since that would be the most comparable to Oracle's DBMS_METADATA stuff. Also, preference for a solution that is as simple as Oracle's for getting the DDL out - eg, a one liner:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE')

Note: Getting things out for procedures in SQL Server 2005 seems easy, but I can't find any references to something similar for other objects (like tables).

SELECT definition 
FROM Sys.sql_modules 
WHERE object_id = OBJECT_ID('MyProc')

Thanks in advance!

Answer 1

There is no support in the Transact-SQL language. The client libraries (SMO) can do it using a Scripter object, see example at http://msdn.microsoft.com/en-us/library/ms162153.aspx. You can use SMO from PowerShell as a scripted solution.

The SQL Management Studio also has an option (right click on a database, go to Tasks, select Generate Scripts), it uses an SMO Scripter under the covers.

Answer 2

I wrote SMOscript, a command-line tool which uses SMO to generate DDL files of all database objects.