Dynamic SQL With USE

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  May 13, 2003 at 11:33 pm

  #159948

  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

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  May 13, 2003 at 11:44 pm

  #457064

  Can also do a

  Select * from Database..Table Where

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  May 14, 2003 at 7:20 am

  #457096

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

  I thought I had seen this somewhere ??

  KlK, MCSE

  
  KlK

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  May 14, 2003 at 7:41 am

  #457098

  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

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  May 14, 2003 at 9:52 am

  #457124

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

  KlK, MCSE

  
  KlK