I love the data profiling task that was added to SSIS in SQL Server 2008. It allows you to get a profile of the data in a table – this includes things like what are potential candidate keys, what length are the data values in each column, what percentage of rows in a table are null in each column, what is the distribution of values in the data, etc. etc.
If you haven't used it, I'd suggest taking a look at it. It's a real hidden gem in the product.
However, I think they missed the #1 place that this sort of functionality would be useful. Imagine if you could just right-click a table in Object Browser in SQL Server Management Studio and say "Profile this table…". That would be so insanely cool.
If you think so too, you know you want to vote for this at: https://connect.microsoft.com/SQLServer/feedback/details/610847/data-profiling-needs-to-be-also-in-ssms-object-explorer