March 25, 2010 at 12:02 pm
Hi All,
I just want to findout all the columns involved in a SP, View or function other than in "select" statement. Is it possible?
I thought of using "sp_depends" built in function, but it will listout all the columns including "select" list also.
Any help would be greatly appreciated........
Srinivas.
March 25, 2010 at 4:16 pm
What are you trying to achieve? I know you want to get those columns... but for what purpose, what are you going to do with the info? I ask because I can't tell you a way to specifically do that, but there could be another way to achieve your end goal.
March 25, 2010 at 7:21 pm
Hi Seth,
thanks for the reply.
we are writing a stored procedure to automate the process of code review on a high level to ensure that all the developers are following coding standards and best practices.
I want this info to find out whether these columns(join &where condition columns) have indexes on them or not. If not in the report generated, we will suggest having indexes on these columns.
Best Regrads,
Srinivas
March 31, 2010 at 2:06 am
So, no one has an answer for this..... 🙂
March 31, 2010 at 2:53 am
srinivas-356817 (3/31/2010)
So, no one has an answer for this..... 🙂
That is not only question its about to work out to achive the aim to do it.
Try to do it from the application.
Idea is that get the script of procedure view or function from the system view sys.sql_modules
and get the list of column names with the table name and search for the column name which are coming just after keywords WHERE, GROUP BY, ON, HAVING etc.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 31, 2010 at 3:14 am
Hi Vaibhav, thanks for the reply.
The approach suggested by you will be very good if we have a small SP which doesnt have any complexity code involved.
But, if the stored procedure having derived tables, CTEs and sub-queries, it will always be difficult to go with ur approach.
I need an approach using which we can ensure that our code will work for any given SP/View/function.
Again, thanks for your reply.
Regards,
Srinivas.
March 31, 2010 at 3:21 am
srinivas-356817 (3/31/2010)
Hi Vaibhav, thanks for the reply.The approach suggested by you will be very good if we have a small SP which doesnt have any complexity code involved.
But, if the stored procedure having derived tables, CTEs and sub-queries, it will always be difficult to go with ur approach.
I need an approach using which we can ensure that our code will work for any given SP/View/function.
Again, thanks for your reply.
Regards,
Srinivas.
Yeah I agree with you? when i will get any solution or approach i will let you know
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 31, 2010 at 3:42 am
For finding the all list of parameter used in SP you can use below mention query.
SELECT sp.name, parm.name AS Parameter, typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm
ON sp.object_id = parm.object_id
JOIN sys.types typ
ON parm.system_type_id = typ.system_type_id
Regards
Vijay
March 31, 2010 at 3:47 am
vijay.s (3/31/2010)
For finding the all list of parameter used in SP you can use below mention query.
SELECT sp.name, parm.name AS Parameter, typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm
ON sp.object_id = parm.object_id
JOIN sys.types typ
ON parm.system_type_id = typ.system_type_id
Regards
Vijay
What is the use of it in above requirement
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 31, 2010 at 3:52 am
In Store Procedure defination you always use use SP parameters with in where clause. So at least with this query you can easily identify what parameter used for what purpose.
March 31, 2010 at 5:08 am
Or Involve a table column in where clause you can use below mention query.
DECLARE
@ObjectName varchar(11),
@ColumnName varchar(20),
@message varchar(80)
DECLARE column_cursor CURSOR FOR
SELECT OBJECT_NAME(C.OBJECT_ID), C.NAME
FROM SYS.COLUMNS C, SYS.TABLES T
WHERE C.OBJECT_ID = T.OBJECT_ID
AND T.TYPE = 'U'
--- For Particular tables's column you can add condition here
OPEN column_cursor
FETCH NEXT FROM column_cursor
INTO @ObjectName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Column Used: ' + @ColumnName
PRINT @message
SELECT OBJECT_NAME(M.OBJECT_ID) [Object Name]
FROM SYS.SQL_MODULES M, SYS.OBJECTS O
WHERE M.OBJECT_ID = O.OBJECT_ID
AND DEFINITION LIKE '%WHERE%'
AND DEFINITION LIKE '%'+ @ColumnName +'%'
IF @@FETCH_STATUS <> 0
PRINT ' <<No Column>>'
FETCH NEXT FROM column_cursor
INTO @ObjectName, @ColumnName
END
CLOSE column_cursor
DEALLOCATE column_cursor
GO
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply