SQL Server Database내의 모든 테이블의 'char', 'varchar', 'nchar', 'nvarchar' 값에 들어있는 특정값을 찾아서 출력해 주는 Stored Procedure입니다.

향후 데이터 변경 프로젝트에서 사용하면 유용할 듯 합니다.

--


CREATE PROC [dbo].[SearchAllTables]
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM @Results
END

이번엔 찾은 값을 다른값으로 변경해주는 기능까지 포함된 Stored Procedure입니다.
유용하게 쓰셈!

--
CREATE PROC [dbo].[SearchAndReplace]

(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' =  REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

master 데이터베이스는 sp_MSforeachtdb, sp_MSForeachtable 과 같은 유용한 Stored Procedure가 있습니다.

[sp_MSforeachdb]

sp_MSforeachdb 프로시저는 SQL Server 인스턴스 내에 있는 모든 데이터베이스의 이름을 반환하며 이 이름을 참조하여 지정된 반복문을수행합니다..

아래 스크립트는 인스턴스내의 모든 데이터베이스의 helpfile 정보를 보여줍니다..

[sp_MSforeachtable]

sp_MSforeachtable 프로시저는 현재 데이터베이스 내의모든 테이블 이름을 반환하며 이 테이블이름을 참조하여 지정된 반복문을 수행 한다.

 아래 스크립트는 해당 데이터베이스에 존재하는 모든 테이블의 row count를테이블에 삽입하여 출력한다.

 위의 두 시스템 프로시저를 잘 활용하면 사용자 프로시저 생성시 코드를 간결하고 빠르게 개발 할 수 있을 듯 하다.

[참고자료]

l  http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB#

l  http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

static void UserRolesAndTasks(Args _args)

{

    System.IO.StreamWriter sw;

    InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);

    SecurityTask            securityTask            ;

    SecurityRole            securityRole            ;

    SecurityUserRole        securityUserRole        ;

    SecurityRoleTaskGrant   securityRoleTaskGrant   ;

    UserInfo                userInfo                ;

    str                     outputText              ;

    ;

    perm.assert();

    sw = new System.IO.StreamWriter(@"C:\RolesAndTasks.txt");

    CodeAccessPermission::revertAssert();

    info( "User|Role|Task");

    while select securityUserRole

            join securityRole           where securityRole.RecId                    == securityUserRole.SecurityRole

            join securityRoleTaskGrant  where securityRoleTaskGrant.SecurityRole    == securityRole.RecId

            join securityTask           where securityRoleTaskGrant.SecurityTask    == securityTask.RecId

            join userInfo               where userInfo.id                           == securityUserRole.User

                                           && userInfo.enable                       == NoYes::Yes

    {

        outputText = strFmt("%1 |%2 | %3" , securityUserRole.User, SysLabel::labelId2String2(securityRole.Name), SysLabel::labelId2String(securityTask.Name));

        sw.WriteLine(outputText);

    }

    sw.Flush();

    sw.Close();

    sw.Dispose();

    info( "done");

}

AX 기본 화면 또는 개발된 화면의 사용자 커스터마이제이션을 막는 방법은 아래와 같이 AllowUserSetup 속성을 No로 설정을 하면 가능합니다. 아래 화면은 AllowUserSetup 속성을 No로 변경 후 Personalize 화면에 들어갔을때 보여지는 화면입니다.

Sort를 막는 방법은 해당 Grid Column의 Sort() 메서드를 Override받은 후 해당 Method의 내용을 주석 처리하면 됩니다.

public int sort(SortOrder _sortDirection)
{
    int ret;

//    ret = super(_sortDirection);

    return ret;
}

Updated: October 11, 2008

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

Disabling user customization is the lowest level that can be set for User Personalization of Forms. This level requires that you set the AllowUserSetup property on the form control, and set the AllowAdd property on the form data source field to No.

In this mode, only the size and position of the form can be changed. Users cannot change properties on the individual controls.

Because the position and size of the form is saved (the size is saved if the SaveSize property is set to Yes), there is an entry for this form in the SysLastValue table. No personalization is allowed.

This personalization level is relevant in forms that have a fixed size and whose control positions are important. Examples are wizard forms and forms used for touch-sensitive screens. Use of this personalization level also ensures that users cannot hide information in a particular form.

Note
If you use the AllowUserSetup and AllowAdd properties to prevent user customizations, it affects all users of the form. If customization should be set at run time on a per-user group basis, use the SecurityKey property on the menu item for the form or on individual form controls.

아래코드는 Excel 파일을 열어서 원하는 Cell 위치의 값을 가져와서 뿌려주는 샘플 코드입니다.

