How to copy the table's structure of dbo schema to other schema

Question

How to copy the table's structure of dbo schema to other schema

apjchandresh

SSC Eights!

Points: 903
More actions
May 15, 2010 at 3:10 am

#146944

How to copy the table's structure of dbo schema to other schema
I am using the store procedure for creating the schema and transfer the table for dbo to new schema after transafering the tables dbo has no table ,I need tables in both dbo and new schema.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

the best way is to script the table out;

you can use the INTO [newtable] with a SELECT, like the script below for something fast and easy, but you'll miss a lot of the structure; that will not copy defaults, check constraints, etc i think:

create schema bob

go

select *

into bob.test

from dbo.test

where 1 = 2 -- never true so no data

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

apjchandresh SSC Eights! Points: 903 More actions · Answer 2

Yes ,This miss many tables structures like relationship and othe contriants ,Can you tell any other way..........

Lowell SSC Guru Points: 323507 More actions · Answer 3

why can't you script the table out? and change dbo to the new schema name?

explain a bit more why you need to clone the table(and the data?) more than one time with a stored proc...

why can't the procedure just have the CREATE TABLE statement and change the script with dynamic SQL?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

apjchandresh SSC Eights! Points: 903 More actions · Answer 4

Yes ,But when running the database script on stroed procedure the 'Go'Statement can create problem,and Without Go statement the Relation ship and other contriants are not created...

Lowell SSC Guru Points: 323507 More actions · Answer 5

apjchandresh (5/15/2010)
Yes ,But when running the database script on stroed procedure the 'Go'Statement can create problem,and Without Go statement the Relation ship and other contriants are not created...

dynamic sql....

declare @sql varchar(2000)

set @sql = 'CREATE SCHEMA newschema'

PRINT @sql

EXEC(@sql)

set @sql = 'CREATE TABLE newschema.WhateverTable ....'

PRINT @sql

EXEC(@sql)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!