SQL Server 多服务器查询

Server表数据结构:

CREATE TABLE [dbo].[Sys_Server]
(
[UpdateTime] [datetime] NULL,
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF__Sys_Serve__IsDel__267ABA7A] DEFAULT ((0)),
[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Sys_Server__ID__276EDEB3] DEFAULT (newsequentialid()),
[IsMain] [bit] NOT NULL,
[DataIP] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL,
[DataDBName] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL,
[DataUser] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL,
[DataPw] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL,
[DataDBType] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Sys_Server_CreateDate] DEFAULT (getdate()),
[IsLocal] [bit] NOT NULL CONSTRAINT [DF__Sys_Serve__IsLoc__286302EC] DEFAULT ((0)),
[Seq] [int] NOT NULL CONSTRAINT [DF__Sys_Server__Seq__29572725] DEFAULT ((0)),
[SynTime] [datetime] NULL,
[ReceiveTime] [datetime] NULL,
[Synchronized] [bit] NULL CONSTRAINT [DF__Sys_Serve__Synch__29CC2871] DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sys_Server] ADD CONSTRAINT [PK_Sys_Server] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MSE_ObjectID', N'4e50a8a2-fc10-4ecf-89e9-ea99098f3cf3', 'SCHEMA', N'dbo', 'TABLE', N'Sys_Server', NULL, NULL
GO

转换函数:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[ConvertSql]
    (
      @StrSql VARCHAR(MAX) ,
      @ServerID VARCHAR(55),
      @IsMainLoadAllSrv BIT
    )
RETURNS VARCHAR(MAX)
AS
    BEGIN
        DECLARE @NewLine CHAR,@Enter CHAR
        SET @NewLine=CHAR(10)
        SET @Enter=CHAR(13)
    
        DECLARE @Firsthand VARCHAR(MAX) ,
            @ReturnSql VARCHAR(MAX),
            @sql VARCHAR(MAX) ,
            @Tmp VARCHAR(max),
            @Seat1 INT,
            @Seat2 INT,
            @Seat3 INT
      
        SET @ReturnSql=''
        SET @Firsthand=@StrSql
        SET @Seat1=CHARINDEX('/*',@Firsthand)
        WHILE @Seat1>0 BEGIN
            SET @Seat2=CHARINDEX('*/',@Firsthand,@Seat1)
            SET @Seat3=CHARINDEX('/*',@Firsthand,@Seat1+2)
            IF @Seat3>0 AND @Seat3<@Seat2 BEGIN
                SET @Seat1=@Seat3
                continue
            END 
            SET @Tmp=SUBSTRING(@Firsthand,@Seat1,@Seat2-@Seat1+2)
            SET @Firsthand=REPLACE(@Firsthand,@Tmp,' ')
            SET @Seat1=CHARINDEX('/*',@Firsthand)
        END
        
        SET @Seat1=CHARINDEX('--',@Firsthand)
        WHILE @Seat1>0 BEGIN
            SET @Seat2=CHARINDEX(@NewLine,@Firsthand,@Seat1)
            IF @Seat2=0 BEGIN
                SET @Seat2=CHARINDEX(@Enter,@Firsthand,@Seat1)
            END
            IF @Seat2=0 BEGIN
                SET @Seat2=LEN(@Firsthand)
            END
            
            SET @Tmp=SUBSTRING(@Firsthand,@Seat1,@Seat2-@Seat1+1)
            SET @Firsthand=REPLACE(@Firsthand,@Tmp,' ')
            SET @Seat1=CHARINDEX('--',@Firsthand)
        END

          DECLARE @tb TABLE(ID UNIQUEIDENTIFIER PRIMARY KEY)
          DECLARE @SID UNIQUEIDENTIFIER,
          @IP VARCHAR(55),
          @User VARCHAR(55),
          @Pwd VARCHAR(55),
          @DbName VARCHAR(200),
          @IsMain BIT
          
          SELECT TOP 1 @SID=ID,@IP=DataIP,@User=DataUser,@Pwd=DataPw,@DbName=DataDBName,@IsMain=IsMain
            FROM dbo.Sys_Server 
            WHERE IsDeleted=0 AND ID = @ServerID 
          WHILE (@@ROWCOUNT>0 ) BEGIN
                
                INSERT INTO @tb (ID)VALUES  (@SID)
                DECLARE @num INT
                SELECT @num=COUNT(1) FROM @tb
                
                SET @sql = REPLACE(@Firsthand, 'dbo.', @DbName + '.dbo.')    
                SET @sql = REPLACE(@sql, '''', '''''')    
            
                SET @sql = @NewLine  + 'SELECT  * FROM    openrowset(''SQLOLEDB'',''' + @IP
                  + ''';''' + @User + ''';''' + @Pwd + ''',''' + @NewLine + @sql + ''')'
                IF (@Num>1) BEGIN
                    SET @ReturnSql = @ReturnSql + @NewLine + 'UNION ALL' + @NewLine
                END
                SET @ReturnSql =@ReturnSql+@sql
                IF(@IsMainLoadAllSrv=0 OR @IsMain=0) BREAK
                SELECT TOP 1 @SID=s.ID,@IP=s.DataIP,@User=s.DataUser,@Pwd=s.DataPw,@DbName=s.DataDBName 
                    FROM dbo.Sys_Server s
                    WHERE s.IsDeleted=0 AND NOT EXISTS(SELECT 1 FROM @tb WHERE ID=s.ID)
                    ORDER BY s.Seq
          END
        RETURN @ReturnSql
    END


GO
EXEC sp_addextendedproperty N'MSE_ObjectID', N'c6bfe6ab-d0df-491c-bee7-cb67cc2114cd', 'SCHEMA', N'dbo', 'FUNCTION', N'ConvertSql', NULL, NULL
GO

调用():

DECLARE @s NVARCHAR(MAX)
SET @s='
--查询语句
select ID,Code,Name from dbo.Sys_User
'
SELECT @s=dbo.ConvertSql(@s,'Server表中服务器的GuID','1,如果服务IsMain=1,加载所有服务器,否则查询单个服务器')
EXEC @s

 

ALTER PROCEDURE [dbo].[ConvertSelect] ( @sql NVARCHAR(MAX) )
AS
    BEGIN
        DECLARE @seat INT;
        SET @seat = PATINDEX('%select%', @sql) + 6;

        SET @sql = 'SELECT  (SELECT  DataDBName FROM dbo.Sys_Server WHERE IsLocal=1 AND IsDeleted=0) _DBName,'   + SUBSTRING(@sql, @seat, LEN(@sql) - @seat);
        SELECT  @sql = dbo.ConvertSql(@sql, ( SELECT  ID FROM dbo.Sys_Server  WHERE     IsMain = 1 AND IsDeleted = 0), 1);
        PRINT @sql;
        EXEC(@sql);
    END;

自动查询汇总12个服务器中的数据