SQL: get number of records for all tables in Database

 Below you may find easist way to get number of records per table in your database. Sometimes it's extremely needed.

Get size of all tables in current database SQL

 I have a task - to perform data upgrade from AX2012 to D365FO and I was wondering how big is each of the customer's table. I'm aware of Object explorer details(F7) in SQL Management studio but it works not fast as I'd like to. So I googled and found a bunch of different scripts that shows different information regarding your database.

One of that scripts I used and the result was pretty good. So I recommend you to use that script if you'd have the same task. The code is below:

SELECT a2.name AS TableName, a1.rows as [RowCount], --(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB, --a1.data * 8 AS DataSize_KB, --(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB, --(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB, CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB, CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB, CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB, CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB, --'| |' Separator_MB_GB, CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB, CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB, CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB, CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps --===Remove the following comment for SQL Server 2014+ --WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1) GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' --AND a2.name = 'MyTable' --Filter for specific table --ORDER BY a3.name, a2.name ORDER BY ReservedSize_MB DESC




Just do the Copy-> Paste into your management studio and run in in front of the desirable database.

How to filter record by dimension values in D365

Hi all,

It's been a long time I wrote the post in my blog. But today I will fresh it by writing the new one.

I got a task - to filter the query on worker's default dimension fields. This was quite challenging for me.
First I did it via DimensionAttributeValueSetStorage class.  It didn't feet the requirement because the functionality has to have a possibility to filter records via any of the dimensions (Division, Location, CostCenter, Department, etc).
Next, I found an article about DimensionsProvider class which has some sort of abilities that I was needed. And I used it.

It worked as expected and as required!

So, below I provided a piece of code which you can interpret for your requirements, but I believe the general concept will be clear:

private void filterResourcesByDimensions(Query _q)
    {
        Counter                             i;
        DimensionAttribute                  dimensionAttribute;
        str                                 dimValue;
        container                           workerDefaultDimension, workerDefaultDimensionVal;
        QueryBuildDataSource                qbdsResource;
        
        DimensionProvider                   dimProvider = new DimensionProvider();

        workerDefaultDimension = ['Division', 'Location', 'Region', 'ServiceLine', 'SubService'];

        workerDefaultDimensionVal = [_context.division(),
                                    _context.location(),
                                    _context.region(),
                                    _context.serviceLine(),
                                    _context.subService()
                                    ]; //Dimensions values (any)

        qbdsResource = _q.dataSourceTable(tableNum(ResCompanyResourceView));
   
        for (i = 1; i <= conLen(workerDefaultDimension); i++)
        {
            dimensionAttribute = dimensionAttribute::findByName(conPeek(workerDefaultDimension,i));
       
            if (dimensionAttribute.RecId == 0 && conpeek(workerDefaultDimensionVal, i) == '')
            {
                continue;
            }
       
            dimValue = conPeek(workerDefaultDimensionVal,i);
       
            if (dimValue != "")
            {
                dimProvider.addAttributeRangeToQuery(_q, qbdsResource.name(), identifierStr(DefaultDimension), DimensionComponent::DimensionAttribute, dimValue, dimensionAttribute.Name);
            }
        }
    }

Feel free to contact if you still have any questions! I'm

How to decode XML string Ax 2012

Hi again! I haven't written posts recently. But today I had a task, which was unusual for me - decode XML string to readable view to parse particular value. Or decoding the XML string.
So, I got a xml node like this

<column columnName="OrderLineFieldValues">&lt;OrderLineFieldValueCollection&gt;&lt;OrderLineFieldValue&gt;&lt;OrderLineFieldSystemName&gt;Backordered&lt;/OrderLineFieldSystemName&gt;&lt;Value&gt;4&lt;/Value&gt;&lt;/OrderLineFieldValue&gt;&lt;OrderLineFieldValue&gt;&lt;OrderLineFieldSystemName&gt;CutInspect&lt;/OrderLineFieldSystemName&gt;&lt;Value&gt;&lt;/Value&gt;&lt;/OrderLineFieldValue&gt;&lt;OrderLineFieldValue&gt;&lt;OrderLineFieldSystemName&gt;Mileage&lt;/OrderLineFieldSystemName&gt;&lt;Value&gt;&lt;/Value&gt;&lt;/OrderLineFieldValue&gt;&lt;OrderLineFieldValue&gt;&lt;OrderLineFieldSystemName&gt;Notes&lt;/OrderLineFieldSystemName&gt;&lt;Value&gt;&lt;/Value&gt;&lt;/OrderLineFieldValue&gt;&lt;OrderLineFieldValue&gt;&lt;OrderLineFieldSystemName&gt;SerialNumber&lt;/OrderLineFieldSystemName&gt;&lt;Value&gt;&lt;/Value&gt;&lt;/OrderLineFieldValue&gt;&lt;/OrderLineFieldValueCollection&gt;</column>';

So, looks a little bit confused, doesn't it?

Well, from this mess I'd need to get particular values. How can I do this?  First of all, I started searching some sort of open API to do this but faced with lack of it. But after 1 hour or so I finally found the desired solution. For me it looks like this:

static void Job3(Args _args)
{
    str output;
    str input = @'my encoded XML string';
 
    output = System.Web.HttpUtility::HtmlDecode(input);
 
    info(output);
}

All that I need is in one HtmlDecode method! It's nice. Now I can continue working on the parser which could help me to get the required value from the XML node.

Hope, this piece of code would be useful for someone other as it was for me!

Happy DAXing! 

Get LogisticsLocation record from customer

Hi! 

Today I've had a requirement to show some custom fields from the LogisticsLocation record from the customer table. I wrote a display method for a particular field, but this approach can be used in different ways. It depends on your requirements.
So, below the code:

public display IsReservedAccount displayIsReservedAccount(CustTable _customer)
{
    DirParty                        dirParty;
    LogisticsLocation               location;
    LogisticsPostalAddress          address;
    dirParty = DirParty::constructFromCommon(_customer);
    address = dirParty.getPrimaryPostalAddressLocation().getPostalAddress();
    location = LogisticsLocation::find(address.Location);
    return location.IsReservedAccount;
}

Hope it will be useful for someone as for myself. 

Check how many rows were selected on the form grid

Hi!

This approach is common for either DAX 2012 or D365FO.

If you need to check how many rows were selected by the user, you just need to get the formDataSource value before, for example

FormDatasource fds = _args.record().datasource();

And then write next code:

if (fds.recordsMarked().lastIndex() > 1)
{
    info("more than 1 record was selected");
}  

Hope, this information will be useful for someone as for me.

Disable auto complete of form control

Hi!
Today I've faced with an issue which causes some discontent of my client. He wanted to scan barcode via barcode field but without autocomplete in this field. What I mean: when you input something into the field (the field type doesn't matter), the system "remembers" this value and if you try to enter it one more time when you started to enter, the system will complete it for you. For example:
"111". When you started entering 111 second time the system will finish it for you.
And I'd need to disable this feature as my client wants. But I didn't find any property which can help me with this.
After a while, my friend suggested I use the method delAutoCompleteString() on the control.

Finally, the code for disabling autocompletion for control would look like this:

public boolean modified()
{

boolean ret;
;
ret = super();
element.delAutoCompleteString(this);
return ret;

          } 

I hope, this information will be helpful for someone else :)

Happy DAX-ing!