4

I had to deal with a third-party database, I am trying to get all columns used in a view but I got them encrypted
e.g: instead of getting int for NUMERO_SALARIE I got TNumSal why?

DECLARE @TableViewName NVARCHAR(128)
SET @TableViewName=N'DP_SALARIE'
SELECT b.name AS ColumnName, c.name AS DataType, b.max_length AS Length
FROM sys.all_objects a
INNER JOIN sys.all_columns b
ON a.object_id=b.object_id
INNER JOIN sys.types c
ON b.user_type_id=c.user_type_id
WHERE a.Name=@TableViewName
AND a.type IN ('U','V')

enter image description here

Is there any way to decrypt them?

New contributor
TAHER El Mehdi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

1 Answer 1

7

Looks like a user defined data type to me.

By looking into sys.types and joining the columns system_type_id and further defining that system_type_id should be equal to user_type_id the query should give you the unterlying system type name.

So your code should look like this

DECLARE @TableViewName NVARCHAR(128)
SET @TableViewName=N'DP_SALARIE'
SELECT b.name AS ColumnName, c.name AS DataType, b.max_length AS Length
FROM sys.all_objects a
INNER JOIN sys.all_columns b
ON a.object_id=b.object_id
INNER JOIN sys.types c
ON b.system_type_id=c.system_type_id
WHERE a.Name=@TableViewName
AND a.type IN ('U','V')
AND c.system_type_id = c.user_type_id
1
  • 2
    Thanks, now NUMERO_SALARIE is int not TNumSal Jun 21 at 8:56

Your Answer

TAHER El Mehdi is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.