In SQL Server, how do I generate a CREATE TABLE statement for a given table?

I've spent a good amount of time coming up with solution to this problem, so in the spirit of this post, I'm posting it here, since I think it might be useful to others.

If anyone has a better script, or anything to add, please post it.

Edit: Yes guys, I know how to do it in Management Studio - but I needed to be able to do it from within another application.

I've modified the version above to run for all tables and support new SQL 2005 data types. It also retains the primary key names. Works only on SQL 2005 (using cross apply).

select	'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from	sysobjects so
cross apply
(SELECT
'  ['+column_name+'] ' +
data_type + case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'ntext' then ''
when 'xml' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end + ' ' +
(case when UPPER(IS_NULLABLE) = 'NO' then 'NOT ' else '' end ) + 'NULL ' +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '



 from information_schema.columns where table_name = so.name
 order by ordinal_position
FOR XML PATH('')) o (list)




left join
information_schema.table_constraints tc
on	tc.Table_name		= so.Name
AND	tc.Constraint_Type	= 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM	information_schema.key_column_usage kcu
WHERE	kcu.Constraint_Name	= tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
where	xtype = 'U'
AND	name	NOT IN ('dtproperties')

Update: Added handling of the XML data type

Update 2: Fixed cases when 1) there is multiple tables with the same name but with different schemas, 2) there is multiple tables having PK constraint with the same name

Here's the script that I came up with. It handles Identity columns, default values, and primary keys. It does not handle foreign keys, indexes, triggers, or any other clever stuff. It works on SQLServer 2000, 2005 and 2008.

declare @schema varchar(100), @table varchar(100)
set @schema = 'dbo' -- set schema name here
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)

-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')

-- column list
insert into @sql(s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','

 from INFORMATION_SCHEMA.COLUMNS where table_name = @table AND table_schema = @schema
 order by ordinal_position

-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin
	insert into @sql(s) values('  PRIMARY KEY (')
	insert into @sql(s)
		select '   ['+COLUMN_NAME+'],' from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
		where constraint_name = @pkname
		order by ordinal_position
	-- remove trailing comma
	update @sql set s=left(s,len(s)-1) where id=@@identity
	insert into @sql(s) values ('  )')
end
else begin
	-- remove trailing comma
	update @sql set s=left(s,len(s)-1) where id=@@identity
end

-- closing bracket
insert into @sql(s) values( ')' )

-- result!
select s from @sql order by id

There is a Powershell script buried in the msdb forums that will script all the tables and related objects:

# Script all tables in a database
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
    | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') '<Servername>'
$db = $s.Databases['<Database>']

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.FileName = 'C:\Temp\<Database>.SQL'

foreach($item in $db.Tables) { $tablearray+=@($item) }
$scrp.Script($tablearray)

Write-Host "Scripting complete"

Support for schemas:

This is an updated version that amends the great answer from David, et al. Added is support for named schemas. It should be noted this may break if there's actually tables of the same name present within various schemas. Another improvement is the use of the official QuoteName() function.

SELECT 
	t.TABLE_CATALOG,
	t.TABLE_SCHEMA,
	t.TABLE_NAME,
	'create table '+QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name) + ' (' + LEFT(o.List, Len(o.List)-1) + ');  ' 
		+ CASE WHEN tc.Constraint_Name IS NULL THEN '' 
		  ELSE 
			'ALTER TABLE ' + QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name) 
			+ ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ');  ' 
		  END as 'SQL_CREATE_TABLE'
FROM sysobjects so

