Queries related to Creation of database,tables and indexes using SQL scripts

Queries related to Creation of database,tables and indexes using SQL scripts

Post by Karthik Dathath » Sun, 09 Jun 2002 00:07:17



Hello All,
    First of all I would like to thank all the newsgroup members who
have answered my earlier queries on SQL Server in this newsgroup.
    I have still some more queries.I searched the net and
newsgroups,but
of not much help.BOL and SQL Server books provide some information,but
may be my understanding is poor.
    I am in the process of creating a database for a project in which
I am working on.I have to use Transact-SQL statements and other sp_*
procedures to create the database and other related stuff using
scripts.
I am running the scripts using isqlw utility(command line version of
SQL Query Analyzer).I tried with 'isql' and 'osql' but faced some
problems.
So then find out 'isqlw' was fine.
    I ran the isqlw utility as below

   D:\> isqlw -U sa -P sa -i d:\karthikd\createmyDb.sql -o
d:\karthikd\output.txt

    Let me explain the steps I carried out

1. I created a database using the statement CREATE DATABASE

   CREATE DATABASE myDB ON PRIMARY
   ( NAME = 'myDB_Data',
     FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\myDB.mdf',
     SIZE = 30,
     FILEGROWTH = 10% )
   LOG ON
   ( NAME = 'myDB_log',
     FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\myDB.ldf',
     SIZE = 10,
     FILEGROWTH = 10% )

   Basically my requirement was to have a single data file.The
database
   creation was successful.I could see a new database created in the
list
   of databases in Enterprise Manager.

   In BOL,it has been mentioned that FILEGROUP clause can be specified
when
   multiple data files are being used.But can I specify a FILEGROUP
for a
   single data file.If yes,may I know the syntax.Because when I tried
   adding the FILEGROUP clause before LOG ON clause or after the LOG
ON
   clause it throws a error saying "Improper place of FILEGROUP
usage".

   May I know which COLLATION I can use in case I need to support
multiple
   languages(Internationalization)??

2. The database creation was fine.Next is I have to create
tables.Before this
   I need to have users other than the 'dbo'(who seems to be only user
created
   by default when the database is created).

   For this I tried to use the following sp_*'s as below
         sp_addlogin
         sp_grantdbaccess


= 'myDB'

   When this code is run,I checked the users in the database 'myDB' in
EM.It didn't showed the user 'karthik'.If I need to have a user other
than 'dbo' what
procedure I need to use.How can that user get the privelege to create
tables/indexes in the database.

3. Next I placed the CREATE TABLE statement as below

   CREATE TABLE SystemUser
   (
    sysUserId       numeric(28,0) IDENTITY(4,1) PRIMARY KEY,
    CardId         char(46)  NOT NULL,
    Password       char(10)  NOT NULL,
    emailAuth       char(100),
    userType        smallint DEFAULT 0,
    authSourceId    numeric(28,0)  DEFAULT 1,
    authGroupId     numeric(28,0)  DEFAULT 1,
    authFieldId     numeric(28,0)  DEFAULT 1,
    authFiledValue  char(100),
    typeMap         image,
    privateMap      image,
    cardId_lc      char(46) NOT NULL UNIQUE
   )

   Instead of creating the table under 'myDB' database,the table got
created under 'master' database.Is it that whenever any object is
created we need to qualify the object with
<database_name>.<owner>.<objectname>?
       i.e myDB.dbo??.myTable

   Is that not I can specify just myTable and the table can be created
under
   'myDB' database.

   I tried the statement USE myDB before creating the table.But it
complained
   that 'Could not locate myDB in sysdatabases.Make sure it is
correct".

   When I used USE I don't know the database itself couldn't get
created sometimes.

 Basically a user with a password all mentioned in a config,should get
control
of the database and start creating tables and indexes.So in
sp_addlogin it
would be something like this

'$DB_USER_PWD$',

-----------
  I could find lot of tutorials,SQL books.They mention creation of all
this
  using EM.

  I have people around with limited knowledge on this.That's the
reason
  I am seeking help from this forums....

Thanks & Regards,
Karthik

 
 
 

Queries related to Creation of database,tables and indexes using SQL scripts

Post by Ron Talmag » Wed, 12 Jun 2002 02:42:41


Karthik,

1. You should use osql.exe if possible. The isql utility will not support
new Transact-SQL features.

Transaction logs do not use filegroups.

You can use Unicode character data types to support multiple languages.

