Cursor returns also empty records

I am using the below code to check identity column size.
In order to run it at all DBs i use cursor, but when i add it it returns me also empty results.
How I can exclude it?

IF OBJECT_ID('tempdb..#identity_columns') IS NOT NULL DROP TABLE #identity_columns

CREATE TABLE #identity_columns
( [database_name] SYSNAME NOT NULL,
[schema_name] SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
column_name SYSNAME NOT NULL,
[type_name] SYSNAME NOT NULL,
maximum_identity_value BIGINT NOT NULL,
current_identity_value BIGINT NULL,
percent_consumed DECIMAL(25,4) NULL );

DECLARE @sqlCommand nvarchar(max) = '',
@database sysname;

DECLARE dbList Cursor Local fast_forward
FROM sys.databases db
WHERE not in ('master', 'model', 'tempdb', 'msdb')

--==== Open and fetch
Open dbList;
Fetch Next From dbList Into @database;

While @@fetch_status = 0

Set @sqlCommand = '
Use ' + quotename(@database) + ';

INSERT INTO #identity_columns
([database_name], [schema_name], table_name, column_name, [type_name], maximum_identity_value, current_identity_value)
DB_NAME() AS database_name, AS schema_name, AS table_name, AS column_name, AS type_name,
WHEN = ''INT'' THEN CAST(2147483647 AS BIGINT)
WHEN = ''BIGINT'' THEN CAST(9223372036854775807 AS BIGINT)
WHEN IN (''DECIMAL'', ''NUMERIC'') THEN CAST(REPLICATE(9, columns.precision - columns.scale ) AS BIGINT)
END AS maximum_identity_value,
IDENT_CURRENT( concat(quotename(,''.'',quotename( ) ) ) AS current_identity_value
FROM sys.tables
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON types.user_type_id = columns.user_type_id
INNER JOIN sys.schemas
ON schemas.schema_id = tables.schema_id
WHERE columns.is_identity = 1
UPDATE #identity_columns
SET percent_consumed = CAST(CAST(current_identity_value AS DECIMAL(25,4)) / CAST(maximum_identity_value AS DECIMAL(25,4)) AS DECIMAL(25,2)) * 100;
select * from #identity_columns order by percent_consumed desc
use Scratch
IF EXISTS((SELECT 1 FROM #identity_columns where percent_consumed > 90))
SELECT @name=table_name FROM #identity_columns where percent_consumed > 90
SELECT @name = @name + ' Identity Limit Close'

	DECLARE @ErrorCode int,	@ErrorMessage varchar(4000) 
	EXEC dbo.[uspSendSmsNotification] @SMSText= @name , @ErrorCode=@ErrorCode  output, @ErrorMessage=@ErrorMessage  output
	SELECT @ErrorCode,	@ErrorMessage

Execute sp_ExecuteSql @sqlCommand;

Fetch Next From dbList Into @database;


Close dbList;
Deallocate dbList;

1 Like

Move this statement to the very end of your code:

select * from #identity_columns order by percent_consumed desc

Right now it's running in the loop that fetches from your cursor, and if the database name in the current loop has no identity columns, you'll get an empty result.

try sp_MSforeachdb

If you want a separate email by database - then you need to truncate the temp table at the beginning of each loop. If there are no qualifying rows then your notification will not be sent for that database.

If you want a separate email by table - then you need to be another cursor to loop over the list of table names that qualify to be sent.

You can simplify the code by moving the CASE expression to determine the max size to a CROSS APPLY or CTE and use CROSS APPLY to generate/create a fully qualified object name:

  Cross Apply (Values (concat_ws('.', object_schema_name(t.object_id), As o(obj_name)

Include quotename if you can have tables or schemas that require them to be quoted.

With that you can then calculate the percentage used in the same query:

      , percentage_used = (ident_current(o.obj_name) * 1.0 / mx.max_size) * 100

And you can include that same code in the where clause:

    And (ident_current(o.obj_name) * 1.0 / mx.max_size) * 100) > .9

And since you now have the object name - include the seed and increment in the report:

      , identity_seed = ident_seed(o.obj_name)
      , identity_increment = ident_incr(o.obj_name)

This will eliminate the update statement and eliminate the need to check for any data that exists in the temp table. Either there is data and that data qualifies - or there is no data and no report needs to be sent for that database.

One last note - you really should get in the habit of using aliases in your queries.