Menu
Question: How can I create a SQL table from another table without copying any values from the old table? Answer: To do this, the SQL CREATE TABLE syntax is: CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2);
Right click on the DB_NAME -> Select Task -> Select Generate Script. Follow along the presented wizard and select all tables in that database to generate the scripts.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With