November 17, 2016 at 11:20 am
CREATE PROCEDURE [dbo].[count]
AS
BEGIN
DECLARE @TABNAME NVARCHAR(255),
@TIME VARCHAR(100),
@sql NVARCHAR (1000 ),
@COUNT INT ,
@SUBJ NVARCHAR (1000 )
SET @TIME = GETDATE()
---CREATING TEMP TABLES
CREATE TABLE #TABLE_NAME
(
TABLE_NAME VARCHAR (100 )
)
CREATE TABLE #FINAL_RESULT
(
TABLE_NAME VARCHAR (100),
TIME DATETIME ,
COUNT INT
)
INSERT INTO #TABLE_NAME
SELECT NAME FROM Adventure..SYSOBJECTS (NOLOCK)
WHERE NAME IN ( 'ada','adad')
AND TYPE ='U'
---CURSOR DECLARATION
DECLARE queue CURSOR FOR
SELECT TABLE_NAME FROM #TABLE_NAME
OPEN TAB
FETCH NEXT FROM TAB INTO @TABNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT '''+@TABNAME+''' AS TABLE_NAME,'''+@TIME+''' AS LAST_CHECKED_DATE_TIME, COUNT(*) AS ROW_COUNT FROM adventure..[' + @TABNAME + ']
(NOLOCK) WHERE OPERATION_DATE >='''+@TIME+''''
--PRINT @sql
INSERT INTO #FINAL_RESULT
EXEC SP_EXECUTESQL @sql
FETCH NEXT FROM TAB INTO @TABNAME
END
CLOSE TAB
DEALLOCATE TAB
--ASSIGN @COUNT
SELECT @COUNT=COUNT (*) FROM #FINAL_RESULT
WHERE ROW_COUNT > 0
--CHECK CONDITON
IF @COUNT > 0
BEGIN
DECLARE @vResult VARCHAR(8000),
@body VARCHAR(8000)
SET @vResult = ''
SELECT @vResult = @vResult +
TABLE_NAME +
'--> LastChecked= '+ CAST(LAST_CHECKED_DATE_TIME AS VARCHAR(20))+
', DataModifiedCount= ' + CAST(ROW_COUNT AS VARCHAR(10)) +
Char(10)+Char(13)
FROM #FINAL_RESULT WHERE ROW_COUNT >=1
SELECT * FROM #FINAL_RESULT WHERE ROW_COUNT >=1
END
November 17, 2016 at 11:50 am
Is there a question here?
November 17, 2016 at 11:53 am
For what it's worth, the @@ROWCOUNT system variable contains the number of rows affected by the immediately preceeding DML operation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 17, 2016 at 11:56 am
This appears to be a blog post. Please add some descriptive text, if you think that this will assist others.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2016 at 12:31 pm
The code is full of errors. Also, it won't count DDL operations, just rows with an operation_date greater than now.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply