-- GENERATES CREATE TABLE DDL for @mytable
-- does not generate defaults or any constraints
declare @mytable sysname
set @mytable = '....'

declare @obj_id int, @colid int, @md varchar(8000), @n sysname, @t nvarchar(4000)
select @obj_id = object_id(@mytable), @colid = 1

exec sp_MSget_qualified_name @obj_id, @md output

set @md = 'CREATE TABLE ' + @md + '('
while @colid is not null
begin
	exec sp_MSget_type @obj_id, @colid, @n output, @t output
	set @md = @md + ' ' + @n + ' ' + @t + ' '
	select @md = @md + case isnullable when 0 then 'not null' else 'null' end from syscolumns where id = @obj_id and colid = @colid
	set @colid = ( select min(colid) from syscolumns where id = @obj_id and colid > @colid )
	set @md = @md + case when @colid is null then ')' else ',' end
end

select coalesce( @md, 'table ' + @mytable + ' does not exist' )