Alex Rivera | Logout

How to execute store procedure for another DB?

Asked 2011-11-21T05:13:36.760
11

I have a stored procedure that should be able to be executed on any table of any database on my MS Sql Server. Most of the combination of EXEC and USE statements didn't result in anything. Here is the stored procedure:

CREATE PROCEDURE [dbo].[usp_TrimAndLowerCaseVarcharFields]
(
    @Database VARCHAR(200),
    @TableSchema VARCHAR(200),
    @TableName VARCHAR(200)
)
AS
BEGIN
    DECLARE @sSql VARCHAR(MAX)

    SET @Database = '[' + @Database + ']'
    SET @sSql = ''

    -- Create first part of a statement to update all columns that have type varchar
    SELECT @sSql = @sSql + COLUMN_NAME + ' = LOWER(RTRIM(' + COLUMN_NAME + ')), ' 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE DATA_TYPE = 'varchar'
    AND TABLE_CATALOG = @Database
    AND TABLE_SCHEMA = @TableSchema
    AND TABLE_NAME = @TableName

    SET @sSql = 'UPDATE ' + @Database + '.' + @TableSchema + '.' + @TableName + ' SET ' + @sSql

    -- Delete last two symbols (', ')
    SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

    EXEC(@sSql)
END

Please, advice what I have to do to execute it on [OtherDB].[TargetTable].

Edit
Report

1 Answer

5

The table name in the query you used is wrong, it is looking up into same database, but you do need to look up from different database. So the query will be as below:

SELECT @sSql = @sSql + COLUMN_NAME + ' = LOWER(RTRIM(' + COLUMN_NAME + ')), ' 
FROM [TargetDB].INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'varchar'
AND TABLE_CATALOG = @Database
AND TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName

-- [TargetDB] = @Database

The TargetDB will be same as your passing database (@Database)

If you want to use [TargetDB] dynamically then you need to generate sql(@sSql) and the execute the sql string.

answered 2011-11-21T06:57:40.100

Your Answer