Query help RSS

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Query help

  Apr 30, 2012 07:05 PM|LINK

  Whats wrong with this?

  create procedure dbo.[schema_update]

  @dbname varchar(50)

  as

  begin

  select * from [@dbname].INFORMATION_SCHEMA.COLUMNS 

  end

  go

  now i am executing the sp as 

  execute [dbo].[schema_update] 'abcd'

  i am getting the following error

  invalid object name @dbname..INFORMATION_SCHEMA.COLUMNS 

  i am trying to pass database name as parameter

• • Reply
  

  silvioyf

  Participant

  1754 Points

  340 Posts

  Re: Query help

  Apr 30, 2012 07:23 PM|LINK

  try:

  declare @SQL nvarchar;
  
  set @SQL = N'select * from ' + [@dbname] + N'.INFORMATION_SCHEMA.COLUMNS';
  
  execute sp_executeSQL @SQL;

  Hope this helps. 

  www.imoveisemexposicao.com.br
• • Reply
  

  Vipindas

  Contributor

  5514 Points

  810 Posts

  Re: Query help

  Apr 30, 2012 07:23 PM|LINK

  create procedure dbo.[schema_update]
      @dbname varchar(50)
  as
  begin
      declare @sql nvarchar(255);
      set @sql = 'select * from ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS';
      EXECUTE (@sql)
  end
  go

  Hope this helps...

  Please remember to mark as answer if it helps you
• • Reply
  

  march11

  Contributor

  3017 Points

  1366 Posts

  Re: Query help

  Apr 30, 2012 07:24 PM|LINK

  I would avoid using the word schema in your procedure name as it is likely a reserved word for starters.

   

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: Query help

  Apr 30, 2012 08:43 PM|LINK

  hi i am trying something like this

  declare @sql nvarchar(255);

  set @sql = 'SELECT  Distinct(Table_Name) into #diff

  FROM'  +[@DbName]+ N'.INFORMATION_SCHEMA.COLUMNS where DATA_TYPE in ('text', 'ntext', 'image','timestamp') ';

  
  

  i am getting the following error

  Incorrect syntax near  'text'

• • Reply
  

  silvioyf

  Participant

  1754 Points

  340 Posts

  Re: Query help

  Apr 30, 2012 08:54 PM|LINK

  Hi,

  Try:

  set @sql = 'SELECT  Distinct(Table_Name) into #diff FROM ' +
             @DbName +
             N'.INFORMATION_SCHEMA.COLUMNS where DATA_TYPE in (''text'', ''ntext'', ''image'', ''timestamp'')';

  Hope this helps. 

  www.imoveisemexposicao.com.br
  • Marked as answer by Chen Yu - MSFT on May 04, 2012 10:42 AM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Query help

  May 02, 2012 08:27 AM|LINK

  DECLARE @dbname NVARCHAR(1000)
  SET @dbname='abcd'
  
  DECLARE @SQLQuery NVARCHAR(1000)
  
  
  SET @SQLQuery='select * from '+@dbname+'.INFORMATION_SCHEMA.COLUMNS '
  
  EXEC sp_executesql @SQLQuery

  Yogesh Bhadauriya
  .Net All About