CROSS APPLY (
	SELECT 
		  '  ['+column_name+'] ' 
		  +  data_type 
		  + case data_type
				when 'sql_variant' then ''
				when 'text' then ''
				when 'ntext' then ''
				when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
			  else 
			  coalesce(
				'('+ case when character_maximum_length = -1 
					then 'MAX' 
					else cast(character_maximum_length as varchar) end 
				+ ')','') 
			end 
		+ ' ' 
		+ case when exists ( 
			SELECT id 
			FROM syscolumns
			WHERE 
				object_name(id) = so.name
				and name = column_name
				and columnproperty(id,name,'IsIdentity') = 1 
		  ) then
			'IDENTITY(' + 
			cast(ident_seed(so.name) as varchar) + ',' + 
			cast(ident_incr(so.name) as varchar) + ')'
		  else ''
		  end 
		+ ' ' 
		+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end) 
		+ 'NULL ' 
		+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT 
		  ELSE '' 
		  END 
		+ ','  -- can't have a field name or we'll end up with XML

	FROM information_schema.columns 
	WHERE table_name = so.name
	ORDER BY ordinal_position
	FOR XML PATH('')
) o (list)

LEFT JOIN information_schema.table_constraints tc on  
	tc.Table_name = so.Name
	AND tc.Constraint_Type  = 'PRIMARY KEY'

LEFT JOIN information_schema.tables t on  
	t.Table_name = so.Name

CROSS APPLY (
	SELECT QuoteName(Column_Name) + ', '
	FROM information_schema.key_column_usage kcu
	WHERE kcu.Constraint_Name = tc.Constraint_Name
	ORDER BY ORDINAL_POSITION
	FOR XML PATH('')
) j (list)

WHERE
	xtype = 'U'
	AND name NOT IN ('dtproperties')
	-- AND so.name = 'ASPStateTempSessions'
;

..

For use in Management Studio:

One detractor to the sql code above is if you test it using SSMS, long statements aren't easy to read. So, as per this helpful post, here's another version that's somewhat modified to be easier on the eyes after clicking the link of a cell in the grid. The results are more readily identifiable as nicely formatted CREATE TABLE statements for each table in the db.

-- settings
DECLARE @CRLF NCHAR(2)
SET @CRLF = Nchar(13) + NChar(10)
DECLARE @PLACEHOLDER NCHAR(3)
SET @PLACEHOLDER = '{:}'

-- the main query
SELECT 
	t.TABLE_CATALOG,
	t.TABLE_SCHEMA,
	t.TABLE_NAME,
	CAST(
		REPLACE(
			'create table ' + QuoteName(t.TABLE_SCHEMA) + '.' + QuoteName(so.name) + ' (' + @CRLF 
			+ LEFT(o.List, Len(o.List) - (LEN(@PLACEHOLDER)+2)) + @CRLF + ');' + @CRLF
			+ CASE WHEN tc.Constraint_Name IS NULL THEN '' 
			  ELSE
				'ALTER TABLE ' + QuoteName(t.TABLE_SCHEMA) + '.' + QuoteName(so.Name) 
				+ ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY (' + LEFT(j.List, Len(j.List) - 1) + ');' + @CRLF
			  END,
			@PLACEHOLDER,
			@CRLF
		)
	AS XML) as 'SQL_CREATE_TABLE'
FROM sysobjects so

CROSS APPLY (
	SELECT 
		  '   '
		  + '['+column_name+'] ' 
		  +  data_type 
		  + case data_type
				when 'sql_variant' then ''
				when 'text' then ''
				when 'ntext' then ''
				when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
			  else 
			  coalesce(
				'('+ case when character_maximum_length = -1 
					then 'MAX' 
					else cast(character_maximum_length as varchar) end 
				+ ')','') 
			end 
		+ ' ' 
		+ case when exists ( 
			SELECT id 
			FROM syscolumns
			WHERE 
				object_name(id) = so.name
				and name = column_name
				and columnproperty(id,name,'IsIdentity') = 1 
		  ) then
			'IDENTITY(' + 
			cast(ident_seed(so.name) as varchar) + ',' + 
			cast(ident_incr(so.name) as varchar) + ')'
		  else ''
		  end 
		+ ' ' 
		+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end) 
		+ 'NULL ' 
		+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT 
		  ELSE '' 
		  END 
		+ ', ' 
		+ @PLACEHOLDER  -- note, can't have a field name or we'll end up with XML

	FROM information_schema.columns where table_name = so.name
	ORDER BY ordinal_position
	FOR XML PATH('')
) o (list)

