Get SELECT part of Stored Procedure

[robertfah]

Is it possible to get just the SELECT portion of a stored procedure?

So if I had a SP like:

SELECT foo1, foo2, foo3
FROM Foo

It would return: "foo1, foo2, foo3"

A bunch of us were pondering this idea over lunch and it got us curious. Any ideas?

 

[gmmastros]

Not easily. In fact, as far as I know, you would need to build a SQL parser to do it.

There is a SET command that you can use that will return just the column headers. Ex:

SET FMTONLY ON

Exec YourStoredProcedureHere

SET FMTONLY OFF

Even this will fail if you are using temp tables. The problem is, stored procedures give you too much functionality. For example, you could have a stored procedure that returns multiple result sets or the result set could depend on an input parameter. Ex:

Create Procedure Blah @TableName
As
SET NOCOUNT ON

If @TableName = 'People' Then
  Select * From People
If @TableName = 'Color' Then
  Select * From Color
Else
  Select 'No data' As Result

However... if you had asked about table-valued functions, the the answer would have been YES.

For table-valued functions:

Select * From Information_Schema.Routine_Columns

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Interesting....I did a bit of diggin' and found this:

SELECT text
FROM syscomments 
WHERE id = (SELECT id FROM sysobjects WHERE name = 'TestSP') 
ORDER BY colid

and while it returns multiple rows (in my case, 2), all the data is there. So if I save this out to a Tab-Delimited file, I get the full SP:

/*
    This is copyrighted material and may not be reproduced in any form
    without express written permission of My Company.
    Copyright c 2011 123 Company.
        This is copyrighted material and may not be reproduced in any form
    without express written permission of My Company.
    Copyright c 2011 123 Company.
        This is copyrighted material and may not be reproduced in any form
    without express written permission of My Company.
    Copyright c 2011 123 Company.
    This is copyrighted material and may not be reproduced in any form
    without express written permission of My Company.
    Copyright c 2011 123 Company.
    Created Date: 08/18/2011
    Author:     Me
    Purpose:    This SP is designed to return all issues
  				from the Issue table.
    
    History
    Mod Date    Mod By        Reference #            Reason for change
    ------------------------------------------------------------------------------------------
    9/5/2011	Me				45444				Added fields to the SELECT
*/

CREATE   PROCEDURE [dbo].[TestSP]
	@IssueID int = NULL
AS

	IF(@IssueID = NULL)
		SELECT Issues.*, mConfig.DBName, c.CustomerName, l.LocationName, l.LocationShortName, r.RoomName,
			a.FirstName + ' ' + a.LastName AS [POC], g.Name AS [Assigned Group], u.FirstName + ' ' + u.LastName AS [Created By], 
			u2.FirstName + ' ' + u2.LastName AS [Assigned User], ist.IssueStatus AS [Status], iss.IssueStatus AS [Sub-Status], 
			p.Priority, ic.IssueCategory, o.Origin, it.IssueType, csd_p.Product, csd_v.VersionNumber, svt.SupportVisitType,
			u3.FirstName + ' ' + u3.LastName AS [Resolved By], csd_psv.ProjectedSoftwareVersion
		FROM Issues Issues
		LEFT OUTER JOIN Customers c ON Issues.ConfigurationID = c.CustomerID
		INNER JOIN SCMaster.dbo.Configuration mConfig ON c.CustomerShortName = mConfig.CustomerCode
		LEFT OUTER JOIN Origins o ON Issues.OriginID = o.OriginID
		LEFT OUTER JOIN Locations l ON Issues.LocationID = l.LocationID
		LEFT OUTER JOIN Rooms r ON Issues.RoomID = r.RoomID
		LEFT OUTER JOIN Groups g ON Issues.AssignedGroupID = g.GroupID
		LEFT OUTER JOIN Users u ON Issues.CreatedByUserID = u.UserID
		LEFT OUTER JOIN Users u2 ON Issues.AssignedUserID = u2.UserID
		LEFT OUTER JOIN Users u3 ON Issues.ResolvedByUserID = u3.UserID
		LEFT OUTER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 0) ist ON Issues.IssueStatusID = ist.IssueStatusID
		LEFT OUTER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 1) iss ON Issues.IssueSubStatusID = iss.IssueStatusID
		LEFT OUTER JOIN Priorities p ON Issues.PriorityID = p.PriorityID
		LEFT OUTER JOIN SCMaster.dbo.Products csd_p ON Issues.ProductID = csd_p.ProductID
		LEFT OUTER JOIN SCMaster.dbo.Versions csd_v ON Issues.VersionID = csd_v.VersionID
		LEFT OUTER JOIN SCMaster.dbo.ProjectedSoftwareVersions csd_psv ON Issues.ProjectedSoftwareVersionID = csd_psv.ProjectedSoftwareVersionID
		LEFT OUTER JOIN SupportVisitTypes svt ON Issues.SupportVisitTypeID = svt.SupportVisitTypeID
		LEFT OUTER JOIN IssueCategories ic ON Issues.IssueCategoryID = ic.IssueCategoryID
		LEFT OUTER JOIN IssueTypes it ON Issues.IssueTypeID = it.IssueTypeID
		LEFT OUTER JOIN AllPOCs a ON Issues.POCID = a.AllPOCID AND c.CustomerID = a.CustomerID
		ORDER BY IssueID DESC
	ELSE
		SELECT Issues.*, mConfig.DBName, c.CustomerName, l.LocationName, l.LocationShortName, r.RoomName,
			a.FirstName + ' ' + a.LastName AS [POC], g.Name AS [Assigned Group], u.FirstName + ' ' + u.LastName AS [Created By], 
			u2.FirstName + ' ' + u2.LastName AS [Assigned User], ist.IssueStatus AS [Status], iss.IssueStatus AS [Sub-Status], 
			p.Priority, ic.IssueCategory, o.Origin, it.IssueType, csd_p.Product, csd_v.VersionNumber, svt.SupportVisitType,
			u3.FirstName + ' ' + u3.LastName AS [Resolved By], csd_psv.ProjectedSoftwareVersion
		FROM Issues Issues
		LEFT OUTER JOIN Customers c ON Issues.ConfigurationID = c.CustomerID
		INNER JOIN SCMaster.dbo.Configuration mConfig ON
 c.CustomerShortName = mConfig.CustomerCode
		LEFT OUTER JOIN Origins o ON Issues.OriginID = o.OriginID
		LEFT OUTER JOIN Locations l ON Issues.LocationID = l.LocationID
		LEFT OUTER JOIN Rooms r ON Issues.RoomID = r.RoomID
		LEFT OUTER JOIN Groups g ON Issues.AssignedGroupID = g.GroupID
		LEFT OUTER JOIN Users u ON Issues.CreatedByUserID = u.UserID
		LEFT OUTER JOIN Users u2 ON Issues.AssignedUserID = u2.UserID
		LEFT OUTER JOIN Users u3 ON Issues.ResolvedByUserID = u3.UserID
		LEFT OUTER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 0) ist ON Issues.IssueStatusID = ist.IssueStatusID
		LEFT OUTER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 1) iss ON Issues.IssueSubStatusID = iss.IssueStatusID
		LEFT OUTER JOIN Priorities p ON Issues.PriorityID = p.PriorityID
		LEFT OUTER JOIN SCMaster.dbo.Products csd_p ON Issues.ProductID = csd_p.ProductID
		LEFT OUTER JOIN SCMaster.dbo.Versions csd_v ON Issues.VersionID = csd_v.VersionID
		LEFT OUTER JOIN SCMaster.dbo.ProjectedSoftwareVersions csd_psv ON Issues.ProjectedSoftwareVersionID = csd_psv.ProjectedSoftwareVersionID		
		LEFT OUTER JOIN SupportVisitTypes svt ON Issues.SupportVisitTypeID = svt.SupportVisitTypeID
		LEFT OUTER JOIN IssueCategories ic ON Issues.IssueCategoryID = ic.IssueCategoryID
		LEFT OUTER JOIN IssueTypes it ON Issues.IssueTypeID = it.IssueTypeID
		LEFT OUTER JOIN AllPOCs a ON Issues.POCID = a.AllPOCID AND c.CustomerID = a.CustomerID
		WHERE Issues.IssueID = @IssueID
		ORDER BY IssueID DESC

As you can tell, I added a bunch of crap just to see if it would process larger SP statements and it appears to be working. I guess the next thing from here would be to write a front-end wrapper that would go through the resultset and parse out the data from the 'SELECT' to the 'FROM'....understading that it would only work with basic SQL and not what you (GMM) mentioned above.

 

[gmmastros]

and while it returns multiple rows

It returns multiple rows because the syscomments table stores the code for a stored procedure in an nvarchar(4000) column, so if the code is longer than 4000 characters, there will be multiple rows.

if you are using SQL2005 or newer, you can use this instead:

Select Object_Definition(Object_ID('YourProcedureNameHere'))

You will get one row and one column without having to deal with multiple rows per procedure.

I guess the next thing from here would be to write a front-end wrapper that would go through the resultset and parse out the data from the 'SELECT' to the 'FROM'

That is exactly what I meant by "you would need to build a SQL parser to do it". For simple procedures, this probably wouldn't be too difficult, but complicated ones will be a real problem.

