CREATE TABLE? - Webhost4life Forum

Welcome to Webhost4life Forum Sign in | Join | Faq

MSSQL

Started by DevoDog at 03-26-2007 11:02 AM. Topic has 2 replies.

Search

Sort Posts:

03-26-2007, 11:02 AM

DevoDog is not online. Last active: 1/28/2008 10:20:16 PM DevoDog

Top 100 Posts
Joined on 03-27-2007
Posts 3

CREATE TABLE?

I imported an existing SQL database which I have working.

However, I now have to reference all of my tables and views as dboXXX.tblName.

I'm trying to see if I can run a CREATE TABLE command to copy the tables and see what it does as the table owner.

The SQL Command is:

CREATE TABLE Characters2 SELECT * FROM dboXXX.tblCharacters

Query Analyzer keeps reporting an error near the SELECT.

Does anyone have any insight on how I can easily copy a table and its data?

   Report

04-11-2007, 8:10 AM

Jordan is not online. Last active: 9/4/2008 9:31:21 PM Jordan

Top 10 Posts
Joined on 09-20-2005
Chicago
Posts 16

Re: CREATE TABLE?

Rather than trying to copy the tables, why not change the schema name to what you need? You can change the schema using the T-SQL command "alter schema"

See http://msdn2.microsoft.com/en-us/library/ms173423.aspx for usage and example.

   Report

05-06-2007, 7:21 AM

Alistair is not online. Last active: 8/5/2008 6:31:21 PM Alistair

Top 100 Posts
Joined on 05-06-2007
India
Posts 3

Re: CREATE TABLE?

Hi DevoDog,

   I was having the same problem so i did something like this.

   I ran an inbuilt stored procedure called "sp_changeobjectowner" in the query analyser.

   This stored proc has two parameters the first being the object name according to your e.g. dboXXX.tblName and second the owner name u would prefer to be. i have mentioned an e.g below in which i change the owner to 'dbo' which is the default in most of the cases.

   EXEC sp_changeobjectowner 'dboXXX.tblName', 'dbo'

   You will have to repeat this for every table which has this owner i.e. dboXXX or watever by just changing the first parameter. like say
EXEC sp_changeobjectowner 'dboXXX.tblName1', 'dbo'
EXEC sp_changeobjectowner 'dboXXX.tblName2', 'dbo' and so on.......

   I hope this is wat u needed.

   Report

Webhost4life Fo... » Database Relate... » MSSQL » CREATE TABLE?

webhost4life.com