dayum that made for a really nice enhancement, Vincent.
here's links to two updated versions of the script.
sp_GetDDL V 309 returns the definition as a single varchar(max) of any of the main objects: Table, TempTable, View, Procedure, Function or Trigger.
sp_GetDDLa V 309a returns the definition as one or more rows of data, the varchar(max) split on vbCrLf of any of the main objects: Table,TempTable, View, Procedure, Function or Trigger.
here's a recap of some of the improvements made so far:
-- USAGE: exec sp_GetDDLa [TableName] | [#TempTableName] |[ProcedureName] | [ViewName] [FunctionName] |[TriggerName]
-- or exec sp_GetDDLa YourObjectName
-- or exec sp_GetDDLa 'bob.example'
-- or exec sp_GetDDLa '[schemaname].[ObjectName]'
-- V300 uses String concatination and sys.tables instead of a cursor
-- V301 enhanced 07/31/2009 to include extended properties definitions
-- V302 fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found
-- V303 fixes an issue where all rules are appearing, instead of jsut the rule related to a column
-- V304 testing whether vbCrLf is better than just CHAR(13), some formatting cleanup with GO statements
-- also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]
-- V305 user feedback helped me find that the type_name function should call user_type_id instead of system_type_id
-- also fixed issue where identity definition missing from numeric/decimal definition
-- V306 fixes the computed columns definition that got broken/removed somehow in V300
-- also formatting when decimal is not an identity
-- V307 fixes bug identified by David Griffiths-491597 from SSC where the @TABLE_ID
-- is reselected, but without it's schema , potentially selecting the wrong table
-- also fixed is the missing size definition for varbinary, also found by David Griffith
-- V308 abtracted all SQLs to use Table Alaises
-- added logic to script a temp table.
-- added warning about possibly not being marked as system object.
-- V309 added logic based on feedback from Vincent Wylenzek @SSC to return the definition from sys.sql_modules for
-- any object like procedure/view/function/trigger, and not just a table.
-- note previously, if you pointed sp_GetDDL at a view, it returned the view as a table...
-- now it will return the view definition instead.
-- V309 returns single varchar(max)
-- V309a returns multi row recordset, one line per record of the varchar(max) split on vbCrLf
Lowell