2

I want to find all Tables alongside the columns used in a special Stored Procedure?

Can anyone help me here?

By the code below I found the tables but I want columns too

SELECT DISTINCT
o.id ,
o.name AS 'Procedure_Name' ,
oo.name AS 'Table_Name' ,
d.depid FROM
sysdepends d ,
sysobjects o ,
sysobjects oo WHERE
o.id = d.id AND
oo.id = d.depid ORDER BY
o.name ,
oo.name;
2
  • You'll have to tell us your brand of RDBMS.
    – Vérace
    Jul 28, 2016 at 4:52
  • I don't think the usage of columns is stored anywhere. You need to search in the source code of each procedure Jul 28, 2016 at 5:52

3 Answers 3

4

Update: You can get the information from a non-deprecated dynamic management function rather then the older dynamic management view, as pointed out by dfundako in the comments below. This would be the recommended approach as the function is available in all currently supported SQL Server releases and likely to be so in at least the next few releases too.

To update my previous example:

CREATE PROCEDURE test_depends 
AS BEGIN
    SELECT TOP 5 ou_id, prop_text FROM tr_n_ou WHERE prop_name = 'display_name'
    SELECT * FROM tr_n_org WHERE parent_display_name = 'Global'
    EXEC some_stored_procedure
END
GO

SELECT ISNULL(referenced_schema_name+'.','')+referenced_entity_name+ISNULL('.'+referenced_minor_name,'') AS Dependency
     , ISNULL(so.type_desc, sn.type_desc) AS DependencyType
     , CASE WHEN ISNULL(so.type_desc, sn.type_desc) IN ('USER_TABLE', 'VIEW') 
            AND referenced_minor_name IS NOT NULL
            THEN 'Column in '
            ELSE ''
            END
     + ISNULL(so.type_desc, sn.type_desc) AS DependencySubType
FROM sys.dm_sql_referenced_entities('dbo.test_depends', 'OBJECT') sr 
LEFT OUTER JOIN 
     sys.objects so 
     ON  so.object_id = sr.referenced_id
LEFT OUTER JOIN 
     sys.objects sn 
     ON  sn.name = sr.referenced_entity_name 
     AND sr.referenced_id IS NULL

GO
DROP PROCEDURE test_depends 

The caveats about ad-hoc SQL still apply.

Also if a procedure references something that is user schema dependent the referenced ID will be NULL in the DMF which is why I'm doing the extra lookup by name in this example. If the EXEC line specified a schema (i.e. EXEC some_schema.some_procedure) then the ID would have a value so the ID lookup would work, otherwise there could be several procedures with the same name in different schemas and the one called will depend on run-type details. Note also that the name lookup will return multiple rows for the same dependency in this case if there are multiple procedures of the same name.

________________________________________

Previous answer:

You can get this information, mostly, from sys.sql_dependencies but be aware that this is marked for deprecation so may not be available in future versions of SQL Server.

To test in one of your databases do something like:

CREATE PROCEDURE test_depends 
AS BEGIN
    SELECT TOP 5 column1, column2 FROM some_table WHERE column3 = 'some value'
    SELECT * FROM another_table
    EXEC some_stored_procdure
END
GO

SELECT sd.*, so.name, sc.name
FROM   sys.sql_dependencies sd
LEFT OUTER JOIN
       sys.objects so 
       ON  so.object_id = sd.referenced_major_id
LEFT OUTER JOIN
       sys.columns sc 
       ON  sc.object_id = sd.referenced_major_id 
       AND sc.column_id = sd.referenced_minor_id
WHERE sd.object_id = (SELECT object_id FROM sys.objects WHERE name = 'test_depends')

GO
DROP PROCEDURE test_depends 

You will see that is correctly lists the individual columns for the first table the procedure references, all columns in the second table as that selects *, and the procedure it calls.

Note that this does not deference views so you need a recursive query to get down to the base tables if that is your requirement, and you'll need to dig further too if a procedure calls other procedures.

Note again that sys.sql_dependencies is flagged as deprecated. I ran the above on a 2008r2 instance, the documentation on MSDN seems to list it only as far as 2014 so it may not be present in 2016 (I don't have access to any instances above 2012 in my current location in order to test this) though Kris notes in comments below that the view is still present in 2016. The replacement (sys.sql_expression_dependencies) doesn't list individual columns, at least in my test on a 2008r2 instance (the is only one entry per table with the minor ID 0 is both cases).

Another important thing to consider is that this view can't contain any references made by ad-hoc SQL run via EXEC (<string>) if you have such evilness in your procedures.

3
3

There is a system DMV that shows you all the tables and columns in your stored procedure:

SELECT * 
FROM sys.dm_sql_referenced_entities ('yourSPSchema.YourSprocObjectName', 'OBJECT')
-2

If you find tables name so you can find theirs columns name by following query:

SELECT *
FROM Your_DB_Name.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Your_Table_name'
1
  • I want the columns which are used in that query , not all the columns , Jul 28, 2016 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.