Since you intend to write a front-end wrapper, I would encourage you to look in to the "SET FMTONLY ON" command. Like I mentioned before, this won't work if you are using temp tables or anything else "fancy", but your simple parsing method will be problematic too. I mean... just look at the code you posted:

SELECT Issues.*, mConfig.DBName, c.CustomerName,

To get all the columns, you would need to see what columns are in the Issues table. If you use the "SET FMTONLY ON" method, you will get a result set with 0 rows, but all the columns. I would encourage you to give this a try.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Hey thanks GMM....much appreciated. How this convo came about at lunch was a little too long to get into but in short, we have an app that has a "query builder" in it, which allows the user to build a query from tables and fields visually. We then have a function that builds the SQL based on what they've selected. This all works great but in the code, we actually have to put the SELECT portion into a string and concatinate with the rest of their selections. The problem is we end up having an SP with the same SELECT statement as in our code. So often times when a new field is introduced to our product, one of these (code or SP) gets forgotten to be updated and it takes a little while to track down.

SO..(takes a deep breath), we were wondering if we could write a SP that would return the SELECT statement of a specific SP and then we could use that in our code to generate the SQL from the query builder. This would elimated duplicate SQL statements and gives us a lot of flexibility....and the SP's that we would be retrieving from are very basic but have a ton of columns.

FMTONLY would work if we only needed the column names but in our case, we are building SQL and the column alisas' are needed....so:

SELECT Issues.*, mConfig.DBName, c.CustomerName,

returns "Issues.*, mConfig.DBName, c.CustomerName," and we really don't need to get all the column names of Issues.* because they are not needed.

I think this is something I will tackle tomorrow AM, first thing. Learning a LOT about SQL from this forum!

 

[gmmastros]

We have something similar in our application.

We allow our customers to generate custom reports. Originally, we allowed them to pick different tables and then specify the join between them. For example, we have an address table. Each "person" can have a home address or a mailing address so there are 2 links from the person table to the address table.

This turned out to be very confusing for our customers.

Now we create a bunch of views and allow our customers to only pick one view, but they can pick any columns they want from the view. We then generate SQL and store it in a flat file on their computer (so they can share their custom reports). The views work out really well because we can easily get the columns that make up the view and we don't need to worry about the joins between the tables because they are defined in the view itself.

I have no idea if this would be appropriate for your situation, but I suspect it doesn't hurt to listen to another idea.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[starsky51]

I've been looking for a solution for a problem like this for months. As George has said, the only way to get a true answer is to buy a full blown SQL parser.

The closest I got was to dissect the execution plan and pull out any column references it contained. These references contain enough information to trace which columns were involved from each table. The downside is that these references are not limited to the columns that are returned by the stored proc. They include columns used in any part of the query, such as JOINs, WHEREs and GROUP BYs. Another downside is that it won't tell you anything about any calculated fields in the SELECT list.

Anyway, here is an example which runs in AdventureWorks (using SS2008):

--run the sproc first, to generate an execution plan
SET FMTONLY ON
EXEC dbo.uspGetBillOfMaterials @StartProductID = 0, @CheckDate = '2011-09-29 13:07:20'
SET FMTONLY OFF

SELECT DISTINCT split.part AS colref
FROM sys.dm_exec_cached_plans cache
CROSS APPLY sys.dm_exec_text_query_plan(cache.plan_handle, DEFAULT, DEFAULT) texts
CROSS APPLY dbo.ufnSplitDelimitedString(texts.query_plan, '<') split
WHERE split.part LIKE 'ColumnReference%' AND NOT split.part LIKE 'ColumnReference Column%'
AND texts.objectid = OBJECT_ID('dbo.uspGetBillOfMaterials')

It uses a bespoke function, ufnSplitDelimitedString, which splits a string by a given delimiter and returns them in a table. Here is the code for that:

CREATE FUNCTION dbo.ufnSplitDelimitedString (
	  @InputString VARCHAR(MAX)
    , @Delimiter VARCHAR(10))
RETURNS @tbl TABLE (part VARCHAR(MAX))
AS
BEGIN
    DECLARE @i INT, @j INT
    SET @i = 1
    WHILE @i <= LEN(@InputString) 
        BEGIN
            SELECT @j = CHARINDEX(@Delimiter, @InputString, @i + 1)
            IF @j = 0 SELECT  @j = LEN(@InputString) + 1
            INSERT INTO @tbl SELECT SUBSTRING(@InputString, @i, @j - @i)
            SELECT @i = @j + LEN(@Delimiter)
        END
    RETURN
END

It's ugly and slow, but it helps solve a few problems!