RSS

Monthly Archives: August 2014

How to Find Large Tables in AX Database through SQL Server

If your database appears to be larger than you expect it to be, this article will show you how to display a list of your MS SQL Database tables in size order, and list the associate size of the table.

  1. Open and log in to Microsoft SQL Server Management Studio
  2. Copy the following script into the New Query page replacing [DatabaseName] with the name of your database and Click the Execute button
USE [DatabaseName]
GO
CREATE TABLE tempLargeTable(
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT tempLargeTable
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM tempLargeTable a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE tempLargeTable
4.tempLargeTable
🙂 Keep Daxing

 

Advertisements
 

Retrieving a list of System databases from SQL Server

I need to get a list of System databases from SQL Server.

A bit of googling provided enabled me to develop this SQL statement:

owner_sid is equal to 0x01 just for system databases. So you can use it to recognise if the database is a system DB or not.

select * from sys.databases
where owner_sid != 0x01

This works, it gives me a list of system databases.

🙂

 
Leave a comment

Posted by on August 7, 2014 in SQL SERVER

 

Retrieving a list of AX databases from SQL Server

i was trying to get list of AX databases on SQL Server.Microsoft Dynamics AX have capability to get list of database exists in SQL Server.it contains class SysDatabaseList.

SysDatabaseList sysDatabaseList;
container conDatabaseList;
Counter conlength;
Counter loopCounter;

sysDatabaseList = new SysDatabaseList();

conDatabaseList =sysDatabaseList.databaseNames();

conlength = conLen(conDatabaseList);

for(loopCounter = 1;loopCounter <= conlength;loopCounter++)
{
info(strFmt(“%1”,conPeek(conDatabaseList,loopCounter)));
}

🙂