DBA - Script out the Linked server objects.

 

Script out the Linked server objects.

      

CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]    

AS      

/***********************************************************************************************************   

--Purporse: To extract Linkded server connection details  

  

--Created By: kovvuru subbareddy    

  

Purpose:   

TEST:     

  EXEC [dbo].[win_linked_server_objects_extract_select]      

***********************************************************************************************************/    

BEGIN  

SET NOCOUNT ON  

  

CREATE TABLE #temp_linkedserver_detail  

(

ID INTEGER identity(1,1) primary key NOT NULL,  

create_linked_server_command VARCHAR(5000),  

linked_server_name varchar(100)  

)  

  

INSERT INTO #temp_linkedserver_detail(create_linked_server_command,linked_server_name)  

select  

'  

USE [master]  

GO  

  

EXEC master.dbo.sp_addlinkedserver @server = N'''+a.name+''', @srvproduct=N'''+a.product+''',@provider = N''' +a.provider+''',@datasrc = N''' +COALESCE(a.data_source,'')+''' ,@catalog = N''' +COALESCE(a.catalog,'')+'''   

GO  

  

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+a.name+''',@useself=N'+case when uses_self_credential = 0 then '''False''' else '''True''' end +',@locallogin=NULL,@rmtuser=N'''+b.remote_name+''',@rmtpassword=''########''  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''collation compatible'', @optvalue=N'+case when is_collation_compatible = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''data access'', @optvalue=N'+ case when is_data_access_enabled = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''dist'', @optvalue=N'+ case when is_distributor = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''pub'', @optvalue=N'+ case when is_publisher = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''rpc'', @optvalue=N'+ case when is_remote_login_enabled = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''rpc out'', @optvalue=N'+ case when is_rpc_out_enabled = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''sub'', @optvalue=N'+ case when is_subscriber = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''connect timeout'', @optvalue=N'''+convert(varchar,connect_timeout)+'''  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''collation name'', @optvalue='+case when collation_name is null then 'null' else collation_name end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''lazy schema validation'', @optvalue=N'+ case when lazy_schema_validation = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''query timeout'', @optvalue=N'''+convert(varchar,query_timeout)+'''  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''use remote collation'', @optvalue=N'+ case when uses_remote_collation = 0 then '''False''' else '''True''' end +'  

GO  

  

EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''remote proc transaction promotion'', @optvalue=N'+ case when is_remote_proc_transaction_promotion_enabled = 0 then '''False''' else '''True''' end +'  

GO  

' as create_linked_server_command,  

a.name  

FROM sys.servers a  

LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id  

LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id  

where a.server_id > 0  

and remote_name is not null  

  

SELECT  

 create_linked_server_command --,linked_server_name   

FROM #temp_linkedserver_detail  

  

END

GO