Find unused fields in stored procedures

  • Hi there,

    I have a big legacy database and i need to reduce the size. My company database has more than 400 stored procedures that produce necessary reports for management people.

    To reduce the database size, i need to find any unused fields' tables in all 400 stored procedures that are not referrenced. Then delete these unused fields for the reports.

    Can anyone guide me the ways to get this information?

    Thank you very much.

    Regards,

    Kai

  • Are you saying that users only interact with the data via stored procedures? There is no ad-hoc sql being run from any application, reporting tool, etc.? I see what you're attempting to do as dangerous.

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • How would you deal with a scenario where you'd have an (badly written) insert statement like:

    INSERT INTO TargetTable

    SELECT col1, col2, col3

    FROM SourceTable

    You won't find any column of your TargetTable in that code (I have to admit that this would be a bad coding standard but such code might be there). Same would apply to usage of dynamic SQL (Edit: in terms of not being able to identify the columns being involved, not related to bad coding standard in general 😉 ).

    Another example would be a column with a default value based on some conditions (CASE WHEN THEN END) and a query like (another example of bad coding standard but still possible and valid):

    SELECT TOP 1 col1

    FROM SourceTable

    ORDER BY 6

    If you delete one column before the 6th column (sorted by colid in sys.syscolumns) you might get totally different results somewhere in your application.

    The bad part is: you wouldn't even notice the effect... Until one day... :pinch:

    To summarize it:

    To delete columns for the sake of size reduction is one of the things I wouldn't do. (Almost) never ever.

    Instead of this approach I would look into archiving, column size tuning (maybe all columns are CHAR(255)?), tuned backup strategy (if "database size" would imply size of transaction log file) ...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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