How can we find a list of Data Extensions that are not being used? That is, not being used by Query Activities, Import, Email Sends, etc.
Short answer: You cannot. Data Extensions don't know about the processes that use them.
Long answer: You could create a web service program that looks at all relevant activities that may target a data extension and compare them to the list of data extensions.
- Retrieve all activities in your account that may deal with data extensions: query activities, imports, user-initiated sends, triggered sends, data filters, AMPScript lookup functions in an email.
- For each activity type extract any relevant data extension information. Targets for imports, queries, sends will have a property returned in web service call. What this property is named will vary based on the activity. AMPScript lookup functions and query sources (select * from DE) are compiled at runtime, so there is no stored value for these DEs. You will have to parse through the email html and query text respectively to find them.
- Obtain a list of all DEs in the account (also done through webservices).
- Find the interestion of DEs from step 2 with step 3 to determine which are used/unused.