-- 작업할 DB를 저장할 임시테이블
SELECT name as [DatabaseName]
INTO #tmpPubDatabases
FROM sys.databases
WHERE database_id > 4
-- object를 저장할 임시테이블
CREATE TABLE #tmpObjects(
DBName VARCHAR(256),
objectName VARCHAR(256),
obj_desc varchar(100),
create_date datetime,
modify_date datetime
);
DECLARE @command VARCHAR(MAX);
DECLARE @condition tinyint;
SET @condition = 7 --일주일 이내에 변경된 데이터 가져올것
-- 변경된 데이터를 불러서 저장
SET @command = '
USE [?]
IF DB_NAME() IN (SELECT DatabaseName FROM #tmpPubDatabases)
BEGIN
INSERT #tmpObjects
SELECT db_name() dbname, name objectName, type_desc as obj_desc, create_date,modify_date
FROM sys.objects
WHERE type IN (''TF'' , ''FN'', ''P'', ''V'', ''PK'', ''F'', ''TR'', ''UQ'', ''C'', ''D'', ''U'')
AND modify_date > getdate() - '+ CAST(@condition AS varchar(2)) +'
END';
-- run for all affected databases
EXEC sp_MSforeachdb @command
-- this will match the publications to the tables and give the you row count and sizes
-- run this in the distribution database
SELECT * FROM
#tmpObjects
--#tempObjects 삭제쿼리 넣기
-- drop table #tmpPubDatabases
-- drop table #tmpObjects