January 10, 2012 at 3:30 pm
Hi,
I wanted to have generic stored procedure where in i have to execute the underlying stored procedure code
against the database which i pass it as parameter. I am looking for best possible ways of changing the context of the database.
Note: I have the underlying tables in all selected databases. In my below example, i have TableA and TableB in both db1 and db2.
Its just based on the parameter i.e dbname is pass i should get the appropriate data in the database.
create database db1
create database db2
use db1
go
create table TableA
(id int,
name varchar(100)
)
insert into TableA
select 101,'1'
union all
select 102,'1'
create table TableB
(id int,
name varchar(100)
)
insert into TableB
select 101,'2'
union all
select 102,'2'
use db2
go
create table TableA
(id int,
name varchar(100)
)
insert into TableA
select 101,'xx'
union all
select 102,'xx'
create table TableB
(id int,
name varchar(100)
)
insert into TableB
select 101,'yy'
union all
select 102,'yy'
use master
go
alter procedure p1
@dbname varchar(100)
as
begin
<<<< is there any way i can change the db context over here>>>
select * from TableA
select * from TableB
end
go
Any suggestions would be greatly appreciated.
I know a way of building a dynamic query. Is there any other better ways to tackle such requirement.
Here basically, i wanted to avoid creating multiple stored procs in each databases.
Thank you.
January 10, 2012 at 3:49 pm
I have tested few things at my end. Just wanted to check is there any other better ways to handle it.
way-1
EXEC sp_MSforeachdb 'SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
way-2
declare @cmd nvarchar(100)
declare @dbname varchar(100)
set @dbname = 'db1'
SELECT @cmd = 'SELECT ' + '''' + @dbname + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
print @cmd
EXEC sp_executesql @cmd
--way3
alter procedure p2
@dbname varchar(100)
as
begin
declare @cmd nvarchar(100)
SELECT @cmd = 'SELECT ' + '''' + @dbname + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
print @cmd
EXEC (@cmd)
end
exec p2 'db1'
exec p2 'db2'
January 10, 2012 at 5:48 pm
without using dynamic SQL, you need to rename it to be sp_* and simply want to mark the procedure as a "system" procedure.
use master
go
--any proc starting with sp_ is assummed to be in the master database first,
--then the database of the current connection is used.
alter procedure sp_p1
@dbname varchar(100)
as
begin
--<<<< db context is assumed to be the current connection's database.>>>
select * from TableA
select * from TableB
end
GO
--by making the database a "system" or "is_msshipped" proc, the current database context is used to go for the objects referenced:
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.* tables or assume a local table exists, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_p1'
Lowell
January 10, 2012 at 11:14 pm
Hi Lowell,
This is working perfectly. I am using sql 2005 and sql 2008. However i have few questions.
1. How to unmark the stored procedure as system proc if i want. in future if i want to drop the stored proc , how can i do that?
2. Is this an undocumented command? Is this safe enough to use this command on production environemnts?
January 11, 2012 at 1:58 am
i think u can solve the problems using Synonyms
use master
go
alter procedure p1
@dbname varchar(100)
as
begin
--<<<< is there any way i can change the db context over here>>>
IF @dbname = 'db1'
BEGIN
CREATE SYNONYM sTableA FOR db1..TableA
CREATE SYNONYM sTableB FOR db1..TableB
END
ELSE IF @dbname = 'db2'
BEGIN
CREATE SYNONYM sTableA FOR db2..TableA
CREATE SYNONYM sTableB FOR db2..TableB
END
select * from sTableA
select * from sTableB
end
go
January 11, 2012 at 4:42 am
Oracle_91 (1/10/2012)
Hi Lowell,This is working perfectly. I am using sql 2005 and sql 2008. However i have few questions.
1. How to unmark the stored procedure as system proc if i want. in future if i want to drop the stored proc , how can i do that?
2. Is this an undocumented command? Is this safe enough to use this command on production environemnts?
there is no way to unmark it. you would have to drop and recreate your procedure instead...when it gets recreated, it's "normal" unil you remark it.
i've been using the command or it's SQL 2000 equivilent for what seems like forever, exactly for the reason you are looking for...i wanted a procedure to use the current database context for looking at tables...for example all the sys.tables and sys.objects or sys.columns when searching for metadata,and other purposes too.
you will not be able to get a function marked as ms_shipped to magically infer the database context, that's the nature of the beast.
For documentation, i would certainly say this is well docuemnted 9simple google search) and ready for prime time. It's one of those tools that belong in your toolbox for those situations when you want a single proc source and variable database context.
here's just one develoepr example i use, where i want to say, find all table names or column names that exist in a database:
--if you are going to put this in MASTER,
--and want variable database context: if you use sys.objects and sys.columns, no changes are needed.
--to use sys.objects and sys.tables however,
--you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_find'
CREATE procedure [dbo].[sp_find]
@findcolumn varchar(50)
AS
BEGIN
SET NOCOUNT ON
--print object_name(@@PROCID)
SELECT
TableFound,
ColumnFound
FROM
(
SELECT
1 AS SortOrder,
sys.objects.name AS TableFound,
'' AS ColumnFound
FROM sys.objects
WHERE sys.objects.type IN('U' ,'V')
AND sys.objects.name LIKE '%' + @findcolumn + '%'
UNION ALL
SELECT
2 AS SortOrder,
sys.objects.name AS TableFound,
sys.columns.name AS ColumnFound
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.id=sys.columns.id
WHERE sys.objects.type IN('U' ,'V')
AND sys.columns.name like '%' + @findcolumn + '%'
) X
ORDER BY
SortOrder,
TableFound,
ColumnFound
END
[/code]
Lowell
January 11, 2012 at 8:04 am
Awesome. Thanks for sharing the information Lowell.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply