SQL Server 游标运用：批量创建、删除链接服务器

顺溜的书架 2015-12-02

展开全文

一、背景

我们的数据库比较多，它们提供了外网的访问，我现在想对这些数据库进行一些管理，获取这些数据库的一些信息，我们可以通过什么方式实现呢？

在SQL Server2005版本之后有一个叫做链接服务器的新功能，基本的操作可以参考：

SQL Server 2005链接服务器，我们就通过这个链接服务器来获取我们需要的数据，但是我们的服务器比较多，这个批量创建链接服务器和批量删除链接服务器就呼之欲出了。

二、设计过程

设计简述：创建如下图的表结构，LinkName保存远程链接的别名，LinkName2是创建链接方式2的一个补充字段，LinkIP代表远程服务器的地址，如果有端口的还需要加上端口。

这里为什么要设计成LinkName与LinkName2并存呢？这是因为我们在T-SQL使用远程链接的时候是通过别名的，我在进行两种方式的切换，只要修改调换下这两个字段的名称，并且去存储过程sp_CreateLink注释方式1的代码，恢复方式2的代码；

（图1：LinkConfig表，链接方式1）

（图2：LinkConfig表，链接方式2）

（图3：链接方式1的属性）

（图4：链接方式2的属性）

详细代码：创建表LinkConfig、批量创建链接服务器存储过程、批量删除链接服务器存储过程。

--创建表
CREATE TABLE [dbo].[LinkConfig](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LinkName] [nvarchar](50) NULL,
    [LinkName2] [nvarchar](50) NULL,
    [LinkIP] [nvarchar](50) NULL,
    [LinkSa] [nvarchar](50) NULL,
    [LinkPassword] [nvarchar](50) NULL,
    [State] [int] NULL CONSTRAINT [DF_LinkConfig_State]  DEFAULT ((0)),
 CONSTRAINT [PK_LinkConfig] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--创建远程链接sp
-- =============================================
-- Author:        <Viajar>
-- Create date: <2012.01.05>
-- Description:    <创建远程链接>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CreateLink] 

AS
BEGIN

    SET NOCOUNT ON;

    --创建远程链接
    DECLARE @linkname VARCHAR(100)
    DECLARE @linkip VARCHAR(100)
    DECLARE @linksa VARCHAR(100)
    DECLARE @linkpassword VARCHAR(100)
    DECLARE @isexists VARCHAR(10)
    DECLARE @sql NVARCHAR(4000)
    SET @isexists = 'False'
    DECLARE @itemCur CURSOR
    SET @itemCur = CURSOR FOR 
        SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1

    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
    WHILE @@FETCH_STATUS=0
    BEGIN
        --正在处理
        PRINT @linkname

        --判断是否存在
        set @sql = 
            N'IF EXISTS (SELECT * FROM sys.servers WHERE name = '''+ @linkname + ''')
            begin set @IsExistsOUT = ''True'' end'
        exec sp_executesql @sql,N'@IsExistsOUT varchar(10) OUTPUT',@IsExistsOUT=@isexists OUTPUT

        --不存在
        IF(@IsExists = 'False')
        BEGIN
            --创建链接方式
            SET @sql = '
            EXEC master.dbo.sp_addlinkedserver @server = N'''+@linkname+''', @srvproduct=N''SQL Server'''
            EXEC(@sql)

            --设置密码
            SET @sql = '
            EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'''+@linkname+''', @locallogin = NULL , @useself = N''False'', @rmtuser = N'''+@linksa+''', @rmtpassword = N'''+@linkpassword+''''
            EXEC(@sql)

--            --创建链接方式
--            SET @sql = '
--            EXEC master.dbo.sp_addlinkedserver @server = N'''+@linkname+''', @srvproduct=N'''+@linkname+''', @provider=N''SQLNCLI'', @datasrc=N'''+@linkip+''''
--            EXEC(@sql)
--
--            --设置密码
--            SET @sql = '
--            EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'''+@linkname+''', @locallogin = NULL , @useself = N''False'', @rmtuser = N'''+@linksa+''', @rmtpassword = N'''+@linkpassword+''''
--            EXEC(@sql)
--
--            --设置属性
--            SET @sql = '
--            EXEC master.dbo.sp_serveroption @server=N'''+@linkname+''', @optname=N''rpc'', @optvalue=N''true''
--            ;
--            EXEC master.dbo.sp_serveroption @server=N'''+@linkname+''', @optname=N''rpc out'', @optvalue=N''true''
--            '
--            EXEC(@sql)
        END

        SET @isexists = 'False'
        FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
    END 

    CLOSE @itemCur
    DEALLOCATE @itemCur

END

--删除远程链接sp
-- =============================================
-- Author:        <Viajar>
-- Create date: <2012.01.05>
-- Description:    <删除远程链接>
-- =============================================
ALTER PROCEDURE [dbo].[sp_DropLink] 

AS
BEGIN

    SET NOCOUNT ON;

    --删除远程链接
    DECLARE @linkname VARCHAR(100)
    DECLARE @linkip VARCHAR(100)
    DECLARE @linksa VARCHAR(100)
    DECLARE @linkpassword VARCHAR(100)
    DECLARE @isexists VARCHAR(10)
    DECLARE @sql NVARCHAR(4000)
    SET @isexists = 'False'
    DECLARE @itemCur CURSOR
    SET @itemCur = CURSOR FOR 
        SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1

    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
    WHILE @@FETCH_STATUS=0
    BEGIN
        --正在处理
        PRINT @linkname

        --判断是否存在
        set @sql = 
            N'IF EXISTS (SELECT * FROM sys.servers WHERE name = '''+ @linkname + ''')
            begin set @IsExistsOUT = ''True'' end'
        exec sp_executesql @sql,N'@IsExistsOUT varchar(10) OUTPUT',@IsExistsOUT=@isexists OUTPUT

        --不存在
        IF(@IsExists = 'True')
        BEGIN
            --删除链接
            SET @sql = '
            IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'''+@linkname+''')
            BEGIN
            EXEC master.dbo.sp_dropserver @server=N'''+@linkname+''', @droplogins=''droplogins''
            END'
            EXEC(@sql)
        END

        SET @isexists = 'False'
        FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
    END 

    CLOSE @itemCur
    DEALLOCATE @itemCur

END