static void OpenExcelApp(Args _args)
{
    SysExcelApplication ExcelApp;
    str cellValue;
    ;

    ExcelApp = SysExcelApplication::construct();
    ExcelApp.workbooks().open('C:\\text.xls');

    cellValue = ExcelApp.activeSheet().cells().item(1,1).value().toString();

    info(strFmt("Value of cell A1 = %1", cellValue);
}

static void akaRolesAndTasks(Args _args)
{
    // !# August.28.13.rvanbran:
    SecurityTask            securityTask            ;
    SecurityRole            securityRole            ;
    SecurityUserRole        securityUserRole        ;
    SecurityRoleTaskGrant   securityRoleTaskGrant   ;
    UserInfo                userInfo                ;
    ;

    info( "User|Role|Task");
    while select securityUserRole
            join securityRole           where securityRole.RecId                    == securityUserRole.SecurityRole
            join securityRoleTaskGrant  where securityRoleTaskGrant.SecurityRole    == securityRole.RecId
            join securityTask           where securityRoleTaskGrant.SecurityTask    == securityTask.RecId
            join userInfo               where userInfo.id                           == securityUserRole.User
                                           && userInfo.enable                       == NoYes::Yes
    {
        info( strFmt("%1 |%2 | %3" , securityUserRole.User, SysLabel::labelId2String2(securityRole.Name), SysLabel::labelId2String(securityTask.Name)));
    }
    info( "done");
}

static void createForm(Args _args)
{
    Args args;
    Form form;
    FormRun formRun;
    FormBuildDesign formBuildDesign;
    FormBuildDataSource formBuildDataSource;
    FormBuildGridControl formBuildGridControl;
    FormBuildStringControl formBuildStringControl;
    FormBuildStringControl formBuildStringControl2;
    FormBuildTabControl formBuildTabControl;
    FormBuildTabPageControl formBuildTabPageControl;
    FormBuildTabPageControl formBuildTabPageControl2;
    FormStringControl formStringControl;
    FormGridControl formGridControl;
    DictTable dictTable;
    int idx;
    int idx2;
    int idx3;
    ;

    // Create the form header.
    form = new Form();

// Add a data source to the form. ID 77 refers to the CustTable.
 dictTable = new DictTable(tablenum(CustTable));
 formBuildDataSource = form.addDataSource(dictTable.name());
 formBuildDataSource.table(dictTable.id());

// Create the form design.
 formBuildDesign = form.addDesign("Design");
 formBuildDesign.caption("myForm");

// Add tabbed page controls, a grid control, and string controls.
 formBuildTabControl =
 formBuildDesign.addControl(FormControlType::Tab, "Overview");

formBuildTabPageControl =
 formBuildTabControl.addControl(FormControlType::TabPage, "Overview");
 formBuildTabPageControl.caption("Overview");

formBuildTabPageControl2 =
 formBuildTabControl.addControl(FormControlType::TabPage,"Details");
 formBuildTabPageControl2.caption("Details");

formBuildGridControl =
 formBuildTabPageControl.addControl(FormControlType::Grid,"Table Grid");
 formBuildStringControl =
 formBuildTabPageControl2.addControl(FormControlType::String,"Table String");
 formBuildStringControl2 =
 formBuildTabPageControl2.addControl(FormControlType::String,"Table String");

// Add data fields to controls.
formBuildGridControl.addDataField
(formBuildDataSource.id(),dictTable.fieldName2Id("AccountNum"));
formBuildGridControl.addDataField
(formBuildDataSource.id(),dictTable.fieldName2Id("Phone"));
formBuildGridControl.addDataField
(formBuildDataSource.id(),dictTable.fieldName2Id("Name"));
formBuildGridControl.addDataField
(formBuildDataSource.id(),dictTable.fieldName2Id("Address"));
formBuildStringControl.dataSource(formBuildDataSource.id());
formBuildStringControl.dataField(2);
formBuildStringControl2.dataSource(formBuildDataSource.id());
formBuildStringControl2.dataField(3);

    args = new Args();
    args.object(form);

    // Create the run-time form.
    formRun = classfactory.formRunClass(args);

    formRun.run();
    formRun.detach();

}

public static void attachFile(Common     _recordToAttachTo,
                              Str        _attachFile,
                              DocuTypeId _typeId)
{

    //rvbAttachDoc::attachFile(salesTable, fcFileName, 'File');

    DocuActionArchive   docuActionArchive   ;
    DocuActionFile      docuActionFile      ;
    DocuAction          docuAction          ;
    DocuRef             docuRef             ;
    DocuType            docuType            ;

    docuType = DocuType::find(_typeId) ;
    if(! docuType.RecId)
    {
        throw Error (strFmt(strFmt("Document Type %1 not setup in document handling.", _typeId)));
    }else
    {
        if (docuType.ActionClassId != ClassNum(DocuActionArchive) ||
            docuType.TypeGroup     != DocuTypeGroup::File)
        {
            throw Error(strFmt("Class Description should be 'Attach File' and Group should be 'File' for Document Type %1", _typeId));
        }
    }

    ttsBegin;
    docuRef.ActualCompanyId     =   curExt()                    ;
    docuRef.Name                =   'Automatically Attached'    ;
    docuRef.Notes               =   'Automatically Attached'    ;
    docuRef.RefCompanyId        =   curExt()                    ;
    docuRef.RefRecId            =   _recordToAttachTo.RecId     ;
    docuRef.RefTableId          =   _recordToAttachTo.TableId   ;
    docuRef.Restriction         =   DocuRestriction::Internal   ;
    docuRef.TypeId              =   'File'                      ;
    docuRef.insert()    ;

    docuActionArchive = new docuActionArchive() ;
    docuActionArchive.add(docuRef, _attachFile) ;

    ttsCommit;
}