Just a little help required

  • Hi,

    I've put together this little SP to generate a Select statement for a table, what i'd like to do is also pass in the database name rather than having to run this from each database, and just have the one copy of the sp.

    I think i'm just having a mental block cos i can't work out how to do this, i've tried building a string like:

    @sql = 'Use ' + @db

    sp_executesql (@sql) but that just sets it for that particular execution not the rest of the sp.  Can anyone point me in the right direction?  I know it can't be that difficult!

    CREATE PROCEDURE usp_GenSelect

    @db varchar(50), @tb varchar(50)

    AS

    /* Generates a select statement for a given table.  Must be in relevant database */

    declare @i int, @count int, @tbid int, @sql varchar(8000), @col varchar(50), @use nvarchar(50)

    --Set @use = 'Use ' + @db

    --exec sp_executesql @use

    select @tbid = id from sysobjects where name = @tb

     

    select @count = count(*) from syscolumns where id = @tbid

    Set @sql = 'Select '

    set @i = 1

    While @i < @count

     Begin

      select @col = name from syscolumns where id = @tbid and colid = @i

      set @sql = @sql + @col + ', '

      set @i = @i + 1

     end

    Select @col = name from syscolumns where id = @tbid and colid = @i

    set @sql = @sql + @col + ' From ' + @tb

    Select @sql

     

    Thanks


    Growing old is mandatory, growing up is optional

  • Check out the 'undocumented' stored procedure sp_MSForEachDB.

    You can do a search on this site for it and get descriptions of how to use it, or just look it up in the Master database and work it out.

    -SQLBill

  • Aside from Sql Bills Advice.

    Did you try to fully qualify your table. using

    databasename.dbo.tablename?

    So your build sql statement will look like this

    Select @col = name from syscolumns where id = @tbid and colid = @i

    set @sql = @sql + @col + ' From ' + @db + '.dbo.' + @tb

  • Thanks Ray, its actually the syscolumns/sysobjects table that i need to qualify and it doesn't seem to like having variables in the from clause.  I'm having a play with the foreachdb scenario at the mo, i'll let you know how it goes.


    Growing old is mandatory, growing up is optional

  • Since the tables involved in your query are structurally identical in every database, you could name your stored procedure sp_GenSelect and create it in the master database. The SP can then be executed from any database, and the SP will run in the context of the current database.

    USE master

    GO

    CREATE PROCEDURE sp_GenSelect @tb varchar(50) AS...

    GO

    USE abc

    EXEC sp_GenSelect 'mytable'

    USE def

    EXEC sp_GenSelect 'mytable'

     

  • Thanks everyone.  I've written it so that it uses ForeachDB now, should have realised about the point mentioned by mkeast.  Thanks again.


    Growing old is mandatory, growing up is optional

Viewing 6 posts - 1 through 5 (of 5 total)

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