LEFT JOIN information_schema.table_constraints tc on  
	tc.Table_name = so.Name
	AND tc.Constraint_Type  = 'PRIMARY KEY'

LEFT JOIN information_schema.tables t on  
	t.Table_name = so.Name

CROSS APPLY (
	SELECT QUOTENAME(Column_Name) + ', '
	FROM information_schema.key_column_usage kcu
	WHERE kcu.Constraint_Name = tc.Constraint_Name
	ORDER BY ORDINAL_POSITION
	FOR XML PATH('')
) j (list)

WHERE
	xtype = 'U'
	AND name NOT IN ('dtproperties')
	-- AND so.name = 'ASPStateTempSessions'
;

Not to belabor the point, but here's the functionally equivalent example outputs for comparison:

-- 1 (scripting version)
create table [dbo].[ASPStateTempApplications] (  [AppId] int  NOT NULL ,  [AppName] char(280)  NOT NULL );  ALTER TABLE [dbo].[ASPStateTempApplications] ADD CONSTRAINT PK__ASPState__8E2CF7F908EA5793 PRIMARY KEY  ([AppId]);  

-- 2 (SSMS version)
create table [dbo].[ASPStateTempSessions] (
   [SessionId] nvarchar(88)  NOT NULL , 
   [Created] datetime  NOT NULL DEFAULT (getutcdate()), 
   [Expires] datetime  NOT NULL , 
   [LockDate] datetime  NOT NULL , 
   [LockDateLocal] datetime  NOT NULL , 
   [LockCookie] int  NOT NULL , 
   [Timeout] int  NOT NULL , 
   [Locked] bit  NOT NULL , 
   [SessionItemShort] varbinary(7000)  NULL , 
   [SessionItemLong] image(2147483647)  NULL , 
   [Flags] int  NOT NULL DEFAULT ((0))
);
ALTER TABLE [dbo].[ASPStateTempSessions] ADD CONSTRAINT PK__ASPState__C9F4929003317E3D PRIMARY KEY ([SessionId]);

..

Detracting factors:

It should be noted that I remain relatively unhappy with this due to the lack of support for indeces other than a primary key. It remains suitable for use as a mechanism for simple data export or replication.

If the application you are generating the scripts from is a .NET application, you may want to look into using SMO (Sql Management Objects). Reference this SQL Team link on how to use SMO to script objects.

One more variant with foreign keys support and in one statement:

 SELECT
        obj.name
        ,'CREATE TABLE [' + obj.name + '] (' + LEFT(cols.list, LEN(cols.list) - 1 ) + ')'
        + ISNULL(' ' + refs.list, '')
    FROM sysobjects obj
    CROSS APPLY (
        SELECT 
            CHAR(10)
            + ' [' + column_name + '] '
            + data_type
            + CASE data_type
                WHEN 'sql_variant' THEN ''
                WHEN 'text' THEN ''
                WHEN 'ntext' THEN ''
                WHEN 'xml' THEN ''
                WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')'
                ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '')
            END
            + ' '
            + case when exists ( -- Identity skip
            select id from syscolumns
            where object_name(id) = obj.name
            and name = column_name
            and columnproperty(id,name,'IsIdentity') = 1 
            ) then
            'IDENTITY(' + 
            cast(ident_seed(obj.name) as varchar) + ',' + 
            cast(ident_incr(obj.name) as varchar) + ')'
            else ''
            end + ' '
            + CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END
            + 'NULL'
            + CASE WHEN information_schema.columns.column_default IS NOT NULL THEN ' DEFAULT ' + information_schema.columns.column_default ELSE '' END
            + ','
        FROM
            INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = obj.name
        ORDER BY ordinal_position
        FOR XML PATH('')
    ) cols (list)
    CROSS APPLY(
        SELECT
            CHAR(10) + 'ALTER TABLE ' + obj.name + '_noident_temp ADD ' + LEFT(alt, LEN(alt)-1)
        FROM(
            SELECT
                CHAR(10)
                + ' CONSTRAINT ' + tc.constraint_name
                + ' ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'
                + COALESCE(CHAR(10) + r.list, ', ')
            FROM
                information_schema.table_constraints tc
                CROSS APPLY(
                    SELECT
                        '[' + kcu.column_name + '], '
                    FROM
                        information_schema.key_column_usage kcu
                    WHERE
                        kcu.constraint_name = tc.constraint_name
                    ORDER BY
                        kcu.ordinal_position
                    FOR XML PATH('')
                ) c (list)
                OUTER APPLY(
                    -- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
                    SELECT
                        '  REFERENCES [' + kcu1.constraint_schema + '].' + '[' + kcu2.table_name + ']' + '(' + kcu2.column_name + '), '
                    FROM information_schema.referential_constraints as rc
                        JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)
                        JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)
                    WHERE
                        kcu1.constraint_catalog = tc.constraint_catalog AND kcu1.constraint_schema = tc.constraint_schema AND kcu1.constraint_name = tc.constraint_name
                ) r (list)
            WHERE tc.table_name = obj.name
            FOR XML PATH('')
        ) a (alt)
    ) refs (list)
    WHERE
        xtype = 'U'
    AND name NOT IN ('dtproperties')
    AND obj.name = 'your_table_name'

