Post reply

update many tables(Give tables'' names as parameters)

  • April 23, 2006 at 7:01 am

    #113955

    Hi all

    How r u?

    i have many tables have same columns

    and i want write one procedure with parameter (name of table)

    such as

    CREATE PROCEDURE updatetbl @prmName nvarchar(100)

    AS

     update @prmName

    set col1=1

    GO

    Can i do that?

    thanks

  • April 23, 2006 at 8:41 am

    #633682

    CREATE PROCEDURE updatetbl @prmName nvarchar(100)

    AS

    BEGIN

    declare @sql varchar(100)

    select @sql = 'update ' + @prmName + ' set col1 = 1'

    exec (@sql)

    END

  • April 24, 2006 at 2:10 am

    #633705

    Hi,

    there are a couple of undocumented system stored procedures, sp_MSforeachtable and sp_MSforeachdb which take a T-SQL command or commands as a parameter and allow you to replace the tablename or dbname with a question mark '?'

    check out the short article by Brian Knight on this website at

    http://www.sqlservercentral.com/columnists/bknight/sp_msforeachtable.asp

    or the one by Greg Larsen at

    http://www.databasejournal.com/features/mssql/article.php/3441031

    I have a system with a constantly changing schema, so rather than use the WITH RECOMPILE in my sp's, I use

    EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''

    when I have delivered major changes to the system which touch a significant proportion of the schema.

    David

    If it ain't broke, don't fix it...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply