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?

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
    – XMehdi01
    Commented Jun 21, 2022 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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