Dependencies seperate the column base_object_name in sys.synonyms

Question

Dependencies seperate the column base_object_name in sys.synonyms

GG_BI_GG

SSCrazy

Points: 2627
More actions
November 9, 2021 at 10:24 am

#3949394
I would like to know the base object that is used in the synonyms, so there is a column called base_object_name:
- The table is use is (
```
SELECT base_object_name
FROM sys.synonyms
```
)
Is there someone who is able to sepereate the value in the columns?
I would like the value [db].[dbo].
in seperate column.
Need some help?
- This topic was modified 3 years ago by GG_BI_GG.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246957 More actions · Answer 1

Easy. Use PARSENAME():

SELECT base_object_name
      ,CONCAT(QUOTENAME(PARSENAME(base_object_name, 3)), '.', QUOTENAME(PARSENAME(base_object_name, 2)))
FROM sys.synonyms;

This reply was modified 3 years ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 2

That not really the solution, but I have something but not complete:

 SELECT DISTINCT [name] AS SYN_NAME,
  LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
    CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1) AS DBNM,
  (LEFT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
    CHARINDEX('.', REPLACE(REPLACE(base_object_name ,'[',''),']',''))-1)) AS DBID,
  RIGHT (REPLACE(REPLACE(base_object_name ,'[',''),']',''),
    CHARINDEX('.', reverse(REPLACE(REPLACE(base_object_name ,'[',''),']','')))-1) AS objectname
FROM sys.synonyms

The only think that needs CHANGE is the middle part DBID must be my SCHEMANAME 🙂

Phil Parkin SSC Guru Points: 246957 More actions · Answer 3

Please explain why it's "not really the solution".

2021-11-09_13-01-18

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 4

O srry, because I would like erverthing in seperate column:

dbname | schema | Table/view |

db dbo xxxx

🙂

Phil Parkin SSC Guru Points: 246957 More actions · Answer 5

SELECT base_object_name
      ,DBName = PARSENAME(base_object_name, 3)
      ,SchemaName = PARSENAME(base_object_name, 2)
      ,ObjectName = PARSENAME(base_object_name, 1)
FROM sys.synonyms;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin