Microsoft SQL Server T-SQL : Delete AX 2009 Company Query

Microsoft Dynamics AX 2009 Company can also be deleted from the database using a SQL T-SQL Query, if you ever encounter issues while deleting a company from Microsoft Dynamics AX 2009 Administration settings than you may use the following T-SQL Query code for company deletion.

Note: Please make sure to first take the Microsoft Dynamics AX 2009 full database backup and use the query on a test environment.

[T-SQL Code Snippet]

--Replace DynamicsAX2009 with your Dynamics AX 2009 Database Name

USE [DynamicsAX2009]

--Replace CEU with Company Id that is required to be deleted

DELETE FROM DataArea WHERE DataArea.ID = 'CEU'

--Replace CEU with Company Id that is required to be deleted

DELETE FROM CompanyDomainList WHERE CompanyDomainList.CompanyID = 'CEU'

--Replace CEU with Company Id that is required to be deleted

EXEC sp_MSforeachtable 'delete from ? where ?.DataAreaID = "CEU"'

Microsoft SQL Server T-SQL: String Left/Right Padding

Microsoft SQL Server 2005 T-SQL does not have left/right string padding function, to use we need to write our own Microsoft SQL  function. The following code example shows how to create a function for Left string padding

[T-SQL Code Snippet]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Go

CREATE FUNCTION [dbo].[lPad]

(

--Input String to Pad

@in_str AS VARCHAR(MAX)

--Character used for padding an input string

,@in_padd_char AS VARCHAR(1)

--Number of Chracter in resulting string, equals to the number of chracters in input string plus additonal padding chracters

,@in_total_width AS INT

)

RETURNS VARCHAR(MAX) WITH EXECUTE AS CALLER

AS

BEGIN

DECLARE @out_padded_str AS VARCHAR(MAX)

SET @out_padded_str = ISNULL( REPLICATE(@in_padd_char, @in_total_width - len(@in_str) ), '') + @in_str

RETURN @out_padded_str

END

Usage Example 

SELECT DBO.lPad ('12345', '0', 10)

----------------------------------------------------------

Result: 0000012345

Microsoft SQL Server T-SQL: Find Database Objects Dependent on a Particular Object

The following SQL query example finds the database objects dependent on a database object. Use the query on database where the object belongs.

 [T-SQL Code Snippet]

SELECT  DISTINCT SysObjects.[name] [Procedure Name] FROM SysObjects JOIN

(

SysObjects [SysObj]

JOIN SysDepends

ON  [SysObj].id = SysDepends.depid

--wod_blogs can be the name of table, stored procedure, views or other database objects

AND [SysObj].[Name] = 'wod_blogs'

)

ON SysDepends.id = SysObjects.id AND SysObjects.xtype = 'P'

Particualr conditions can be added to find particual dependent objects type, for example:-

 To find dependent Views use  AND SysObjects.xtype = 'V'

 To find dependent Constaints use AND SysObjects.xtype = 'C'