June 11, 2008 at 3:56 am
I'm trying to identify all the stored procedures that use linked server names, I need a script that will itterate through all the databases and stored procedures and highlight any that contain linked servers, I know what I want but don't know how to exactly do it, could someone help me piece this together?
I so far have a simple script to look in the stored procedures for any linked server:
declare @LinkedServer nvarchar(50)
DECLARE LServerCursor CURSOR FOR
SELECT Name
FROM sys.servers
WHERE server_id > 0
OPEN LserverCursor
FETCH NEXT FROM LServerCursor INTO @LinkedServer
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+ @LinkedServer + '%'
FETCH NEXT FROM LServerCursor INTO @LinkedServer
END
Close LServerCursor
Deallocate LServerCursor
All I need to do is make it iterate through each user database, can anyone tell me how to do this?
June 11, 2008 at 3:46 pm
Please run this query:
Declare @Results Table
(
DBName nvarchar(256),
LinkedServer nvarchar(256),
SPName nvarchar(256)
)
declare @DBName nvarchar(256)
declare @LinkedServer nvarchar(256)
Declare @Qry nvarchar(1024)
DECLARE LServerCursor CURSOR FOR
SELECT Name
FROM sys.servers
WHERE server_id > 0
OPEN LserverCursor
FETCH NEXT FROM LServerCursor INTO @LinkedServer
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE DBCursor CURSOR FOR
SELECT Name
FROM sys.Databases Where Source_database_id Is Null
Open DBCursor
FETCH NEXT FROM DBCursor INTO @DBName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
Set @Qry = 'use ' + @DBName + '; SELECT ''' + @DBName + ''',
''' + @LinkedServer + ''', [Name] FROM ' + @DBName + '.sys.procedures Where OBJECT_DEFINITION(OBJECT_ID)
Like ''%' + @LinkedServer + '.%'';'
Insert Into @Results
exec sp_executesql @Qry
--Select @qry
FETCH NEXT FROM DBCursor INTO @DBName
END
Close DBCursor
Deallocate DBCursor
FETCH NEXT FROM LServerCursor INTO @LinkedServer
END
Close LServerCursor
Deallocate LServerCursor
Select * from @Results order by DBName, LinkedServer, SPName
This will give you db names, linked servers used in that db along with the SPs using that.
June 12, 2008 at 1:19 am
Set @Qry = 'use ' + @DBName + '; SELECT ''' + @DBName + ''',
''' + @LinkedServer + ''', [Name] FROM ' + @DBName + '.sys.procedures Where OBJECT_DEFINITION(OBJECT_ID)
Like ''%' + @LinkedServer + '.%'';'
Insert Into @Results
exec sp_executesql @Qry
Thank you, it was this bit above that I knew I needed in my code but I didn't know how to do it and wasn't quite sure how to explain.
Thanks again
June 12, 2008 at 6:00 am
Hi,
I have updated the SP for Following
1. Looking for is_linked = 1
2. Exclude Offline databases and they may give problem
3. Use CHARINDEX function to search the procedure text.
Please test and le me know if it works for you.
Use Master
Declare @Results Table
(
DBName nvarchar(256),
LinkedServer nvarchar(256),
SPName nvarchar(256)
)
declare @DBName nvarchar(256)
declare @LinkedServer nvarchar(256)
Declare @Qry nvarchar(1024)
DECLARE LServerCursor CURSOR FOR
SELECT Name
FROM sys.servers
WHERE is_linked = 1 -- Looked for Linked servers only
OPEN LserverCursor
FETCH NEXT FROM LServerCursor INTO @LinkedServer
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE DBCursor CURSOR FOR
SELECT Name
FROM sys.Databases Where Source_database_id Is Null
and state <> 3 -- To disable search in offline databases
Open DBCursor
FETCH NEXT FROM DBCursor INTO @DBName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- Search the Linked Server in the object defination
Set @Qry = 'use ' + @DBName + '; SELECT ''' + @DBName + ''',
''' + @LinkedServer + ''', [Name] FROM ' + @DBName + '.sys.procedures
Where CHARINDEX('''+ @LinkedServer +''',OBJECT_DEFINITION(OBJECT_ID)) > 0;'
Insert Into @Results
exec sp_executesql @Qry
--Select @qry
FETCH NEXT FROM DBCursor INTO @DBName
END
Close DBCursor
Deallocate DBCursor
FETCH NEXT FROM LServerCursor INTO @LinkedServer
END
Close LServerCursor
Deallocate LServerCursor
Select * from @Results order by DBName, LinkedServer, SPName
Thanks,
Rahul
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply