Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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