June 25, 2008 at 8:39 am
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
June 25, 2008 at 9:10 am
Kwisatz78 (6/25/2008)
Hi allI 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
If all you are doing is checking for "metadata" you only need to query "information_schema.columns" and you won't need sp_MSforeachtable at all. Am I missing something ?
* Noel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply