Citrix SQL数据库查询和替换修改字段-摩杜云开发者社区

1 如下图，右键指定库新建查询即可

Citrix SQL数据库查询和替换修改字段_右键

2 粘贴底部代码后，如下，点击执行查询即可

Citrix SQL数据库查询和替换修改字段_数据库_02

--查看Citrix站点数据库如下表内容字段
select * from chb_config.Controllers;
select * from ADIdentitySchema.Services;
select * from HostingUnitServiceSchema.Services;
select * from AppLibrarySchema.Services;
select * from DesktopUpdateManagerSchema.Services;
select * from Monitor.Services;
select * from StorefrontSchema.Services;
select * from TrustSchema.Services;
select * from EnvTestServiceSchema.Services;
select * from Analytics.Services;
select * from OrchestrationSchema.Services;
select * from ConfigurationSchema.Services;
select * from ConfigLoggingSiteSchema.Services;
select * from DAS.Services;

3 替换指定表的指定字段内容，如下set sid=“新的内容” where sid=“原来的内容”。

--如下是替换上述表的SID字段内容
update   chb_Config.Controllers set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   ADIdentitySchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   HostingUnitServiceSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   AppLibrarySchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   DesktopUpdateManagerSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   Monitor.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   StorefrontSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   TrustSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   EnvTestServiceSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   Analytics.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   OrchestrationSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   ConfigurationSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   ConfigLoggingSiteSchema.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';
update   DAS.Services set SID='S-1-5-21-788223459-3112803599-3052912352-2109' where sid='S-1-5-21-788223459-3112803599-3052912352-1104';

3 查询指定数据库下包含指定字段和内容的表有哪些。

--查看CitrixSZsite库下SID='S-1-5-21-788223459-3112803599-3052912352-1104'字段的表有哪些
USE CitrixSZsite;
DECLARE @ColumnName NVARCHAR(128) = 'SID';
DECLARE @SIDValue NVARCHAR(100) = 'S-1-5-21-788223459-3112803599-3052912352-1104';
DECLARE @sql NVARCHAR(MAX) = (
    SELECT STRING_AGG(
        'SELECT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''' AS TABLE_NAME
        FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
        WHERE ' + QUOTENAME(c.name) + ' = ' + QUOTENAME(@SIDValue, '''')
    , ' UNION ALL ')
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    JOIN sys.schemas s on t.schema_id = s.schema_id
    WHERE c.name = @ColumnName
);
EXEC sp_executesql @sql;


--查看CitrixSZsite库下含有SID字段的表有哪些
USE CitrixSZsite;
DECLARE @ColumnName NVARCHAR(128) = 'SID';
SELECT s.name + '.' + t.name AS TABLE_NAME
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE c.name = @ColumnName;


--查看表里面指定字段值的行，如下为示例
SELECT [Id]
      ,[Sid]
      ,[Name]
      ,[DnsName]
      ,[LifecycleState]
      ,[IPAddress]
FROM [CitrixSZMonitoring].[MonitorData].[Machine]
where sid = 'S-1-5-21-788223459-3112803599-3052912352-1249'

--多条件匹配and同时满足条件为真，OR一个满足条件为真
WHERE column1 = value1 AND column2 = value2;

#where语法
WHERE column_name <> value;
-- 或
WHERE column_name != value;

--% 代表任意字符序列（包括空字符序列），_ 代表单个字符。
WHERE column_name LIKE 'pattern';

--IN 匹配（IN Matching）：使用 IN 关键字来匹配包含在给定值列表中的行，NOT表示不在
WHERE column_name IN (value1, value2, ...);
WHERE column_name NOT IN (value1, value2, ...);

--范围匹配（Range Matching）：使用 BETWEEN 和 AND 关键字来匹配指定范围内的值。
WHERE column_name BETWEEN value1 AND value2;