You could try in is sqlfiddle: http://sqlfiddle.com/#!6/e3b66/3/0

I modified the accepted answer and now it can get the command including primary key and foreign key in a certain schema.

declare @table varchar(100)
declare @schema varchar(100)
set @table = 'Persons' -- set table name here
set @schema = 'OT' -- set SCHEMA name here
declare @sql table(s varchar(1000), id int identity)

-- create statement
insert into  @sql(s) values ('create table ' + @table + ' (')

-- column list
insert into @sql(s)
select 
    '  '+column_name+' ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','

 from information_schema.columns where table_name = @table and table_schema = @schema
 order by ordinal_position

-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin
    insert into @sql(s) values('  PRIMARY KEY (')
    insert into @sql(s)
    	select '   '+COLUMN_NAME+',' from information_schema.key_column_usage
    	where constraint_name = @pkname
    	order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
end


-- foreign key
declare @fkname varchar(100)
select @fkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='FOREIGN KEY'

if ( @fkname is not null ) begin
    insert into @sql(s) values(',')
    insert into @sql(s) values('  FOREIGN KEY (')
    insert into @sql(s)
    	select '   '+COLUMN_NAME+',' from information_schema.key_column_usage
    	where constraint_name = @fkname
    	order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  ) REFERENCES ')
    insert into @sql(s) 
		SELECT  
			OBJECT_NAME(fk.referenced_object_id)
		FROM 
			sys.foreign_keys fk
		INNER JOIN 
			sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
		INNER JOIN
			sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
		INNER JOIN
			sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
		where fk.name = @fkname
    insert into @sql(s) 
		SELECT  
			'('+c2.name+')'
		FROM 
			sys.foreign_keys fk
		INNER JOIN 
			sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
		INNER JOIN
			sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
		INNER JOIN
			sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
		where fk.name = @fkname
end

-- closing bracket
insert into @sql(s) values( ')' )

-- result!
select s from @sql order by id

I'm going to improve the answer by supporting partitioned tables:

find partition scheme and partition key using below scritps:

declare @partition_scheme varchar(100) = (
select distinct ps.Name AS PartitionScheme
from sys.indexes i  
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
where i.object_id = object_id('your table name')
)
print @partition_scheme

declare @partition_column varchar(100) = (
select c.name 
from  sys.tables          t
join  sys.indexes         i 
      on(i.object_id = t.object_id 
  and i.index_id < 2)
join  sys.index_columns  ic 
  on(ic.partition_ordinal > 0 
  and ic.index_id = i.index_id and ic.object_id = t.object_id)
join  sys.columns         c 
  on(c.object_id = ic.object_id 
  and c.column_id = ic.column_id)
where t.object_id  = object_id('your table name')
)
print @partition_column

then change the generation query by adding below line at the right place:

+ IIF(@partition_scheme is null, '', 'ON [' + @partition_scheme + ']([' + @partition_column + '])')

Credit due to @Blorgbeard for sharing his script. I'll certainly bookmark it in case I need it.

Yes, you can "right click" on the table and script the CREATE TABLE script, but:

• The a script will contain loads of cruft (interested in the extended properties anyone?)
• If you have 200+ tables in your schema, it's going to take you half a day to script the lot by hand.

With this script converted into a stored procedure, and combined with a wrapper script you would have a nice automated way to dump your table design into source control etc.

The rest of your DB code (SP's, FK indexes, Triggers etc) would be under source control anyway ;)

Something I've noticed - in the INFORMATION_SCHEMA.COLUMNS view, CHARACTER_MAXIMUM_LENGTH gives a size of 2147483647 (2^31-1) for field types such as image and text. ntext is 2^30-1 (being double-byte unicode and all).

This size is included in the output from this query, but it is invalid for these data types in a CREATE statement (they should not have a maximum size value at all). So unless the results from this are manually corrected, the CREATE script won't work given these data types.

I imagine it's possible to fix the script to account for this, but that's beyond my SQL capabilities.

-- or you could create a stored procedure ... first with Id creation

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGeneratorWithId]    Script Date: 06/13/2009 22:18:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROC [dbo].[procUtils_InsertGeneratorWithId]    
(    
@domain_user varchar(50),    
@tableName varchar(100)    
)     
    
    
as    
    
--Declare a cursor to retrieve column specific information for the specified table    
DECLARE cursCol CURSOR FAST_FORWARD FOR     
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName    
OPEN cursCol    
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement    
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement    
DECLARE @dataType nvarchar(1000) --data types returned for respective columns    
DECLARE @IDENTITY_STRING nvarchar ( 100 )    
SET @IDENTITY_STRING = ' '     
select  @IDENTITY_STRING    
SET @string='INSERT '+@tableName+'('    
SET @stringData=''    
    
DECLARE @colName nvarchar(50)    
    
FETCH NEXT FROM cursCol INTO @colName,@dataType    
    
IF @@fetch_status<>0    
 begin    
 print 'Table '+@tableName+' not found, processing skipped.'    
 close curscol    
 deallocate curscol    
 return    
END    
    
WHILE @@FETCH_STATUS=0    
BEGIN    
IF @dataType in ('varchar','char','nchar','nvarchar')    
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'    
END    
ELSE    
if @dataType in ('text','ntext') --if the datatype is text or something else     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'    
END    
ELSE    
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly    
BEGIN    
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
END    
ELSE     
IF @dataType='datetime'    
BEGIN    
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'    
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
END    
ELSE     
IF @dataType='image'     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'    
END    
ELSE --presuming the data type is int,bit,numeric,decimal     
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'    
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'    
END    
    
SET @string=@string+@colName+','    
    
FETCH NEXT FROM cursCol INTO @colName,@dataType    
END    
DECLARE @Query nvarchar(4000)    
    
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName    
exec sp_executesql @query    
--select @query    
    
CLOSE cursCol    
DEALLOCATE cursCol    
    

  /*
USAGE

*/

GO

-- and second without iD INSERTION

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGenerator]    Script Date: 06/13/2009 22:20:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[procUtils_InsertGenerator]        
(        
@domain_user varchar(50),        
@tableName varchar(100)        
)         
        
        
as        
        
--Declare a cursor to retrieve column specific information for the specified table        
DECLARE cursCol CURSOR FAST_FORWARD FOR         
      
      
-- SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName        
/* NEW     
SELECT c.name , sc.data_type  FROM sys.extended_properties AS ep                   
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id                   
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id                   
= c.column_id                   
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and                   
c.name = sc.column_name                   
WHERE t.name = @tableName and c.is_identity=0      
  */      
    
