Create tables in a faster easier way

Hi,

I have abt 100 tables in my MS SQL Server 2000.

I would like to ask how can i generate a script that will auto these
tables when i move them to another server.

I also need to change the owner for each table, how do i do that?

Thanks.
jedistarAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
jedistar,
> I also need to change the owner for each table, how do i do that?
DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo' --------------------------replace dbo with new owner name
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN ('V', 'P', 'U')
ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''dbo''')
FETCH NEXT FROM CurDbObjects INTO @dbObject END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects

0
 
LowfatspreadCommented:
use the MS_foreachTAble  stored procedure

and run the sp_changeowner stored procedure First?

to script out the tables in the first place...
just right click the database in enterprise manager and
select all task  - script ...

make sure your visit each SCREEN and select all the required functions
e.g. indexes / constraints, etc...


but this should be a task you use your modelling/design tool for....  

what did you use to design the database in the first place...



0
 
jedistarAuthor Commented:
i merely added tables/fields etc.

what is MS_foreachTAble  stored procedure
and sp_changeowner stored procedure ?

How again.
0
 
SireesCommented:
To change the owner for all tables of a database at once

You can use the undocumented function ms_foreachtable and the stored procedure sp_changeobjectowner in Query Analyzer to archive that quite easy.

Example to take over for dbo: sp_msforeachtable "sp_changeobjectowner '?', 'dbo'".

0
 
jedistarAuthor Commented:
hrm still don't understand
0
All Courses

From novice to tech pro — start learning today.