SQL Server Column list customization

 

In order to customize the "fields list" (or “Columns list”) displayed by the SQL Server interface, it is possible to modify the DDISQL.INI file and define the SQL Query to be used to get the fieldlist. The query must return 6 fields: 5 mandatory field + 1 optional field:

  1. Column name.

  2. Column type.

  3. Column length.

  4. Column number of decimal.

  5. Column identifier.

  6. Column description.

 

That can be done for the SQL Server interface in general, by adding a « COLUMNSQUERY » entry in the [General] section, as in the following example:

 

[GENERAL]

COLUMNSQUERY=SELECT syscolumns.name,syscolumns."type",syscolumns.length,syscolumns.scale,syscolumns.colid,sysproperties."value" FROM ClicknDECiDEDemo.dbo.syscolumns syscolumns,ClicknDECiDEDemo.dbo.sysproperties sysproperties WHERE (syscolumns.id *= sysproperties.id AND syscolumns.colid *= sysproperties.smallid) AND (sysproperties."type"=4 AND sysproperties.name='MS_Description' AND syscolumns.id=object_id('#Creator#.#NameMask#'))

 

That is possible also to define only a specific configuration, by adding a « COLUMNSQUERY » entry in the section corresponding to the SQL Server configuration, as in the following example :

 

[ClicknDECiDE Demo]

COLUMNSQUERY=SELECT syscolumns.name,syscolumns."type",syscolumns.length,syscolumns.scale,syscolumns.colid,sysproperties."value" FROM ClicknDECiDEDemo.dbo.syscolumns syscolumns,ClicknDECiDEDemo.dbo.sysproperties sysproperties WHERE (syscolumns.id *= sysproperties.id AND syscolumns.colid *= sysproperties.smallid) AND (sysproperties."type"=4 AND sysproperties.name='MS_Description' AND syscolumns.id=object_id('#Creator#.#NameMask#'))

 

Caution : you must keep criteria with parameter #NameMask# and #Creator# because they are used to identify the object from which columns are retrieved.

 

See also : SQL Server Tables/Views List Customization