I was rummaging through my sent mail boxes and found this message from Aug
2005, back when I worked for cMango. Somebody out there may find it useful.
======================
Form view aliases (e.g. "Help Desk Case", "Help Desk Cases") are
contained in the propShort or propLong column of the field_dispprop
table. Experience shows that if you assign alias names, the property
string is so long that AR System sets propShort to null and moves it to
propLong.
This SQL script finds the 206 property (Singular Alias) and the 207
property (Plural Alias) in propLong and chops out the string values.
The LEFT and RIGHT functions don't work on TEXT fields, so I used
SUBSTRING instead.
On my system, the schemaId for the SPRT:Issue schema is 223, so when I
run the script I get:
Singular Alias Plural Alias
-------------- ------------
Issue Issues
I assume you know how to get the schemaId and vuiId.
Here it is:
---------------------------------------------------------------------
-- GetLongAndShortAliases_1.1.sql
--
-- Author: [EMAIL PROTECTED]
-- Note: Assumes alias will be in propLong, because adding aliases
-- makes the view's property string too large to fit in propShort
--
-- Changed: Uses the number after the CHAR indicator (\4\), which is the
-- actual length of the alias string. That works better than finding the
-- trailing backslash.
declare @schemaId nvarchar(30)
declare @vuiid nvarchar(30)
set @schemaID = '223';
set @vuiid = '10003000';
select
-- Column 1: 206 is the property for Singular Alias. 4 means CHAR
replace(
substring(
substring(propLong,
(patindex('%\206\4\%', propLong) + 9),
(datalength(propLong) - (patindex('%206\4\%', propLong)))),
0,
cast
(substring(
substring(propLong,
(patindex('%\206\4\%', propLong) + 7),
(datalength(propLong) - (patindex('%\206\4\%', propLong)))),
0,
patindex('%\%',
substring(propLong,
(patindex('%\206\4\%', propLong) + 7),
(datalength(propLong) - (patindex('%\206\4\%', propLong))))))
as int) + 2),
'\', '')
SingularAlias,
-- Column 2: 207 is the property for Plural Alias. 4 means CHAR
replace(
substring(
substring(propLong,
(patindex('%\207\4\%', propLong) + 9),
(datalength(propLong) - (patindex('%\207\4\%', propLong)))),
0,
cast
(substring(
substring(propLong,
(patindex('%\207\4\%', propLong) + 7),
(datalength(propLong) - (patindex('%\207\4\%', propLong)))),
0,
patindex('%\%',
substring(propLong,
(patindex('%\207\4\%', propLong) + 7),
(datalength(propLong) - (patindex('%\207\4\%', propLong))))))
as int) + 2),
'\','')
PluralAlias
from
arsystem.dbo.field_dispprop
where
fieldId is null
and
propLong is not null
and
propLong like '%\206\4\%'
and
schemaId = @schemaId
and
vuiId = @vuiId
--
--
-- Useful for testing.... Try 'HPD:%' and 'SHARE:%'.
--
-- schemaId in
--(select
-- schemaId
-- from
-- arsystem.dbo.arschema
-- where
-- name like 'SPRT%')
---------------------------------------------------------------------
End of Script
I tested this in Query Analyzer, but it should work in isql, too.
======================
Tim Widowfield
[EMAIL PROTECTED]
v: 937-878-9045
f: 937-878-9055
m: 937-369-7012
http://www.widowfield.com
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
