SQL Server跨库/服务器查询及拓展知识点

Database.DatabaseSchema.DatabaseObject
# 示例 [SqlMatc] 数据库中，查询数据库[SQLATM]里的 DeptSales_copy 表里的所有数据
select * from [SQLATM].[dbo].[DeptSales_copy]

sp_addlinkedserver [ @server = ] 'server' 	-- 链接服务器的名称
    [ , [ @srvproduct = ] 'product_name' ]  -- 链接服务器的 OLE DB 数据源的产品名
    										-- product_name值为 nvarchar (128) ，
    										-- 默认值为 NULL。 
    									 -- 如果值为SQL Server，则无需指provider_name、
    									 -- data_source、位置、provider_string和目录。
    										
    [ , [ @provider = ] 'provider_name' ] 	-- 唯一编程标识符。建议使用 MSOLEDBSQL 而不是 SQLNCLI。
    [ , [ @datasrc = ] 'data_source' ] -- 目的服务器地址
    [ , [ @location = ] 'location' ]   -- 本地登录
    [ , [ @provstr = ] 'provider_string' ] -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
    [ , [ @catalog = ] 'catalog' ] -- 与 OLE DB 提供程序建立连接时要使用的目录。
    

if exists(select * from sys.servers where name='LinkedServerName')
begin
	--删除运行本地与远程之间的用户映射
	execute sys.sp_droplinkedsrvlogin @rmtsrvname='LinkedServerName', @locallogin=null
	--删除链接服务器
	execute sys.sp_dropserver @server='LinkedServerName', @droplogins='droplogins'
end
go

EXEC sp_addlinkedserver
     @server = 'LinkedServerName'	-- 目的服务器别名
    ,@srvproduct = ''
    ,@provider = 'MSOLEDBSQL' 
    ,@datasrc = '192.168.3.21' -- 目的服务器IP地址
    ,@location = ''	-- 本地登录
    ,@provstr = '' -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
    --@catalog = '';	-- 指定目录 默认值为 NULL

-- 将凭据和选项添加到此链接服务器。
EXEC sp_addlinkedsrvlogin
	 @rmtsrvname = 'LinkedServerName'
	,@useself = 'false'	-- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器
	,@rmtuser = 'sa'	-- 登录名
	,@rmtpassword = 'root';	-- 密码

EXEC sp_serveroption 'LinkedServerName', 'rpc', true;	-- 从指定的服务器启用远程过程调用 (RPC)
EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;	-- 对指定的服务器启用 RPC。

-- 查询示例 select * from Server.Database.DatabaseSchema.DatabaseObject
SELECT name FROM [LinkedServerName].master.sys.databases;  

create proc RemoteConnectionServer
	@LinkedServerName nvarchar(255)	-- 目的服务器别名
	,@LinkedServerIP nvarchar(255) -- 目的服务器IP地址
	,@userName nvarchar(255)	-- 登录名
	,@password nvarchar(255)	-- 密码
as
	-- 查找链接服务器是否已创建，若创建则删除
	if exists(select * from sys.servers where name= @LinkedServerName)
	begin
		-- 删除运行本地与远程之间的用户映射
		execute sys.sp_droplinkedsrvlogin @rmtsrvname= @LinkedServerName, @locallogin=null
		--删除链接服务器
		execute sys.sp_dropserver @server=@LinkedServerName, @droplogins='droplogins'
	end

	-- 创建链接服务器 
	exec sp_addlinkedserver
		 @server = @LinkedServerName	-- 目的服务器别名
		,@srvproduct = ''
		,@provider = 'MSOLEDBSQL' 
		,@datasrc = @LinkedServerIP -- 目的服务器IP地址
		,@location = ''	-- 本地登录
		,@provstr = '' -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
		--@catalog = '';	-- 指定目录 默认值为 NULL

	-- 将凭据和选项添加到此链接服务器。
	exec sp_addlinkedsrvlogin
		 @rmtsrvname = @LinkedServerName
		,@useself = 'false'	-- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器
		,@rmtuser = @userName	-- 登录名
		,@rmtpassword = @password;	-- 密码

	-- 设置服务器选项
	exec sp_serveroption @LinkedServerName, 'rpc', true;	-- 从指定的服务器启用远程过程调用 (RPC)
	exec sp_serveroption @LinkedServerName, 'rpc out', true;	-- 对指定的服务器启用 RPC。

go

declare	@LinkedServerName nvarchar(255)	= 'linkName'
declare	@LinkedServerIP nvarchar(255) = '192.168.3.21'
declare	@userName nvarchar(255)	= 'sa'
declare	@password nvarchar(255)	= '****'
-- 执行
exec RemoteConnectionServer @LinkedServerName, @LinkedServerIP, @userName, @password

-- 测试
SELECT name FROM linkName.master.sys.databases;  

-- 查询示例 select * from Server.Database.DatabaseSchema.DatabaseObject
SELECT * FROM [192.168.3.21].[GZ].[dbo].[Dept]

Server.Database.DatabaseSchema.DatabaseObject

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin')
DROP SCHEMA [db_accessadmin]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_backupoperator')
DROP SCHEMA [db_backupoperator]