Create Table creating table in the wrong schema

[mdav2]

I've got very little experience in SQL server so I am learning this as I go along (hence a recent post you may have spotted regarding parameterising a create database statement).

I am trying to write a script to create a new database schema, add several tables and stored procedures. The script works fine syntaxically, no errors are generated. The issue I am having is the new database is created as I would expect but the part of the script that creates the tables and stored procedures does so in the MASTER schema not the newly database schema. I am not running the script as the database owner, because part of the script creates the database I am then trying to create objects in.

I am using the SQL Server management console to run my commands. You have to select a schema to run the script against that schema but I though with me using the "USE database" command it would create the tables in the correct schema. The whole script is massive so I am not going to include it all (1350 lines). I know that the code to create the new schema works because that has been tested and appears as a schema to be used. I have issued a GO statement between each section to make sure statements are being sent to the server for processing. The results of what I have tried so far are as follows:


The code below works and creates the table in the SAW_LIVE schema as expected (hardcoding the schema name in the create table statement).

CREATE TABLE [Saw_Live].[dbo].[adiin] (
	[adiin_id]    [INT] IDENTITY (1, 1) NOT NULL ,
	[instructid]  [VARCHAR] (15) DEFAULT '' COLLATE Latin1_General_CI_AS NOT NULL ,
	[lastoffer]   [INT]          DEFAULT 0 NOT NULL 
) ON [PRIMARY]

The following also works, again hardcoding the USE statement to reference the Saw_Live (works with or without the GO):

USE Saw_Live
GO
CREATE TABLE [dbo].[adiin] (
	[adiin_id]    [INT] IDENTITY (1, 1) NOT NULL ,
	[instructid]  [VARCHAR] (15) DEFAULT '' COLLATE Latin1_General_CI_AS NOT NULL ,
	[lastoffer]   [INT]          DEFAULT 0 NOT NULL 
) ON [PRIMARY]

If I parameterise the script so I can specify the database name in a variable and run this the table is created the database in the MASTER database schema not the SAW_LIVE schema which I require. Regardless of if the GO is included or not.

-- The Database Name
DECLARE @DataBaseName   VARCHAR(MAX)
SET  @DataBaseName  = 'Saw_Live'

-- Command variable, used for building statements and EXEC'ing them
DECLARE @cmd VARCHAR(MAX)

-- Select the database
SET @cmd = 'USE ' + @DataBaseName
EXEC (@cmd)

GO

CREATE TABLE [dbo].[adiin] (
	[adiin_id]    [INT] IDENTITY (1, 1) NOT NULL ,
	[instructid]  [VARCHAR] (15) DEFAULT '' COLLATE Latin1_General_CI_AS NOT NULL ,
	[lastoffer]   [INT]          DEFAULT 0 NOT NULL 
) ON [PRIMARY]

The only way I can get a parameterised script to work is if I build the entire CREATE TABLE command into a variable and execute that like so:

-- The Database Name
DECLARE @DataBaseName   VARCHAR(MAX)
SET  @DataBaseName  = 'Saw_Live'

-- Command variable, used for building statements and EXEC'ing them
DECLARE @cmd VARCHAR(MAX)

-- Select the database
     SET @cmd= 'CREATE TABLE [' + @DataBaseName + '].[dbo].[adiin] ('
SET @cmd=@cmd+'[adiin_id] [INT] IDENTITY (1, 1) NOT NULL, '
SET @cmd=@cmd+'[instructid] [VARCHAR] (15) COLLATE Latin1_General_CI_AS NOT NULL , '
SET @cmd=@cmd+'[lastoffer] [INT] DEFAULT 0 NOT NULL '
SET @cmd=@cmd+') ON [PRIMARY]'

EXEC (@cmd)

The main reasons for wanting to parameterise scripts is we may need to recreate the database on different servers with different name, locations etc and its useful to have a script I can change 3 or 4 references rather than having to do a search and replace excercise. I may just bite the bullet and have to put all the hardcoded values back in because building exec statements for each table is not what I want to do. It just seems silly that the DECLARE command isn't of much use outside EXEC commands. I understand for one offs this is a lot of work but I have used the opportunity as a learning experience to see what's possible so as to understand how things work on SQL server.

Thanks in advance,

Mark.

Mark Davies
Warwickshire County Council

 

[bborissov]

EXEC() executes the script in different batch. After execution everything is reset to the current batch values. What database has been opened it stays opened after EXEC.
All declared variables in the current batch are NOT visible int the "EXEC" batch and vice versa.
So you are stuck with two choices (and both are dynamic sql):
1. The pne you use already

2.

DECLARE @DataBaseName   VARCHAR(MAX)
SET  @DataBaseName  = 'Saw_Live'
DECLARE @cmd VARCHAR(MAX)

SET @cmd= 'USE '+@DataBaseName+
          ' CREATE TABLE [dbo].[adiin] (
            [adiin_id] [INT] IDENTITY (1, 1) NOT NULL, 
            [instructid] [VARCHAR] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
            [lastoffer] [INT] DEFAULT 0 NOT NULL 
           ) ON [PRIMARY]'

EXEC (@cmd)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

I think you may be making this harder than it needs to be. Here's what I would do (Assuming SQL Server 2005 or above)....


Open SQL Server Management Studio.
Right click your database.
Click Tasks -> Generate Scripts

On the 'Select Database' step, highlight your database, and then click the check box for "Script all objects in the selected database".

Click NEXT

On the 'Choose Script Options' step, scroll down to "Script USE DATABASE" and set it to false. You should probably check the other scripting options too. Like, you probably want to script indexes and triggers. There are other things in the list that you may want to include or remove.

Click NEXT

On the "Output Options", you can send the script to a file, the clipboard, or a new query window. I usually script to a new query window. After reviewing the script, I can easily save it later if I want to.

Click Finish.

Since you did not script the USE command, you will need to modify the script a little.

At the top of the script, add this:

Create Database AnyNameYouWant
go
Use AnyNameYouWant
go
-- The rest of the script here

You should understand that this will only script your objects, tables, procedures, views, etc.... This will NOT copy any data you may have in any of the tables.

There is an alternative to this process. What you could do is create a database on your local server that is exactly the way you want it. Then, you could detach the database and copy the mdf and ldf file to your remote server. Then all you need to do is attach the database to remote server, set up some logins with appropriate permissions, and you're ready to go.

Alternatively, you could backup and restore your database to the target server. This approach is extremely similar to the detach/attach method. You'll still need to set up users and permissions.

There is a potential problem with the detach/attach and the backup/restore methods. There are these things called collations. Collations determine how strings are sorted and compared. For example.... when you order a string column, you expect A to sort before Z, but.... where do symbols fall in to the order? Should $ sort before % ? Collations determine this. Anyway... if the collation of your database does not match the default collation on the target server, you could start getting mysterious errors when using temp tables and table variables. There are ways to prevent this error, but that's a topic for another thread.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mdav2]

Thanks for the response guys. I know I am over complicating this and I could set this up using the generated scripts gmmastros suggested. I didn't set up any of these tables, it was a contractor we had in and I have no previous experience of SQL Server. I've used the whole process to improve my learning.

I like the way bborissov has set the EXEC statement and thats the way I'll be going. In the future I will probably just hardcode the values.

One thing that came out of the process for me was the sp_bindefault is generated by the scripts and this is being dropped by Microsoft (according to MSDN it should be removed and replaced).

Mark Davies
Warwickshire County Council