Post reply

Create table in other db

  • October 30, 2010 at 1:49 pm

    #230488

    Pff, can't get it to work.

    I have a SP in master, and it runs some checks on a db but it should also create a new table in the db it is running the checks on.

    From my SP code i have the database name in @otherdb.

    From my SP code i tried following but none did work:

    CREATE TABLE [@otherdb].[dbo].[my_new_table]

    => does not work

    USE [@otherdb]

    CREATE TABLE [dbo].[my_new_table]

    => 'use' is not allowed in sp

    SET @CMD = "USE [@otherdb] CREATE TABLE [dbo].[my_new_table]"

    => does not work

    I'm getting :crazy:

    Anyone could help me out? SP needs to stay in master.

  • October 30, 2010 at 3:20 pm

    #1244572

    Build the command in a string and use EXECSql to run it

    DECLARE @sql VARCHAR(1000)

    SET @sql = 'CREATE TABLE ' + '[' + @otherdb' + '].[dbo].[my_new_table]'

    Exec ( @sql)

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • October 31, 2010 at 9:15 am

    #1244628

    Andrew thanks for pointing that out to me. Was using wrong syntax.

    Thanks for the help man!

    Brgds

    T

  • October 31, 2010 at 9:43 am

    #1244630

    And just to be perfectly safe, use QUOTENAME

    SET @sql = 'CREATE TABLE ' + QUOTENAME(@otherdb) + '.[dbo].[my_new_table]'

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 4 posts - 1 through 4 (of 4 total)

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