February 10, 2010 at 1:21 am
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
February 10, 2010 at 1:25 pm
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
February 10, 2010 at 1:56 pm
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) ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply