Link to home
Start Free TrialLog in
Avatar of michalek19
michalek19Flag for United States of America

asked on

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

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 ........
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
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
Avatar of michalek19
michalek19
Flag of United States of America image

ASKER

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)
Avatar of michalek19
michalek19
Flag of United States of America image

ASKER

I still see synonyms. They were not dropped
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
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.
Avatar of michalek19
michalek19
Flag of United States of America image

ASKER

In which part of the script i have to remove "--" ? can you please send me script ready to remove synonyms
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.