select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"      
  from sys.columns c          
  join sys.systypes s on (s.xtype = c.system_type_id)          
  where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')          
   AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName  ) and c.is_identity=0 and s.name not like 'sysname'  
    
    
      
      
OPEN cursCol        
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement        
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement        
DECLARE @dataType nvarchar(1000) --data types returned for respective columns        
DECLARE @IDENTITY_STRING nvarchar ( 100 )        
SET @IDENTITY_STRING = ' '         
select  @IDENTITY_STRING        
SET @string='INSERT '+@tableName+'('        
SET @stringData=''        
        
DECLARE @colName nvarchar(50)        
        
FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        
        
IF @@fetch_status<>0        
 begin        
 print 'Table '+@tableName+' not found, processing skipped.'        
 close curscol        
 deallocate curscol        
 return        
END        
        
WHILE @@FETCH_STATUS=0        
BEGIN        
IF @dataType in ('varchar','char','nchar','nvarchar')        
BEGIN        
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'        
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'        
END        
ELSE        
if @dataType in ('text','ntext') --if the datatype is text or something else         
BEGIN        
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'        
END        
ELSE        
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly        
BEGIN        
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'        
END        
ELSE         
IF @dataType='datetime'        
BEGIN        
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'        
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'        
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'        
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
END        
ELSE         
IF @dataType='image'         
BEGIN        
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'        
END        
ELSE --presuming the data type is int,bit,numeric,decimal         
BEGIN        
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'        
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'        
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'        
END        
        
SET @string=@string+@colName+','        
        
FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        
END        
DECLARE @Query nvarchar(4000)        
        
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName        
exec sp_executesql @query        
--select @query       
        
CLOSE cursCol        
DEALLOCATE cursCol        
      
        
  /*      
      
use poc     
go    
      
DECLARE @RC int      
DECLARE @domain_user varchar(50)      
DECLARE @tableName varchar(100)      
      
-- TODO: Set parameter values here.      
set @domain_user='yorgeorg'      
set @tableName = 'tbGui_WizardTabButtonAreas'      
      
EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]       
   @domain_user      
  ,@tableName      
      
*/
GO

Show create table in classic asp (handles constraints, primary keys, copying the table structure and/or data ...)

> Sql server Show create table > Mysql-style "Show create table" and "show create database" commands from Microsoft sql server. > The script is written is Microsoft asp-language and is quite easy to port to another language.*

I include definitions for computed columns

    select 'CREATE TABLE [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END, name
