sp_msforeachtable

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  June 25, 2008 at 6:55 am

  #93493

  Hi all

  I am currently have a requirement to run some code against each table in a database and was thinking of using sp_msforeachtable. However I also have a requirement to set variables in the @command and use them in a calling procedure.

  Does anyone know if this is possible or do I need to write a cursor. Please see below for my dismal attempts.

  CREATE PROCEDURE [dbo].[uspRemoveServerInfo](

  @ServerName varchar(128)

  @ReturnTableName varchar(128) OUTPUT

  )

  AS

  DECLARE @Command varchar(8000),

  @SessionId varchar(32)

  SET @Command = '

  DECLARE @TableName varchar(128)

  SET @TableName = PARSENAME(''?'', 1)

  IF EXISTS (SELECT * FROM information_schema.columns where column_name = ''userid'' AND table_name = @TableName)

  BEGIN

  SET @ReturnTableName = @TableName

  END

  IF EXISTS (SELECT * FROM information_schema.columns where column_name = ''devicename'' AND table_name = @TableName)

  BEGIN

  SET @ReturnTableName = @TableName

  END

  IF EXISTS (SELECT * FROM information_schema.columns where column_name = ''device_id'' AND table_name = @TableName)

  BEGIN

  SET @ReturnTableName = @TableName

  END

  '

  exec sp_MSforeachtable @Command