Information Gathering

• Get All Tables & Fields
  • SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE 
    FROM INFORMATION_SCHEMA.COLUMNS
• Zips Variants wLeading Zeros
  • Replace all “ZipCode” with appropriate field and “tTable” with appropriate table name
  • SELECT CASE
    WHEN LEN(ZipCode) = 4 THEN FORMAT(CAST(ZipCode AS numeric),’00000′)
    WHEN LEN(ZipCode) = 5 THEN FORMAT(CAST(ZipCode AS numeric),’00000′)
    WHEN LEN(ZipCode) = 8 THEN FORMAT(CAST(ZipCode AS numeric),’00000-0000′)
    WHEN LEN(ZipCode) = 9 THEN FORMAT(CAST(ZipCode AS numeric),’00000-0000′)
    ELSE ZipCode
    END
    FROM tTable
• Store Procedure to Search All Tables
  • https://gist.github.com/chrispret/959420810973b60d2c3b7f136f5f1cc0
    SP_SearchTables.sql

System Specific Examples

• Get Labels / API Names – Dynamics CRM
  • https://axforum.info/forums/showthread.php?t=27429
  • http://mscrm-chandan.blogspot.com/2013/04/get-all-attribute-detail-of-entity-in.html
  • Note the line above ORDER BY can be changed to reflect other tables/objects
  • SELECT EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName, AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName
    FROM LocalizedLabelView AS LocalizedLabelView_2
    INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
    RIGHT OUTER JOIN EntityView
    INNER JOIN LocalizedLabelView AS LocalizedLabelView_1
    ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
    ON AttributeView.EntityId = EntityView.EntityId
    WHERE LocalizedLabelView_1.ObjectColumnName = ‘LocalizedName’
    AND LocalizedLabelView_2.ObjectColumnName = ‘DisplayName’
    AND LocalizedLabelView_1.LanguageId = ‘1033’
    AND LocalizedLabelView_2.LanguageId = ‘1033’
    AND EntityView.Name IN (‘Account’)
    /* ^^^ This can be changed to Contact, etc. ^^^ */
    ORDER BY EntityName, AttributeName
• An XML PATH example
  • If you want counts, I’ve only been able to do that via Excel (Remove Duplicates)
  • SELECT DISTINCT tc.ID,
    (SELECT SUBSTRING((SELECT ‘,’ +  Classification
    FROM tblClientClassifications tcc 
    WHERE tc.ID = tcc.ClientID
    ORDER BY Classification FOR XML PATH(”)),2,200000)
    ) AS Classifications,
    (SELECT SUBSTRING((SELECT ‘,’ +  Keyword
    FROM tblClientKeywords tck
    WHERE tc.ID = tck.ClientID
    ORDER BY Keyword FOR XML PATH(”)),2,200000)
    ) AS Keywords,
    (SELECT SUBSTRING((SELECT ‘,’ +  Interest
    FROM tblClientInterests tci
    WHERE tc.ID = tci.ClientID
    ORDER BY Keyword FOR XML PATH(”)),2,200000)
    ) AS Interests
    FROM tblClients tc