Get synonym definitions for all databases in server

1 minute read

If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested.

Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp table and display results.:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
/*
    create temp table for holding synonym definitions & list of DB
    */

    if object_id('tempdb..#dblist') is not null
        drop table #dblist;
    select
        *
    into #dblist
    from
        sys.databases
    where
        name not in ('master', 'tempdb', 'model', 'msdb')
        and State_desc = 'ONLINE'
        and Is_In_Standby = 0
    if object_id('tempdb..#temp') is not null
        drop table #temp;

    create table #temp
        (
            db_name               sysname
            ,object_id             int
            ,name                  sysname
            ,base_object_name      sysname
            ,server_name_hardcoded as case
                when base_object_name like '%ThisDatabaseIsOkToHardCode%'
                then 0
                when len(base_object_name)
                        - len(replace(base_object_name, '.', '')) > 2
                then 1
                else 0
            end
        )

    go

    declare @DbName sysname
    declare @XSQL varchar(max)
    declare @CompleteSQL varchar(max)
    declare db_cursor cursor fast_forward read_only local for select
                name
            from
                #dblist
    open db_cursor
    fetch next from db_cursor into @DbName;

    while @@fetch_status = 0
    begin
        set @XSQL = '
                    insert into #temp
                    ( db_name ,object_id ,name,base_object_name )
                    select
                        db_name()
                        ,s.object_id
                        ,s.name
                        ,s.base_object_name
                    from
                        sys.synonyms s
                    '
        set @CompleteSQL = 'USE ' + @DbName
                            + '; EXEC sp_executesql N'''
                            + @XSQL + '''';
        exec (@CompleteSQL)
        fetch next from db_cursor into @DbName;
    end

    close db_cursor
    deallocate db_cursor
    go
    select
        *
    from
        #temp t

Leave a Comment