Get all lists and sites using a given site column in sharepoint 2007 using powershell

Question

In other words, is there a SPField.ListsFieldUsedIn() equivalent in SharePoint 2007? Ref: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfield.listsfieldusedin.aspx

Answer 1

Here's how it's done in SharePoint 2010:

public ICollection<SPFieldTemplateUsage> ListsFieldUsedIn()
{
    List<SPFieldTemplateUsage> list = new List<SPFieldTemplateUsage>();
    using (SPSqlCommand command = new SPSqlCommand())
    {
        using (SqlDataReader reader = this.GetSqlDataForPushdown(command))
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    SPFieldTemplateUsage item = new SPFieldTemplateUsage();
                    item.WebID = (Guid) reader[0];
                    item.ListID = (Guid) reader[1];
                    list.Add(item);
                }
                reader.Close();
            }
            return list;
        }
    }
}

private SqlDataReader GetSqlDataForPushdown(SPSqlCommand cmd)
{
    SPSite site = this.Fields.Web.Site;
    SqlSession sqlSession = site.SqlSession;
    cmd.SetStoredProcedure("proc_ListsUsingFieldTemplate");
    cmd.Parameters.AddWithValue("@SiteId", site.ID);
    cmd.Parameters.AddWithValue("@FieldId", this.Id.ToByteArray());
    cmd.Parameters.AddWithValue("@BaseTypes", SPBaseFieldId.BaseTypesFromFieldId(this.Id));
    return sqlSession.ExecuteReader((SqlCommand) cmd);
}

I don't have a SharePoint 2007 VM handy to see if the stored proc exists.

The alternative is to loop through all the lists in the site collection and check the Fields collection. It won't be fast but it'll do the trick.