December 11, 2008 at 8:23 pm
Sorry to ask this dumb question..i did search in this forum but could notfind exactly what i needed....i was told to script out the schemas in 2000 db to a new db in 2005...now does that mean i need to copy users,roles,stored procs etc from 2000 database to 2005?...then i can do it through copy wizard right...so is it that copying schema is copying everything except data?..please siggest me.
December 11, 2008 at 9:01 pm
I think you have to be careful with how you are using the term schema.
Have a read here.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
December 11, 2008 at 9:10 pm
well he told me to copy the schema..he said he doesnt need the data....
December 11, 2008 at 9:25 pm
Then you just open up SSMS.
Create a connection to the 2000 database server.
Right click on the database, and select tasks, generate scripts.
Select the items you need, and it will create a script for you. Be sure to toggle the True/False items to fit what you want. I believe you'll have to make sure the SQL 2005 script is selected and not 2000, but I am not sure if SQL 2005 will come up by default. Once you've created the script to your liking you can just run it on the new database server.
I hope that's enough info to get you started. There are other ways to do this, but that's one way.
If you aren't using a schema in sql 2000 (defined like the article I sent you), then I believe the default schema of dbo will be used in 2005.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
December 11, 2008 at 9:37 pm
thank you for your suggestion...i really dnt know whether i have SSMS installed on that box to connect to the 2000 server...just curious to know...when he said schema..does it means he needs tables,procs ..everything without data?...or just pure schemas..and i thought in 2000 there we no schemas...thx..
December 11, 2008 at 10:03 pm
If you have SQL 2005 installed with the SQL Server Management Studio then just try to make a connection to the SQL 2000 instance that has the database(s) in question. If you don't have SSMS on a box then you can't create the script in the same manner that I mentioned. You can do something similar in Enterprise Manager but it's not as thorough in my experience.
Schemas in 2000 existed in different form, and they are much improved in 2005.
Schema is used in the manner you first used it, but I also see it used the way that is listed in the following section. I believe most people stick with dbo though.
Here's a snippet from SQL 2000 Books Online:
CREATE SCHEMA
Creates a schema that can be thought of as a conceptual object containing definitions of tables, views, and permissions.
Syntax
CREATE SCHEMA AUTHORIZATION owner
[ [ ...n ] ]
::=
{ table_definition | view_definition | grant_statement }
Arguments
AUTHORIZATION owner
Specifies the ID of the schema object owner. This identifier must be a valid security account in the database.
table_definition
Specifies a CREATE TABLE statement that creates a table within the schema.
view_definition
Specifies a CREATE VIEW statement that creates a view within the schema.
grant_statement
Specifies a GRANT statement that grants permissions for a user or a group of users.
Remarks
CREATE SCHEMA provides a way to create tables and views and to grant permissions for objects with a single statement. If errors occur when creating any objects or granting any permissions specified in a CREATE SCHEMA statement, none of the objects are created.
The created objects do not have to appear in logical order, except for views that reference other views. For example, a GRANT statement can grant permission for an object before the object itself is created, or a CREATE VIEW statement can appear before the CREATE TABLE statements creating the tables referenced by the view. Also, CREATE TABLE statements can declare foreign keys to tables specified later. The exception is that if the select from one view references another view, the referenced view must be specified before the view that references it.
Permissions
CREATE SCHEMA permissions default to all users, but they must have permissions to create the objects that participate in the schema.
Examples
A. Grant access to objects before object creation
This example shows permissions granted before the objects are created.
CREATE SCHEMA AUTHORIZATION ross
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)
B. Create mutually dependent FOREIGN KEY constraints
This example creates mutually dependent FOREIGN KEY constraints. Other methods would take several steps to accomplish what is enabled by this CREATE SCHEMA example.
CREATE SCHEMA AUTHORIZATION ross
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1))
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1))
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
December 12, 2008 at 7:48 am
thank you for taking time and replying to my posts...so you mean to say that schemas in 2000 means tables,views only..
December 12, 2008 at 7:52 am
My understanding is that there are two ways to look at schema.
1. the way you initially said it.
2. the way I posted just above with the command giving authorization to ross. I haven't played with the CREATE SCHEMA in 2000 at all so I can't speak about it at length.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply