Post reply

Script for column usage

  • November 25, 2002 at 3:28 pm

    #105922

    Does anyone have a script that will tell you how a column is used in an application? What I'm looking for is a script that, given a certain Database tablename, will tell me how many places reference each column, and HOW each column is referenced (in a where clause, in a select statement, etc.). Is there a way to get this information without having to go through the entire application code?

    The reason I'm looking for this is that I have an application that is not normalized and I suspect is very badly indexed....such a tool will greatly help me in proposing some fixes for this situation.

    Thanks,

    SB

  • November 25, 2002 at 3:45 pm

    #442360

    sysdepends will give you the information, depid points to the object id and dep number points to the colid.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 25, 2002 at 7:02 pm

    #442378

    The only thing you may have trouble with is sysdepends will not pick up dynamic SQL and it will not tell you how referenced. There is a script on this site that will parse Procs and tell you what other references there are. However, I don't remember the name and you might have to search the forums to find it. Was about a month ago when the author posted about that I found out about it. Anything else you may have to write a Proc to get those details yourself, or if I find time after this week I may play with the idea if noone else does.

  • November 26, 2002 at 3:12 am

    #442401

    You can find out if the column is involved in a read or write action or a select * action from the last columns

    selall bit On, if the object is used in a SELECT * statement.

    resultobj bit On, if the object is being updated.

    readobj bit On, if the object is being read.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 26, 2002 at 8:57 am

    #442432

    I looked at sysdepends, but it doesn't seem to show reliable information....on one of my most active tables, sysdepends shows that it is not read, updated, or used in a select * statement....and this is grossly inaccurate.

  • November 26, 2002 at 9:42 am

    #442441

    Hi simonsabin,

    Really stumped by what you talked about

    selall bit On, if the object is used in a SELECT * statement.

    resultobj bit On, if the object is being updated.

    readobj bit On, if the object is being read.

    I am having no clue about all this can you explain it a bit ar put a link to get details into it

    Thanks..Prakash

    Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • November 26, 2002 at 6:01 pm

    #442495

    If the "application" you are refering to includes database triggers, procedures, etc, then you can search the syscomments table.

    Better still, dump all triggers and procedures to a text file, and then use a text editor to do your searching. (use EM to dump the objects to a file)

    If the application is compiled, and you do not have access to the code, then set up a trace on the database using profiler, and push the profile dump to a table. Then search the table for the info. It will have all the selects, updates, etc - with the assumption that all the apps sections were used.

    As for a tool to actually package this, doubt one exists.

    Andrew


    What's the business problem you're trying to solve?

  • December 3, 2002 at 7:41 pm

    #442977

    quote:

    Does anyone have a script that will tell you how a column is used in an application?

    Not a script per se but an idea: I use EM and "generate script" to script the entire data base to a text file. You can search this single file to find references to your column names.

    2nd idea: Add "with schema binding" to all your views, functions and procs and recreate them. Then sysdepends will be accurate. Makes your coders very unhappy but you eliminate 99% of your overlays this way.

    3rd idea: use the information_schema.views and information_schema.tables views to list all the occurences of your column.

  • December 3, 2002 at 7:50 pm

    #442981

    Exporting to text and then building a short script using regular expressions is perfect for something like this. Pick your favorite scripting language. Perl has excellent regexp support. VBScript does as well using the RegExp object:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/reconIntroductionToRegularExpressions.asp

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply