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
|
Webmentions
(No webmentions yet.)