February 4, 2009 at 9:57 am
I inherited a large database that is getting populated through an SSIS package. In an effort to reduce the amount of data that has to be loaded by a nightly process, I'm trying to figure out if there are any unused columns in the database. Does anybody know if there's a way of doing it (examining application code is out of a question, because there're too many applications using the database ). any ideas/suggestions are appreciated.
February 4, 2009 at 1:32 pm
Depends on the definition of "not used." If you mean empty columns then you could look at using some queires based on the system tables like sys.columns. You can use these to build a dynamic sql statement that would iterate through the columns of all the tables and return ones where all the rows are null (count of all = count where null).
If you mean columns that contain data but are not used (no longer inserted, updated, deleted) then this becomes more difficult. I think you could use a log tool, but I don't know of any way off hand.
February 5, 2009 at 1:04 pm
I wish it was as easy as identifying empty columns:) No, it's basically one big read-only database that is used by different applications. Data gets populated every night, and I'm sure that there columns that contain data which is not being used by any application. I need to identify those columns and clean up the database. Thanks for the reply, though!
February 5, 2009 at 2:23 pm
If you are looking for columns that are not used by your application - and assuming that your application uses stored procedures/functions/views to access data - you can parse through the system tables that contain the code for the procedures/functions/views and check if columns are being used...
There are also tools available that document your database and list out such information (I think RedGate and Apex have such tools)...
Alternatively you could look at some codeplex projects around similar areas...
February 9, 2009 at 11:09 am
winash (2/5/2009)
If you are looking for columns that are not used by your application - and assuming that your application uses stored procedures/functions/views to access data - you can parse through the system tables that contain the code for the procedures/functions/views and check if columns are being used...There are also tools available that document your database and list out such information (I think RedGate and Apex have such tools)...
Alternatively you could look at some codeplex projects around similar areas...
Well, it's a number of applications, and no, they don't use procedures/functions/views. I'm not sure how much thought and/or planning went into developing those apps, but whatever it was, it surely did not produce quality code. I think between dozens of apps, there's one function, no stored procedures and no views. No documentation, either. Anybody ever had to deal with things like that? The code I see on a regular basis would probably allow thedailywtf have new material for a year with no other submissions, lol.
Anyway, thanks for the links, and I'll check to see what RedGate and Apex have.
February 9, 2009 at 2:52 pm
That is a task for Client-Side "IF" you want to engage on such venture.
There are *many* cases where developers use select * from just to read a couple of colums. There is nothing th server could know about what are the clients doing with the data.
This looks like the project-from-hell!
* Noel
February 10, 2009 at 12:27 pm
Amazingly enough, I don't see too many of "select all" in applications.
I kind of have a strange desire to take on that project:) maybe, because I don't like useless things, and unused columns would definitely fall into that category. I've been thinking about putting a utility together that will automate that process... when I have time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply