What would be the query (Script) to drop all synonyms in corresponding databases in entire instance as a bulk?

michalek19
michalek19 used Ask the Experts™
on
What would be the query (Script) to drop all synonyms in corresponding databases in entire instance as a bulk?

The report ( csv file) looks like this. I want to drop them all by base_object_name


DB_name               Database_id     object_id                    base_object_name

[ABSCorxxxOM]      39                         NULL                              [fwbsqerewe2].[ABSCorxxxOM].[dbo].[DealMaster]
[ABSCoyyyyOM]      35                         NULL                              [fwbsqerewe2].[ABSCoyyyyOM].[dbo].[TrancheMaster]
[A.ROM]                    40                         NULL                              [fwbsqerewe2].[A.ROM].[dbo].[Static_UserDirectory]
[As3.Ms.Import]      92                         NULL                              [sg-nt].[As3.Ms.Import].[dbo].[Static_InterfaceTransformSequence]

and ........
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
IF OBJECT_ID('tempdb.dbo.synonyms') IS NULL
    CREATE TABLE tempdb.dbo.synonyms (
        db_name varchar(100) NOT NULL,
        database_id int NOT NULL,
        object_id int NULL,
        base_object_name varchar(1000) NULL,
        synonym_name varchar(100) NULL
        )
TRUNCATE TABLE tempdb.dbo.synonyms;

EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN;
use [?]
DECLARE @sql_to_drop_synonyms nvarchar(max)
INSERT INTO tempdb.dbo.synonyms
select ''[?]''
  , ISNULL(db_id(parsename(base_object_name, 3)), DB_ID()) as dbid
  , case when parsename(base_object_name, 3) is null
         then object_id(base_object_name)
         else null end as objid
  , base_object_name
  , parsename(base_object_name, 1) as synonym_name
from sys.synonyms
--where name not like ''?%''
--/*
IF @@ROWCOUNT > 0
BEGIN
    SELECT @sql_to_drop_synonyms = STUFF(CAST((
        SELECT N''; DROP SYNONYM ['' + CAST(name AS nvarchar(max))
        from sys.synonyms
        --where name not like ''?%''
        order by name
        for xml path(''''), type
        ) AS nvarchar(max)), 1, 2, '''')
    SELECT @sql_to_drop_synonyms AS [--sql_to_drop_synonyms]
    --EXEC(@sql_to_drop_synonyms) --uncomment when ready to actually DROP SYNONYMs
END /*IF*/
--*/
'

SELECT *
FROM tempdb.dbo.synonyms
ORDER BY db_name

Author

Commented:
I am getting this

Msg 213, Level 16, State 1, Line 6
Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 1, Line 6
Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 1, Line 6
Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 1, Line 6
Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 1, Line 6
Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 1, Line 6
Column name or number of supplied values does not match table definition.

(0 row(s) affected)

Author

Commented:
I still see synonyms. They were not dropped
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Yes, the EXEC was commented out "to be safe"; just uncomment when you are sure you want it to run.

Be sure to drop the tempdb work table before the first time you run this code, since the old definition did not include the synonym name.

Author

Commented:
In which part of the script i have to remove "--" ? can you please send me script ready to remove synonyms
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
IF OBJECT_ID('tempdb.dbo.synonyms') IS NULL
    CREATE TABLE tempdb.dbo.synonyms (
        db_name varchar(100) NOT NULL,
        database_id int NOT NULL,
        object_id int NULL,
        base_object_name varchar(1000) NULL,
        synonym_name varchar(100) NULL
        )
TRUNCATE TABLE tempdb.dbo.synonyms;

EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN;
use [?]
DECLARE @sql_to_drop_synonyms nvarchar(max)
INSERT INTO tempdb.dbo.synonyms
select ''[?]''
  , ISNULL(db_id(parsename(base_object_name, 3)), DB_ID()) as dbid
  , case when parsename(base_object_name, 3) is null
         then object_id(base_object_name)
         else null end as objid
  , base_object_name
  , parsename(base_object_name, 1) as synonym_name
from sys.synonyms
--where name not like ''?%''
--/*
IF @@ROWCOUNT > 0
BEGIN
    SELECT @sql_to_drop_synonyms = STUFF(CAST((
        SELECT N''; DROP SYNONYM ['' + CAST(name AS nvarchar(max))
        from sys.synonyms
        --where name not like ''?%''
        order by name
        for xml path(''''), type
        ) AS nvarchar(max)), 1, 2, '''')
    SELECT @sql_to_drop_synonyms AS [--sql_to_drop_synonyms]
    EXEC(@sql_to_drop_synonyms)
END /*IF*/
--*/
'

SELECT *
FROM tempdb.dbo.synonyms
ORDER BY db_name

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial