How to copy a table schema and constraints to a table of different database?

Question

Hi all,

I need an sql which will copy schema of a specified table to a table in different db.

How to implement this?

Please help.

Answer 1

im not well versed wid sql but den i can give u snippets which might come in handy for you.

exec sp_columns MyTable

// This would give you all columns of MyTable with some constraints like nullable , length, default values and all

declare @TableName varchar(128)
select @TableName = 'mytbl'

select  c.COLUMN_NAME 
from  INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where  pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

//This can give you the primary keys

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

//This would give you foreign key constraints..

Hope this will help you out.. cheers!!

Answer 2

SELECT INTO will create a new table with the same schema. So you could:

 SELECT *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

To just copy the schema and not the data:

 SELECT TOP 0 *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

This would not copy indexes or keys. To copy those, right click the table in SQL Sever Management Studio and choose 'Script table as'. That will give you a script you can run on the new database.

Answer 3

Try with SQL SERVER SCRIPT WIZARD

1) Select a database

2) Right Click - > Tasks - > Generate scripts

3) Click Next - > and in the Select Database Screen, choose the table(will be selected by default). Check the "Script All Objects In The Selected Database" .

4) Click Finish.

Hope this helps