Copying Tables structure from SQL 2000 to Local database.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Strategies » Copying Tables structure from SQL 2000 to...

Copying Tables structure from SQL 2000 to Local database.

Rate Topic

Display Mode

Topic Options

Author

Message

razausmani

Posted Wednesday, December 30, 2009 12:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 31, 2009 3:44 AM
Points: 2, Visits: 2

Dear Friends,

I have created my own DB which included a table named "Tables_Information". In this table i have created 8 columns which includes, Seq No, Module Name, Table Name, Table Desciption, Table Fields, Fields type, primary key as boolean, Foreign key as boolean.

I want to copy all these information from SQL 2000 DB to my local table fields. Any method / procedure to fulfill this requirement???

Regards,

Raza Usmani
Software Engineer

Post #840203

Greg Charles

Posted Wednesday, December 30, 2009 9:11 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 4,007, Visits: 4,854

Is your local database that includes the new table in SQL 2005 or SQL 2000? Since you posted in the Integration Services forum, I could assume that it's in SQL 2005 and recommend that you use the Import Wizard in Management Studio.

Greg

Post #840403

razausmani

Posted Thursday, December 31, 2009 3:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 31, 2009 3:44 AM
Points: 2, Visits: 2

Both Databases are on the same Server which is SQL 2000. Somehow i have managed to collect almost all data but unable to get foreign keys information.

Regards,

Raza

Post #840753

Greg Charles

Posted Thursday, December 31, 2009 4:47 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 4,007, Visits: 4,854

You can find that in the sysforeignkeys system table. Here's a query that will list foreign key info for the tables in a database:

select t.name as 'Table', c.name as 'Foreign_Key_Constraint', rt.name as 'Referenced_Table'
  from (select distinct constid, fkeyid, rkeyid from sysforeignkeys) as FK
   join sysobjects c on c.id = FK.constid
   join sysobjects t on t.id = FK.fkeyid
   join sysobjects rt on rt.id = FK.rkeyid
order by 1,2,3

Greg

Post #840999

« Prev Topic | Next Topic »

Permissions