StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's a script to find all procs/functions/views that reference linked servers on a SQL 2005 instance - might be useful too:</p> <pre> USE master GO SET NOCOUNT ON; -------------------------------------------------------------------- -- Test linked server connections -------------------------------------------------------------------- BEGIN TRY DROP TABLE #Svrs; END TRY BEGIN CATCH END CATCH; CREATE TABLE #Svrs ( [Server] nvarchar(max), [CanConnectAsDefault] bit ); DECLARE @ServerName nvarchar(max), @RetVal int; DECLARE Svrs CURSOR FAST_FORWARD READ_ONLY FOR SELECT ServerName = S.name FROM sys.servers S; OPEN Svrs; FETCH NEXT FROM Svrs INTO @ServerName; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC @RetVal = sys.sp_testlinkedserver @ServerName; END TRY BEGIN CATCH SET @RetVal = sign(@@error); END CATCH; INSERT INTO #Svrs SELECT @ServerName , CASE WHEN @RetVal = 0 THEN 1 ELSE 0 END; FETCH NEXT FROM Svrs INTO @ServerName; END; CLOSE Svrs; DEALLOCATE Svrs; SELECT * FROM #Svrs DROP TABLE #Svrs; GO -------------------------------------------------------------------- -- Report linked server references -------------------------------------------------------------------- BEGIN TRY DROP TABLE #Refs; END TRY BEGIN CATCH END CATCH; CREATE TABLE #Refs ( [Server] nvarchar(max), [Database] nvarchar(max), [Schema] nvarchar(max), [Object] nvarchar(max), [Type] nvarchar(max) ); DECLARE @DatabaseName nvarchar(max), @ServerName nvarchar(max), @SQL nvarchar(max); DECLARE Refs CURSOR FAST_FORWARD READ_ONLY FOR SELECT DatabaseName = D.name , ServerName = S.name -- , ServerProvider = S.provider -- , ServerSource = S.data_source FROM sys.databases D CROSS JOIN sys.servers S WHERE D.name NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB'); OPEN Refs; FETCH NEXT FROM Refs INTO @DatabaseName, @ServerName; WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'USE [' + @DatabaseName + ']; INSERT INTO #Refs SELECT DISTINCT ''' + @ServerName + ''', ''' + @DatabaseName + ''', S.[name], O.[name], O.type_desc FROM syscomments C INNER JOIN sys.objects O ON C.id = O.[object_id] LEFT JOIN sys.schemas S ON S.[schema_id] = O.[schema_id] WHERE C.[TEXT] LIKE ''%[ ,~[( '''']' + @ServerName + '[ .,~])'''' ]%'' ESCAPE ''~'';' PRINT 'Looking for ' + @ServerName + ' refs in ' + @DatabaseName -- + ': ' + @SQL; EXEC sp_executesql @SQL; FETCH NEXT FROM Refs INTO @DatabaseName, @ServerName; END CLOSE Refs; DEALLOCATE Refs; SELECT * FROM #Refs DROP TABLE #Refs; GO -------------------------------------------------------------------- SET NOCOUNT OFF; GO </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload