Search code examples
sqlsql-server-2008sqldatatypes

SQL Table Data Source

I've been tasked with creating a data dictionary for a DB that has 90 tables. Is there any way to identify by which procedure/task/job a table is populated? I need to source the data in each of the tables and I'm not quite sure how to do this.

Any tips would be greatly appreciated. -T

Solution

  • You can search for which stored procedures use a given table with something like:

    SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE text LIKE '%table_name%'

    You'll then have to manually examine and analyse the code within those SPs to see what it actually does with that table. I expect you'll need to manually eyeball any SQL Agent tasks and SSIS packages you may have as well. This kind of work tends to be hard graft - there aren't many shortcuts to simply grinding over all the code by hand.