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

目录

• 1 SQL Server 跨库/服务器查询
• 1.1 跨库查询
• 1.2 跨服务器查询
• 1.2.1 创建链接服务器
• 1.2.2 跨库查询
• 1.3 拓展：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]