November 12, 2007 at 7:48 am
Hi,
i want to write stored procedure insuch a way where in it has to return the table name used stored procedure.
for example
create procedure proc1
as
begin
select * from student
end
once u exit exec proc1 it has return the table name used i.e "Student"
could you please help in doing this.
or it is possible in sql.
reply me soon at suhailquadri@gmail.com
November 12, 2007 at 8:10 am
From sql this is not a very simple task. We use a parser to analyze store procedure text and then determine the referenced objects like stored procedures. But this is done outside of the database. What are you trying to achieve by trying to get the names of the used tables? Is it only dependency information? For this you may want to look at third party tools as well.
Regards,
Andras
November 12, 2007 at 8:20 am
If you're writing the procedures yourself, rather than working with existing ones, then why not just use an output parameter to return the name(s) of the table(s) used? You can read about output parameters in the CREATE PROCEDURE topic in Books Online. Don't forget that it's likely that some procedures use more than one table.
John
November 12, 2007 at 8:27 am
Did you try sp_depends?
November 12, 2007 at 8:32 am
Rajesh Patavardhan (11/12/2007)
Did you try sp_depends?
Sp_depends can be used in certain cases, but it is really unreliable. Much depends on the database though. If you build up your objects in dependency order and do not alter them, sp_depends will work (it relies on information on sysdepends (2000) or sys.sql_dependencies (2005). However, if your database schemata is not built in dependency order and/or you alter textual objects, sp_depends will return only partial, and often incorrect data.
Regards,
Andras
November 12, 2007 at 9:01 am
well i wrote sample stored procedure in this way:-
alter procedure proc1
as
begin
select * from student
insert into cust1 values('eric')
exec sp_depends proc1
end
exec proc1
output i got like:
dbo.sp_depends stored procedure no no @objname
dbo.student user table no yes sno
dbo.student user table no yes sname
dbo.cust1 user table yes no cname
-----------------------------
Now why the student is repeated twice ?
cant i get only table names like student and cust1 as these are commited successfully...!
November 12, 2007 at 9:05 am
suhailquadri (11/12/2007)
...output i got like:
dbo.sp_depends stored procedure no no @objname
dbo.student user table no yes sno
dbo.student user table no yes sname
dbo.cust1 user table yes no cname
-----------------------------
Now why the student is repeated twice ?
cant i get only table names like student and cust1 as these are commited successfully...!
The last column of the resultset is the column (in this case the dependency is on the sno and sname columns.
This is documented in http://msdn2.microsoft.com/en-us/library/ms189487.aspx
Regards,
Andras
November 12, 2007 at 9:13 am
i got the solution in this way..
thnx and let me know if any possible way
alter procedure proc1
as
begin
select * from student
insert into cust1 values('eric')
exec sp_depends proc1
end
----------------------------------------------
create procedure proc2
as
begin
CREATE TABLE #Inputbuffer(
oname nvarchar(255),
EventType NVARCHAR(30) NULL,
updated varchar(10),selected varchar(10),
EventInfo NVARCHAR(255) NULL
)
insert into #Inputbuffer exec sp_depends 'proc1'
SELECT distinct oname FROM #Inputbuffer
drop table #Inputbuffer
end
exec proc1
exec proc2
November 12, 2007 at 9:32 am
suhailquadri,
Sorry to interrupt in middle,
I was bit concerned what you are trying to do here. Could you please post more details on your requirement?.
If you decide to use sp_depends (in spite of risks/unreliability issues highlighted above), I feel the sp_depends to be used outside the stored procedure just to check what are dependencies.
When I looked at your code, it looks like you are planning to put this as last line in all the stored procedures. Please correct me if I am wrong...
This might affect
a) Performance to great extent
b) existing business logic(since this adds a extra tablename dump at the end)
Rajesh
November 12, 2007 at 10:22 am
Well hi,
I am Mohammed Suhail ahmed working on Business Objects.
My client wants me write such a stored procedure to get the tables involved.
He need to display the table name(s) in web application.
Do you have any alternative with respect to this you can give me .
I m glad to you.
Thanks & Regards,
Mohammed Suhail Ahmed (9789990522)
Cybernet Slash Support
Chennai
November 12, 2007 at 10:55 am
It is still not clear to me.
Assuming the requirement "web application need to display the dependency information of the stored procedure being executed".
This has two parts
a) executing stored procedures
b) finding dependency of a stored procedure
these two need to be distinguished separately as first one is a business logic requirement and second one is a administration functionality, which to me to be kept independent of the application business logic.
Coming to implementation you can have two separate calls from web application one for executing sp and other to find dependency.
The second one can be a common routine which gives dependency information given stored procedure name.
Once this is done, it is also interesting to know what this information is used for in web application.
i.e, dependency information may only required only to find dependency whenver a stored procedure errors or when run in debug mode or it may even suffice to have a separate webpage to list the dependencies given stored procedure name.
November 12, 2007 at 11:25 am
well with the client request i gave solution.
i never ask for what he needs !
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply