Creating a Database from a SP

Question

Post reply

Creating a Database from a SP

Dinesh Asanka

SSChampion

Points: 11058
More actions
January 10, 2005 at 2:42 am

#60589

Hi ,
I needed to create a Db, by reading a script which contains the table creation scripts
How can I do this?

My Blog: http://dineshasanka.spaces.live.com/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Hello,

Try the following:

declare @TableCreateStr VARCHAR(8000)

SET @TableCreateStr = 'CREATE TABLE TmpTbl (Col1 Int NULL)'

exec (@TableCreateStr)

Your only task now is to read the external file into the variable @TableCreateStr. I would probably use BCP to load the external file into a table. Once the data was in a table, then I would concatenate the rows into a varchar variable, and exec the variable as shown above.

There are probably faster ways to load the external file into a variable, but BCP is the method of choice in the environment I work in.

Hope this helps

Wayne

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I agree with Wayne. Load into a table and work with it. Or execute the script with xp_cmdshell calling isql.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

thankx you both,

but the my problem is i have a lengthey script. if I need any changes to be done then I have to modify the sp again

so my requirement is some thing liek this

Exec spCreateDb 'Tdb'

by executing this

1. it should create a db by the name of the Tdb which I have done

2. then from the given path (Hard coded) script should be read and execute it. at later stage only i have to do is the replacing the scripts.( NB this script contains the all the table/ sp/ view creation scripts)

Thankx in advance

My Blog: http://dineshasanka.spaces.live.com/

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Could you explain a little more on why you want this packaged into a stored procedure? I don't quite see the point of it, if practically everything else is already in a script - why not then have the CREATE DATABASE statement in there as well?

/Kenneth

Markus Veretin SSCommitted Points: 1565 More actions · Answer 5

Is there a way to generate db script automatically based on schedule? May be some third party tools.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Again, why would one want to do that? Generating databases automatically is in general not 'normal', so to speak. Is there any special circumstances which makes this the 'best' choice?

/Kenneth

Markus Veretin SSCommitted Points: 1565 More actions · Answer 7

I need to generate full db script every week as DBA documentation. Want to do it automatically on schedule.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Ah, I see.

This would probably be easiest by DMO (same stuff as EM uses) Probably someone has already put something together for this purpose, though I don't know of any right away. But try to google some.

/Kenneth