2. After you grant access to a user to a database, you need to assign
permissions. Take a look at sp_addsrvrolemember and sp_addrolemember, as
well as the GRANT command.

3. Yes, you can use Create Table MyDb.dbo.SystemUser. If you use the USE
MyDB first in the script, be sure to add a GO after it and before the Create
Table.

Hope this helps,
Ron


> Hello All,
>     First of all I would like to thank all the newsgroup members who
> have answered my earlier queries on SQL Server in this newsgroup.
>     I have still some more queries.I searched the net and
> newsgroups,but
> of not much help.BOL and SQL Server books provide some information,but
> may be my understanding is poor.
>     I am in the process of creating a database for a project in which
> I am working on.I have to use Transact-SQL statements and other sp_*
> procedures to create the database and other related stuff using
> scripts.
> I am running the scripts using isqlw utility(command line version of
> SQL Query Analyzer).I tried with 'isql' and 'osql' but faced some
> problems.
> So then find out 'isqlw' was fine.
>     I ran the isqlw utility as below

>    D:\> isqlw -U sa -P sa -i d:\karthikd\createmyDb.sql -o
> d:\karthikd\output.txt

>     Let me explain the steps I carried out

> 1. I created a database using the statement CREATE DATABASE

>    CREATE DATABASE myDB ON PRIMARY
>    ( NAME = 'myDB_Data',
>      FILENAME = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\myDB.mdf',
>      SIZE = 30,
>      FILEGROWTH = 10% )
>    LOG ON
>    ( NAME = 'myDB_log',
>      FILENAME = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\myDB.ldf',
>      SIZE = 10,
>      FILEGROWTH = 10% )

>    Basically my requirement was to have a single data file.The
> database
>    creation was successful.I could see a new database created in the
> list
>    of databases in Enterprise Manager.

>    In BOL,it has been mentioned that FILEGROUP clause can be specified
> when
>    multiple data files are being used.But can I specify a FILEGROUP
> for a
>    single data file.If yes,may I know the syntax.Because when I tried
>    adding the FILEGROUP clause before LOG ON clause or after the LOG
> ON
>    clause it throws a error saying "Improper place of FILEGROUP
> usage".

>    May I know which COLLATION I can use in case I need to support
> multiple
>    languages(Internationalization)??

> 2. The database creation was fine.Next is I have to create
> tables.Before this
>    I need to have users other than the 'dbo'(who seems to be only user
> created
>    by default when the database is created).

>    For this I tried to use the following sp_*'s as below
>          sp_addlogin
>          sp_grantdbaccess


> = 'myDB'

>    When this code is run,I checked the users in the database 'myDB' in
> EM.It didn't showed the user 'karthik'.If I need to have a user other
> than 'dbo' what
> procedure I need to use.How can that user get the privelege to create
> tables/indexes in the database.

> 3. Next I placed the CREATE TABLE statement as below

>    CREATE TABLE SystemUser

>     sysUserId       numeric(28,0) IDENTITY(4,1) PRIMARY KEY,
>     CardId         char(46)  NOT NULL,
>     Password       char(10)  NOT NULL,
>     emailAuth       char(100),
>     userType        smallint DEFAULT 0,
>     authSourceId    numeric(28,0)  DEFAULT 1,
>     authGroupId     numeric(28,0)  DEFAULT 1,
>     authFieldId     numeric(28,0)  DEFAULT 1,
>     authFiledValue  char(100),
>     typeMap         image,
>     privateMap      image,
>     cardId_lc      char(46) NOT NULL UNIQUE
>    )

>    Instead of creating the table under 'myDB' database,the table got
> created under 'master' database.Is it that whenever any object is
> created we need to qualify the object with
> <database_name>.<owner>.<objectname>?
>        i.e myDB.dbo??.myTable

>    Is that not I can specify just myTable and the table can be created
> under
>    'myDB' database.

>    I tried the statement USE myDB before creating the table.But it
> complained
>    that 'Could not locate myDB in sysdatabases.Make sure it is
> correct".

>    When I used USE I don't know the database itself couldn't get
> created sometimes.

>  Basically a user with a password all mentioned in a config,should get
> control
> of the database and start creating tables and indexes.So in
> sp_addlogin it
> would be something like this

> '$DB_USER_PWD$',

> -----------
>   I could find lot of tutorials,SQL books.They mention creation of all
> this
>   using EM.

>   I have people around with limited knowledge on this.That's the
> reason
>   I am seeking help from this forums....

> Thanks & Regards,
> Karthik