How to decrypt the name of columns from view?

All we need is an easy explanation of the problem, so here it is.

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 [email protected]
AND a.type IN ('U','V')

How to decrypt the name of columns from view?

Is there any way to decrypt them?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 [email protected]
AND a.type IN ('U','V')
AND c.system_type_id = c.user_type_id

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply