1

I am in the process of optimising my database and I was thinking of changing the datatype for some columns from DATETIME to SMALLDATETIME on my tables.

Is there a system stored procedure that returns both the contents/code of a store procedure and the dependent table which will then allow me to do a join on a filtered list of tables?

Cheers!

EDIT1:

Im looking to programatically rename the stored procedures not track dependencies!

super9
  • 29,181
  • 39
  • 119
  • 172

3 Answers3

2

The built-in dependency tracking for SQL isn't very good for this type of work. Two tools come to mind thought...

  1. Red Gate SQL Dependency Tracker - Good for determining all the dependent code
  2. Visual Studio for Database Developers - Contains TSQL Code Analysis which can identify if a piece of data is being treated as an incorrect type.

Red Gate has a free trial on their stuff, which might get you through this job

STW
  • 44,917
  • 17
  • 105
  • 161
  • Sorry I wasnt very clear with my question but the Red Gate tools would not be helpful for my purposes. (I need to change the input variables of affected stored procedures programmatically) – super9 Jul 15 '09 at 13:40
0

If your dependencies in SQL Server are accurate, you can use sys.sql_dependencies with appropriate joins.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

I answered a simliar question to this (link below) with a sample of a scipt I use to find text in stored procedures (and functions and views). It requires a bit of work, but might help you here.

[How to find data table column reference in stored procedures

[1]: http://How to find data table column reference in stored procedures

Community
  • 1
  • 1
Philip Kelley
  • 39,426
  • 11
  • 57
  • 92