Last active
February 21, 2024 12:41
-
-
Save camrobert/3f0cd399ed0eb43b5638c198e892b3f3 to your computer and use it in GitHub Desktop.
SSJS script to locate every Field in every Data Extension with a given name
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<script runat="server"> | |
/*Code provided as is without warranty - make sure you understand what this code does before using it - use at your own risk*/ | |
Platform.Load("Core","1"); | |
try{ | |
var prox = new Script.Util.WSProxy(); | |
var cols = ["Name","CustomerKey","FieldType","IsPrimaryKey", "MaxLength"]; | |
var filter = { | |
Property: "Name", | |
SimpleOperator: "like", | |
Value: "email" | |
}; | |
var res = prox.retrieve("DataExtensionField", cols, filter); | |
var arraydes = {}; | |
var fields = []; | |
for (i = 0; i < res.Results.length; i++) { | |
var dekey = res.Results[i].CustomerKey.match(/\[.*\]\.{1}/gi); | |
var stringkey = dekey[0].substring(1,dekey[0].length - 2); | |
arraydes[stringkey] = 1; | |
res.Results[i].DEKey = stringkey; | |
fields.push(res.Results[i]); | |
} | |
var dedetails = {}; | |
for (var key in arraydes) { | |
var cols = ["Name","CustomerKey","CategoryID"]; | |
var filter = { | |
Property: "CustomerKey", | |
SimpleOperator: "equals", | |
Value: key | |
}; | |
var resb = prox.retrieve("DataExtension", cols, filter); | |
dedetails[key] = {Name: resb.Results[0].Name, CategoryID: resb.Results[0].CategoryID}; | |
}; | |
var finaldata = []; | |
for (a = 0; a < fields.length; a++) { | |
finaldata.push({CustomerKey: fields[a].CustomerKey, Name: fields[a].Name, FieldType: fields[a].FieldType, MaxLength: fields[a].MaxLength, IsPrimaryKey: fields[a].IsPrimaryKey, DataExtensionName: dedetails[fields[a].DEKey].Name, DataExtensionCategoryID: dedetails[fields[a].DEKey].CategoryID}); | |
} | |
Write("<table border=1>"); | |
Write("<tr><th>CustomerKey</th><th>Name</th><th>FieldType</th><th>MaxLength</th><th>DataExtension</th><th>CategoryID</th></tr>"); | |
for (b = 0; b < finaldata.length; b++) { | |
Write("<tr><td>"+finaldata[b].CustomerKey+"</td><td>"+finaldata[b].Name+"</td><td>"+finaldata[b].FieldType+"</td><td>"+finaldata[b].MaxLength+"</td><td>"+finaldata[b].DataExtensionName+"</td><td>"+finaldata[b].DataExtensionCategoryID+"</td></tr>"); | |
} | |
Write("</table>"); | |
/* | |
var FieldsDE = DataExtension.Init("Cameron Robert"); | |
FieldsDE.Rows.Add(finaldata); | |
*/ | |
} | |
catch(error) { | |
Write('Message: '+ error); | |
} | |
</script> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment