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
No comments:
Post a Comment