Citrix SQL数据库查询和替换修改字段
1 如下图，右键指定库新建查询即可
2 粘贴底部代码后，如下，点击执行查询即可
--查看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;
