Get DDL for any SQL 2005 table

--##############################################################################
-- Check If TableName is Valid
--##############################################################################
IF ISNULL(@TABLE_ID,0) = 0
BEGIN
SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,
@TBLNAME = PARSENAME(@TBL,1)
SELECT
@TABLE_ID = [object_id]
FROM sys.objects
--WHERE [type] = 'U'
WHERE [type] in ('V','P','FN' )
AND [name] <> 'dtproperties'
AND [name] = @TBLNAME
AND [schema_id] = schema_id(@SCHEMANAME) ;
IF ISNULL(@TABLE_ID,0) = 0
BEGIN
SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database [' + db_name() + ']'
SELECT @FINALSQL
END
ELSE
BEGIN
SET @FINALSQL = 'SELECT S.TEXT FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON S.ID = O.OBJECT_ID WHERE O.NAME = ''' + @TBLNAME + ''''
EXEC (@FINALSQL)
--SELECT @FINALSQL
END
RETURN 0
END

SET @FINALSQL = 'SELECT S.TEXT FROM SYSCOMMENTS....
--becomes
SET @FINALSQL = SELECT REPLACE(REPLACE(S.TEXT,CHAR(13),CHAR(13) + CHAR(10)), CHAR(13) + CHAR(10) + CHAR(10),CHAR(13) + CHAR(10) ) FROM SYSCOMMENTS S [more]

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