To get a Table Create script

To get a create(script)structure of the table. Use the following stored procedures.

CREATE PROC sp_table_schema
(
  @objname         varchar(255)
 )
 AS
 set nocount on
 DECLARE         @dbname         SYSNAME ,
                 @owner                        SYSNAME ,
                 @max_colid      INT       ,
                 @cnt                INT       
  
CREATE TABLE #CommentText
(
               LineId    INT       ,
               Text      NVARCHAR(255))
 
/***  Make sure the @objname is local to the current database.*/
 select @dbname = parsename(@objname,3)
 if @dbname is not null and @dbname <> db_name()
 begin
             raiserror(15250,-1,-1)
             return (1)
 end
 /*  See if @objname exists.*/
 
if (object_id(@objname) is null)
 begin
                select @dbname = db_name()
                raiserror(15009,-1,-1,@objname,@dbname)
                return (1)
   end
 if (select count(*) from sysobjects where id = object_id(@objname)
       and type = 'U') = 0
        begin
             select 'Object is not a Table'
             return (0)
        end
 select @owner = user_name(uid) from sysobjects 
 where name = @objname
 setuser @owner
 select @max_colid = max(colid) from syscolumns c (nolock) , sysobjects o (nolock)
 where c.id = o.id
 and o.name = @objname
 insert into #CommentText
 --select 0 , 'DROP TABLE '+ltrim(rtrim(@objname))
 SELECT 0,'IF EXISTS ( SELECT ' + '''X''' + ' FROM SYSOBJECTS WHERE NAME = ' + ''''+ltrim(rtrim(@objname)) +''''+
                     ' AND TYPE = '+'''U'''+ ' )'+ char(10)+
           'DROP TABLE '+ ltrim(rtrim(@objname)) 
 insert into #CommentText
 select 0 , 'GO'
 insert into #CommentText
 select 0 , 'CREATE TABLE '+ltrim(rtrim(@objname))
 insert into #CommentText
 select 0 , '('
 insert into #CommentText
 select  c.colid , convert(char(40),c.name)+'           ' + convert(char(32),LTRIM(RTRIM( UPPER(t.name) + 
 case     when t.xusertype in ( 165 , 167 , 173 , 175 , 231 , 239 ) then '('+ convert(varchar(10),c.length) + ')'
          when t.xusertype in ( 59 , 62 , 106 , 108 ) then '(' + convert(varchar(5),c.prec) +' , '+ convert(varchar(5),c.scale) + ')'
          else ''
 end)))
 +'   '+
 case c.isnullable            when 1 then 'NULL'
                               else 'NOT NULL'
 end
 +
 case c.cdefault 
             when 0 then ''
             else ' DEFAULT 0'
 end
 +
 case when colid = @max_colid then ''
 else ','
 end
 from syscolumns c (nolock) , sysobjects o (nolock) , systypes t (nolock)
 where c.id = o.id
 and o.name = @objname
 and t.xusertype = c.xusertype
 order by colid
 
 insert into #CommentText
 select @max_colid+1 , ')'
 insert into #CommentText
 select @max_colid+2 , 'GO'
 insert into #CommentText
 SELECT @max_colid+3, 'IF EXISTS ( SELECT ' + '''X''' + ' FROM SYSOBJECTS WHERE NAME = ' + ''''+ltrim(rtrim(@objname)) +''''+
             ' AND TYPE = '+'''U'''+ ' )'+ char(10)+
             'GRANT ALL ON '+ ltrim(rtrim(@objname)) +' TO PUBLIC'
 insert into #CommentText
 select @max_colid+4 , 'GO'
 select text from #CommentText
 order by lineid
     #CommentText
 setuser
      set nocount off
 return (0)