Hi.
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
GO
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
FOR
SELECT db.name
FROM sys.databases db
WHERE db.name not in ('master', 'model', 'tempdb', 'msdb')
--==== Open and fetch
Open dbList;
Fetch Next From dbList Into @database;
While @@fetch_status = 0
Begin
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)
SELECT
DB_NAME() AS database_name,
schemas.name AS schema_name,
tables.name AS table_name,
columns.name AS column_name,
types.name AS type_name,
CASE
WHEN types.name = ''TINYINT'' THEN CAST(255 AS BIGINT)
WHEN types.name = ''SMALLINT'' THEN CAST(32767 AS BIGINT)
WHEN types.name = ''INT'' THEN CAST(2147483647 AS BIGINT)
WHEN types.name = ''BIGINT'' THEN CAST(9223372036854775807 AS BIGINT)
WHEN types.name IN (''DECIMAL'', ''NUMERIC'') THEN CAST(REPLICATE(9, columns.precision - columns.scale ) AS BIGINT)
ELSE -1
END AS maximum_identity_value,
IDENT_CURRENT( concat(quotename(schemas.name),''.'',quotename( tables.name ) ) ) 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
DECLARE @name VARCHAR(100)
IF EXISTS((SELECT 1 FROM #identity_columns where percent_consumed > 90))
BEGIN
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
END
Execute sp_ExecuteSql @sqlCommand;
Fetch Next From dbList Into @database;
End
Close dbList;
Deallocate dbList;
