3

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?

Improve this question
0

1 Answer 1

Reset to default
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
Improve this answer
1
  • 2
    Thanks, now NUMERO_SALARIE is int not TNumSal Commented Jun 21, 2022 at 8:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.