• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

creating schemas

if im asked to create a database and then create a schema in the model database would it simply be
just this.

for example:
my database is called db1

and the script would be
create schema db1?

and later after creating a schema i would like to create a table called tb1 within that schema db1
would it just be Create table db1.tb1?

finally if i were to create a table very similar to
sys.database_files view

how would i determine the data types, properties constraints, primary keys, foreign keys, checks, references
Can you provide a sample
create table for the sys.database_files view?

0
salesprod
Asked:
salesprod
  • 3
  • 3
4 Solutions
 
Jai STech ArchCommented:
its advisable not to have the schema name as the database name

create schema schemaname
correct
would it just be Create table db1.tb1?
yes--you can also move the existing tables to this schema
ALTER SCHEMA schemaname TRANSFER dbo.[existingtable]

how would i determine the data types, properties constraints, primary keys, foreign keys, checks, references
can you ellaborate more on what you need ?
0
 
salesprodAuthor Commented:
when i query this as select * from sys.database_files i get a view

I was wondering if you can show me how that was created the steps using the create table and the types of properties that was used.
0
 
Jai STech ArchCommented:
they are called SYS tables which are managed by SQL SERVER and cannot be modified by any external means...we dont have the option to make an update to these tables as it will seriously affect the DB architecture...
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dbbishopCommented:
jaiganeshsrinivasan said, "...it will seriously affect the DB architecture..." What he should have said was, "it could lead to you having a completely corrupt, unaccessible, ruined, unusable piece of junk that once was a database." :-)
0
 
salesprodAuthor Commented:
yes i understand that.
Would it be possible to create a similar architecture like that and how would that be.
thks.
0
 
Jai STech ArchCommented:
i meant that you know...i was just trying to SCARE people...i think i succeeded a little... ;-))..seriously ...jst kidding
0
 
salesprodAuthor Commented:
hmm? im confused.

create table files where files wouuld be the sys view.. as an example
within this table, not sure what type of columns to define and properties to set.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now