115 lines
3.4 KiB
Transact-SQL
115 lines
3.4 KiB
Transact-SQL
|
|
declare @cvCurrentOrganizationalUnit int,
|
|
@CV_OUT_value varchar(8000),
|
|
@ou_code varchar(10),
|
|
@ServerName varchar(20),
|
|
@restartdate datetime,
|
|
@errno int,
|
|
@errmsg varchar(255),
|
|
@LnkSrv varchar(15),
|
|
@cmd varchar(8000),
|
|
@Entity varchar(4);
|
|
|
|
exec arizona.dbo.sp_bmc_Bmc_Applic_Default
|
|
@in_job_type = 3,
|
|
@in_param_int_1 = null,
|
|
@in_param_int_2 = null,
|
|
@in_param_varchar_1 = 'cvCurrentOrganizationalUnit',
|
|
@out_default_value = @CV_out_value output,
|
|
@out_param_int_1 = null;
|
|
select @cvCurrentOrganizationalUnit = convert(int,@CV_out_value);
|
|
|
|
select @ou_code = ou.ou_code
|
|
from arizona.dbo.Organizational_unit ou (nolock)
|
|
where ou.Organizational_unit_ID = @cvCurrentOrganizationalUnit;
|
|
|
|
SELECT @Entity = [Customer] FROM [master].[cfg].[Identity];
|
|
SET @ServerName = @Entity+isnull(@ou_code,'')+'APS';
|
|
|
|
select @RestartDate = MIN(login_time)
|
|
from sys.dm_exec_sessions;
|
|
|
|
SET @LnkSrv = 'ARIZONACASH';
|
|
|
|
SET @cmd = '
|
|
|
|
IF OBJECT_ID(''tempdb..#tmp'')IS NOT NULL BEGIN
|
|
DROP TABLE #tmp;
|
|
END
|
|
|
|
SELECT TOP 50000
|
|
'''+@ServerName+''' as [SI_ServerName]
|
|
,[Stats_index_ID]
|
|
,[SI_schemaname]
|
|
,[SI_databasename]
|
|
,[SI_tablename]
|
|
,[SI_indexname]
|
|
,[SI_indextype]
|
|
,[SI_user_seeks]
|
|
,[SI_user_scans]
|
|
,[SI_user_lookups]
|
|
,[SI_user_updates]
|
|
,[SI_last_user_seek]
|
|
,[SI_last_user_scan]
|
|
,[SI_last_user_lookup]
|
|
,[SI_last_user_update]
|
|
,[SI_updatedate]
|
|
,[SI_restartdate]
|
|
,NULL as [SI_deletedate]
|
|
into #tmp
|
|
FROM [HCITools].[dbo].[Stats_index] si WITH (NOLOCK)
|
|
WHERE SI_updatedate < CAST('''+CONVERT(nvarchar(30),@RestartDate,126)+''' as datetime);
|
|
|
|
select ''delete from ARIZONACASH.HCITools.dbo.All_Stats_Index where [SI_ServerName] = ''''''+s.[SI_ServerName]+'''''' and [Stats_index_ID]=''+cast(s.[Stats_index_ID] as varchar(50))+'';'' as queryForCentral
|
|
from ARIZONACASH.HCITools.dbo.All_Stats_Index t
|
|
inner join #tmp s on s.[SI_ServerName] = t.[SI_ServerName] and s.[Stats_index_ID] = t.[Stats_index_ID];
|
|
|
|
|
|
--INSERT INTO '+@LnkSrv+'.HCITools.dbo.All_Stats_Index(
|
|
-- [SI_ServerName]
|
|
-- ,[Stats_index_ID]
|
|
-- ,[SI_schemaname]
|
|
-- ,[SI_databasename]
|
|
-- ,[SI_tablename]
|
|
-- ,[SI_indexname]
|
|
-- ,[SI_indextype]
|
|
-- ,[SI_user_seeks]
|
|
-- ,[SI_user_scans]
|
|
-- ,[SI_user_lookups]
|
|
-- ,[SI_user_updates]
|
|
-- ,[SI_last_user_seek]
|
|
-- ,[SI_last_user_scan]
|
|
-- ,[SI_last_user_lookup]
|
|
-- ,[SI_last_user_update]
|
|
-- ,[SI_updatedate]
|
|
-- ,[SI_restartdate]
|
|
-- ,[SI_deletedate]
|
|
--)
|
|
--select [SI_ServerName]
|
|
-- ,[Stats_index_ID]
|
|
-- ,[SI_schemaname]
|
|
-- ,[SI_databasename]
|
|
-- ,[SI_tablename]
|
|
-- ,[SI_indexname]
|
|
-- ,[SI_indextype]
|
|
-- ,[SI_user_seeks]
|
|
-- ,[SI_user_scans]
|
|
-- ,[SI_user_lookups]
|
|
-- ,[SI_user_updates]
|
|
-- ,[SI_last_user_seek]
|
|
-- ,[SI_last_user_scan]
|
|
-- ,[SI_last_user_lookup]
|
|
-- ,[SI_last_user_update]
|
|
-- ,[SI_updatedate]
|
|
-- ,[SI_restartdate]
|
|
-- ,[SI_deletedate]
|
|
--from #tmp;
|
|
--print ''inserted in central'';
|
|
--print @@rowcount;
|
|
|
|
DROP TABLE #tmp;
|
|
|
|
'
|
|
print @cmd
|
|
EXEC (@cmd)
|