You can script a table relatively easy using the UI of course:
This will output a
CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).
But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire
CREATE TABLE DDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventual
CREATE TABLE script (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:
However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).
In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:
SELECT name, system_type_name, is_nullable FROM
sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)
name system_type_name is_nullable
-------------------- ---------------- -----------
name nvarchar(128) 0
object_id int 0
principal_id int 1
schema_id int 0
parent_object_id int 0
type char(2) 0
type_desc nvarchar(60) 1
create_date datetime 0
modify_date datetime 0
is_ms_shipped bit 0
is_published bit 0
is_schema_published bit 0
I've blogged about this, too.
Arguably this is much closer to your targeted
CREATE TABLE statement than a convoluted approach using
sys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.
That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.