Dynamic SQL With USE

  • I need to execute a dynamic command against different DBs.

    So for normal stuff I'd

    USE DB1

    COMMAND

    But I need to do this within and EXEC ??

    Any ideas.

    KlK, MCSE


    KlK

  • Can also do a

    Select * from Database..Table Where

  • Unfortunately what I am trying to EXEC is basically a CREATE .... or Alter ...

    I thought I had seen this somewhere ??

    KlK, MCSE


    KlK

  • You can do what you want by building dynamic sql that builds dynamic sql, that then gets executed. Here is an example that issues a USE statement, followed by an alter table:

    declare @CMD char(1000)

    set @CMD = 'use test ' + char(13) + 'declare @cmd char(1000)' + char(13) +

    'set @cmd = ''alter table test_it add col_a int''' + char (13) +

    'exec (@cmd)'

    print @cmd

    exec (@cmd)

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Thanks Greg, a slight variation of what you provided, solved it perfectly.

    KlK, MCSE


    KlK

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

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