Using dynamic SQL on Information_schema views

• blom0344

  SSCrazy

  Points: 2239

  More actions

  April 19, 2012 at 3:32 am

  #256576

  I'm using some scripts to insert small sets of data from DB to DB and everything works using dynamic SQL. However , I cannot get a simple Select on the Information_schema views to work..

  I may be missing the obvious, but what is wrong with the following script:

  DECLARE @DATABASE_NEW VARCHAR(30)

  DECLARE @QUERY VARCHAR(4000)

  SET @DATABASE_NEW = N'Some_database'

  SET @QUERY = 'SELECT TABLE_NAME,COLUMN_NAME FROM ['+ @DATABASE_NEW + N '].[INFORMATION_SCHEMA].[COLUMNS]'

  EXEC (@QUERY)

• Jan Van der Eecken

  SSCrazy Eights

  Points: 9029

  More actions

  April 19, 2012 at 3:59 am

  #1475912

  blom0344 (4/19/2012)

  ---

  I'm using some scripts to insert small sets of data from DB to DB and everything works using dynamic SQL. However , I cannot get a simple Select on the Information_schema views to work..

  I may be missing the obvious, but what is wrong with the following script:

  DECLARE @DATABASE_NEW VARCHAR(30)

  DECLARE @QUERY VARCHAR(4000)

  SET @DATABASE_NEW = N'Some_database'

  SET @QUERY = 'SELECT TABLE_NAME,COLUMN_NAME FROM ['+ @DATABASE_NEW + N '].[INFORMATION_SCHEMA].[COLUMNS]'

  EXEC (@QUERY)

  Is this statement exactly as you try to execute it? If yes, then remove the space between the N and '], i.e. it should read

  SET @QUERY = 'SELECT TABLE_NAME,COLUMN_NAME FROM ['+ @DATABASE_NEW + N'].[INFORMATION_SCHEMA].[COLUMNS]'

  --------------------------------------------------------------------------
  A little knowledge is a dangerous thing (Alexander Pope)
  In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

• blom0344

  SSCrazy

  Points: 2239

  More actions

  April 19, 2012 at 5:16 am

  #1475941

  Thanks Jan,

  I new it was something small and obvious..