from    sysobjects so
cross apply
    (SELECT

case when comps.definition is not null then '  ['+column_name+'] AS ' + comps.definition 
else
        '  ['+column_name+'] ' + data_type + 
		case
        when data_type like '%text' or data_type in ('image', 'sql_variant' ,'xml')
            then ''
        when data_type in ('float')
            then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ')'
        when data_type in ('datetime2', 'datetimeoffset', 'time')
            then '(' + cast(coalesce(datetime_precision, 7) as varchar(11)) + ')'
        when data_type in ('decimal', 'numeric')
            then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ',' + cast(coalesce(numeric_scale, 0) as varchar(11)) + ')'
        when (data_type like '%binary' or data_type like '%char') and character_maximum_length = -1
            then '(max)'
        when character_maximum_length is not null
            then '(' + cast(character_maximum_length as varchar(11)) + ')'
        else ''
		end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when information_schema.columns.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END 
end + ', ' 
		  
     from information_schema.columns 
	 left join sys.computed_columns comps 
	 on OBJECT_ID(information_schema.columns.TABLE_NAME)=comps.object_id and information_schema.columns.COLUMN_NAME=comps.name

	 where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

I realise that it's been a very long time but thought I'd add anyway. If you just want the table, and not the create table statement you could use

select into x from db.schema.y where 1=0

to copy the table to a new DB

A query based on Hubbitus answer.

• includes schema names
• fixes foreign keys with more than one field
• includes CASCADE UPDATE & DELETE
• includes a conditioned DROP TABLE

SELECT 
  Schema_Name = SCHEMA_NAME(obj.uid)
, Table_Name = name
, Drop_Table = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = ''' + SCHEMA_NAME(obj.uid) + '''  AND  TABLE_NAME = ''' + obj.name + '''))
DROP TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] '
, Create_Table ='
CREATE TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] (' + LEFT(cols.list, LEN(cols.list) - 1 ) + ')' + ISNULL(' ' + refs.list, '')
    FROM sysobjects obj
    CROSS APPLY (
        SELECT 
            CHAR(10)
            + ' [' + column_name + '] '
            + data_type
            + CASE data_type
                WHEN 'sql_variant' THEN ''
                WHEN 'text' THEN ''
                WHEN 'ntext' THEN ''
                WHEN 'xml' THEN ''
                WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')'
                ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '')
            END
            + ' '
            + case when exists ( -- Identity skip
								select id from syscolumns
								where id = obj.id
								and name = column_name
								and columnproperty(id, name, 'IsIdentity') = 1 
								) then
			            'IDENTITY(' + 
							cast(ident_seed(obj.name) as varchar) + ',' + 
							cast(ident_incr(obj.name) as varchar) + ')'
            else ''
            end + ' '
            + CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END
            + 'NULL'
            + CASE WHEN IC.column_default IS NOT NULL THEN ' DEFAULT ' + IC.column_default ELSE '' END
            + ','
        FROM INFORMATION_SCHEMA.COLUMNS IC
        WHERE IC.table_name   = obj.name
		  AND IC.TABLE_SCHEMA = SCHEMA_NAME(obj.uid)
        ORDER BY ordinal_position
        FOR XML PATH('')
    ) cols (list)
    CROSS APPLY(
        SELECT
            CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] ADD ' + LEFT(alt, LEN(alt)-1)
        FROM(
            SELECT
                CHAR(10)
                + ' CONSTRAINT ' + tc.constraint_name
                + ' ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'
                + COALESCE(CHAR(10) + r.list, ', ')
            FROM information_schema.table_constraints tc
                CROSS APPLY(
                    SELECT   '[' + kcu.column_name + '], '
                    FROM     information_schema.key_column_usage kcu
                    WHERE    kcu.constraint_name = tc.constraint_name
                    ORDER BY kcu.ordinal_position
                    FOR XML PATH('')
                ) c (list)
                OUTER APPLY(
                    -- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
                    SELECT LEFT(f.list, LEN(f.list)-1) + ')' + IIF(rc.DELETE_RULE = 'NO ACTION', '', ' ON DELETE ' + rc.DELETE_RULE) + IIF(rc.UPDATE_RULE = 'NO ACTION', '', ' ON UPDATE ' + rc.UPDATE_RULE) + ', '
                    FROM information_schema.referential_constraints rc
					CROSS APPLY(
						SELECT IIF(kcu.ordinal_position = 1, ' REFERENCES [' + kcu.table_schema + '].[' + kcu.table_name + '] (', '') 
								+ '[' + kcu.column_name + '], '
						FROM information_schema.key_column_usage kcu 
						WHERE kcu.constraint_catalog = rc.unique_constraint_catalog AND kcu.constraint_schema = rc.unique_constraint_schema AND kcu.constraint_name = rc.unique_constraint_name
						ORDER BY kcu.ordinal_position
						FOR XML PATH('')
					) f (list)
                    WHERE rc.constraint_catalog = tc.constraint_catalog 
					  AND rc.constraint_schema  = tc.constraint_schema 
					  AND rc.constraint_name    = tc.constraint_name
                ) r (list)
            WHERE tc.table_name = obj.name
            FOR XML PATH('')
        ) a (alt)
    ) refs (list)
    WHERE xtype = 'U'

To combine drop table (if exists) with create use like this:

SELECT Drop_Table + CHAR(10) + Create_Table FROM SysCreateTables

If you are using management studio and have the query analyzer window open you can drag the table name to the query analyzer window and ... bingo! you get the table script. I've not tried this in SQL2008