September 13, 2005 at 8:24 am
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
September 13, 2005 at 8:49 am
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
September 13, 2005 at 9:31 am
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
September 13, 2005 at 9:38 am
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
September 14, 2005 at 12:39 pm
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'
September 15, 2005 at 1:53 am
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