操作方法
提供的说明将介绍如何报告 SQL Server 地理数据库中的地理数据库版本统计数据。 以下提供的 SQL 脚本将报告有关 SQL Server 中版本化地理数据库的信息。 输出将列出版本数、阻止默认版本压缩为状态 0 的版本数、前 5 个阻止版本的所有者名称、状态数、state_lineages 表中的条目数,以及版本化表和增量表中的行数。
请按照以下提供的说明进行操作。
SET NOCOUNT ON -- 取消此 CREATE TABLE 语句和 INSERT INTO 语句的注释 -- 在第 150 行和第 151 行生成包含添加和删除计数的永久表。 /* 创建表 delta_info ( id int identity(1,1), table_name nvarchar(100), registration_id int, adds int, deletes int ) */ DECLARE @versions_info_tab TABLE ( ver_info_state_id int, source_lin int, com_anc_id int, lin_name int, state_id int ) DECLARE @blocking_list TABLE ( state_id int, name varchar(100) ) DECLARE @delta_table_info TABLE ( a_table_name nvarchar(50), d_table_name nvarchar(50), a_table_count int, d_table_count int ) DECLARE @ver_count int = (SELECT COUNT(*) FROM sde.SDE_versions) DECLARE @state_count int = (SELECT COUNT(*) FROM sde.sde_states) DECLARE @state_lineages_count int = (SELECT COUNT(*) cnt FROM sde.SDE_state_lineages) -- 如果从未对地理数据库进行压缩,则 SDE_compress_log 表将不存在。 DECLARE @last_compress varchar(15) IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN ('SDE', 'DBO') AND table_name = 'SDE_compress_log') BEGIN SET @last_compress = CONVERT(varchar(15), (SELECT MAX(compress_end) FROM sde.SDE_compress_log), 1) END ELSE BEGIN SET @last_compress = 'No compress log table'; END DECLARE @ver_info_state_id int, @message varchar(100) = '', @source_lin int, @com_anc_id int, @lin_name int, @state_id int, @ver_blocking int = 0, @cur_max_state int, @pos int, @idx int, @s1 int = 0, @s2 int = 0, @s3 int = 0, @s4 int = 0, @s5 int = 0, @blocking_ver_name nvarchar(100), @blocking_ver_count int, @table_info_name varchar(100), @table_info_reg_id int, @table_info_owner varchar(100), @p_stmt nvarchar(200),@a_stmt nvarchar(max), @d_stmt nvarchar(max), @row_count int DECLARE table_info_cur CURSOR FOR SELECT owner, table_name, registration_id FROM sde.sde_table_registry WHERE sde.sde_table_registry.object_flags&8 = 8 DECLARE ver_list_cur CURSOR FOR SELECT DISTINCT state_id FROM sde.sde_versions WHERE name = 'DEFAULT' and owner IN ('SDE', 'DBO') ORDER BY state_id; SELECT @state_id=state_id, @lin_name = lineage_name FROM sde.sde_states WHERE state_id = (SELECT state_id FROM sde.sde_versions WHERE name = 'DEFAULT' and owner IN ('SDE', 'DBO')); SET @cur_max_state = @state_id PRINT '========== Versioning Statistics =================' PRINT '' PRINT 'Number of versions: ' + CONVERT(varchar, @ver_count) PRINT '==============' OPEN ver_list_cur FETCH NEXT FROM ver_list_cur INTO @ver_info_state_id WHILE @@FETCH_STATUS = 0 BEGIN SELECT @row_count = COUNT(*) FROM sde.sde_state_lineages WHERE lineage_name = @lin_name AND lineage_id
示例输出: ==========Versioning Statistics================= Number of versions: 2 ============== Number of versions blocking DEFAULT: 1 Blocking Versions: dbo.QA Number of states: 3577 Number of state lineages: 100015 Last Compress: 2018-01-09 Table: DBO.CONTROLCORNER (45) Adds Count: 124 Deletes Count: 124 Table: DBO.PLSSBOUNDARY (46) Adds Count: 8 Deletes Count: 8
从 ArcGIS 专家处获得帮助
下载 Esri 支持应用程序