Post reply

Creating a Database from a SP

  • 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/

  • January 10, 2005 at 9:04 am

    #536680

    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

  • January 10, 2005 at 10:03 am

    #536694

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

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 10, 2005 at 9:22 pm

    #536746

    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/

  • January 11, 2005 at 6:53 am

    #536800

    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

  • January 12, 2005 at 11:29 am

    #537096

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

  • January 13, 2005 at 3:39 am

    #537228

    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

  • January 13, 2005 at 8:50 am

    #537308

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

  • January 14, 2005 at 1:46 am

    #537425

    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

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

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