0

I have a database with 112 stored procs, 7 views and 88 tables. The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be doubling up on work. I already know I have a couple of reports I generate that use essentially the same information but are pulling from two different tables. I am trying to bring everything down to as few tables and stored procs as possible.

The only thing I can think of is running a query that queries the stored procs to see what tables they all reference, this should get me a list of all the tables that are actually even being used any more.

Also I would want to know which procs are actually being run so a query that shows last run time for each proc.

Improve this question
3
  • 1
    Is there a specific question? Sounds like you need to google referenced and referencing entities as well as sys.dm_exec_procedure_stats
    – dfundako
    Commented Jun 9, 2016 at 13:40
  • There's not going to be an easy answer, but it sounds like you have learned the importance of documenting your database and hopefully constructing your application code more thoughtfully in the future.
    – Molenpad
    Commented Jun 9, 2016 at 13:44
  • Without a doubt, unfortunately things around here were rush rush rush and I fell into the same trap I swore I would never fall into again. Live Learn Repeat lol
    – scripter78
    Commented Jun 9, 2016 at 14:34

1 Answer 1

Reset to default
2

We have all been there. It sounds like you don't have a specific question, but just need some help getting started. You need to learn about the sys tables available in SQL Server and the DMV's. They are insanely useful.

Ones that will be helpful in your case:

  • sys.procedures - gets you a list of your stored procedures
  • sys.object - gets a list of all objects
  • sys.dm_exec_procedure_stats - gives stats of your stored procedures, how many times they executed, when the last execute was, etc.
  • sys.dm_sql_referenced_entities - returns entities that are referenced by the object you provide (pass a stored procedure name and it will return all the tables and views it hits)
  • sys.dm_sql_referencing_entities - returns entities that refer to the object you pass (pass a table name and it will return all the stored procedures that hit that table)
Improve this answer
2
  • So far this query which after a ton of searching and only finding right after posting this broad question I found on a microsoft site. seems to have provided a lot of help and goes along with your suggestions Although it does not provide a referrencing list of tables but it looks like your suggestions might do that SELECT * FROM sys.dm_exec_procedure_stats AS s INNER JOIN sys.procedures AS p ON s.[object_id] = p.[object_id] ORDER BY p.name;
    – scripter78
    Commented Jun 9, 2016 at 14:20
  • SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
    – scripter78
    Commented Jun 9, 2016 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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