The purpose of sp_GetDDL is to get the full DDL of any table in TSQL; a function version of the same code also exists.
since the results are in a single varchar(max), it makes it easy to use in the auditing of DDL triggers, or capturing an occasional schema snapshot for disaster recovery scnarios.
Behind the scenes it's smart, readable but somewhat complicated code. usage upfront is easy:
exec sp_GetDDL YourTableName
exec sp_GetDDL 'schemaname.tablename'
exec sp_GetDDL [schemaname].[tablename]
it produces well formatted CREATE TABLE scripts like this: note that a lot of testing went into the spacing to make everything have a sharp appearance;
The proc has been enhanced to fix minor issues, and recently added any extended properties for the table ror it's columns.
CREATE TABLE [dbo].[TBSTATE] (
[STATETBLKEY] INT NOT NULL,
[INDEXTBLKEY] INT NOT NULL,
[STATECODE] CHAR(2) NOT NULL,
[STATENAME] VARCHAR(50) NOT NULL,
[FIPS] CHAR(3) NULL,
CONSTRAINT [PK__TBSTATE__17A421EC] PRIMARY KEY CLUSTERED (STATETBLKEY),
CONSTRAINT [STATECODEUNIQUE] UNIQUE NONCLUSTERED (STATECODE))