i went to the trouble of creating a stored proc that does this;
as previous posters already identified, the actual statement is never saved...all you can do is script out the info as a CREATE TABLE statement, which may be formatted differently than the original statement, but is functionally the same:
my proc is sp_getDDL, and it's usage is pretty simple:
-- USAGE: exec sp_GetDDL YourTableName
-- or exec sp_GetDDL 'bob.example'
-- or exec sp_GetDDL '[schemaname].[tablename]'
so if you create a table like this:
create table dbo.tallycalendar ( thedate datetime not null primary key, dayofweek varchar(50) null, isholiday bit null default(0), isworkholiday bit null default(0), isdaylightsavings bit null default (0), holidayname varchar(100) null, )
go
create index ix_tallystuff on tallycalendar (thedate, dayofweek, holidayname)
it produces well formatted results like this:
CREATE TABLE [dbo].[TALLYCALENDAR] (
[THEDATE] DATETIME NOT NULL,
[DAYOFWEEK] VARCHAR(50) NULL,
[ISHOLIDAY] BIT NULL
CONSTRAINT [DF__TallyCale__IsHol__023D5A04] DEFAULT ((0)),
[ISWORKHOLIDAY] BIT NULL
CONSTRAINT [DF__TallyCale__IsWor__03317E3D] DEFAULT ((0)),
[ISDAYLIGHTSAVINGS] BIT NULL
CONSTRAINT [DF__TallyCale__IsDay__0425A276] DEFAULT ((0)),
[HOLIDAYNAME] VARCHAR(100) NULL,
CONSTRAINT [PK__TallyCal__5CB7C64E00551192] PRIMARY KEY CLUSTERED (TheDate))
GO
CREATE INDEX [IX_TallyStuff] ON [TallyCalendar] (TheDate, DayOfWeek, HolidayName)
you can download the code here:
Get DDL for any SQL 2005/2008 table
here's the article i contributed discussion with a lot of improvements:
http://www.sqlservercentral.com/Forums/Topic751783-566-1.aspx
Lowell