Just a little help required

Question

Just a little help required

bond007

SSCrazy

Points: 2056
More actions
September 13, 2005 at 8:24 am

#89030

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

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

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

Ray M SSC-Insane Points: 21093 More actions · Answer 2

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

bond007 SSCrazy Points: 2056 More actions · Answer 3

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

vadba SSChampion Points: 11133 More actions · Answer 4

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'

bond007 SSCrazy Points: 2056 More actions · Answer 5

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