Scan SQL stored procedures/udfs/views, return the table name with columns

Iterate through SQL stored procedures, udfs, and views returning the table name with column names referenced by SELECT, WHERE, ORDER BY, GROUP BY, and JOIN.

Feed this demo with a SQL script file, generate output like this in stdout or in a text file.

scantable test.sql

Table Name: <Table Name1>
   SELECT:        <ColumnName1>, <ColumnName3>
   WHERE:        <ColumnName1>, <ColumnName2>
   ORDER BY:   <ColumnName>
   GROUP BY:  <ColumnName>
   JOIN:            <ColumName>, <ColumnName2>
 
 
Table Name: <Table Name2>
   SELECT:        <ColumnName>
   WHERE:        <ColumnName1>, <ColumnName2>
   ORDER BY:   <ColumnName>
   GROUP BY:  <ColumnName>
   JOIN:            <ColumName>

Let’s take this SQL for example:

CREATE FUNCTION dbo.ufnGetStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0
    RETURN @ret
END;
GO

USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

-- Table-Valued Functions
USE AdventureWorks;
GO
CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

SELECT * FROM Sales.fn_SalesByStore (602);

USE AdventureWorks;
GO
CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO

-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.fn_FindReports(109)
ORDER BY Sort;

Output generated by this tool is:

Table Name:	Sales.SalesOrderDetail
SELECT LIST:	LineTotal
JOIN:		ProductID, SalesOrderID

Table Name:	Sales.SalesOrderHeader
WHERE:		CustomerID
JOIN:		SalesOrderID

Table Name:	DirectReports
SELECT LIST:	EmployeeID, Name, Title, EmployeeLevel, Sort
JOIN:		EmployeeID

Table Name:	HumanResources.Employee
SELECT LIST:	Title, EmployeeID, EmployeeLevel, Sort, FirstName, LastName
WHERE:		EmployeeID
JOIN:		ContactID, ManagerID

Table Name:	Production.ProductInventory
SELECT LIST:	Quantity
WHERE:		ProductID, LocationID

Table Name:	Production.Product
SELECT LIST:	ProductModelID, Name, ProductID
WHERE:		ProductModelID
GROUP BY:	ProductID, Name
JOIN:		ProductID

Table Name:	Person.Contact
SELECT LIST:	FirstName, LastName
JOIN:		ContactID

Download this tool with source code: .NET version, Java version