:::: MENU ::::

Posts Tagged / Verify Partitions

  • Aug 31 / 2017
  • 0
T-SQL Scripts, Table Partitioning

Verify next month partitions in all databases

I have databases with partitioned tables in some databases. I occasionally had to verify that next month partition is there in each database. It required to check in each database one by one. To improve the process i created a script that parses the databases with partitioned tables one by one and checks either partition for next month data is there or not.

Specifications

My created script is specific to scenario and following are the specifications

  1. All partitioned tables in a DB are created on a single partition scheme. Hence there is only one partition scheme and partition function in a DB.
  2. Partitions are created with one month span. i.e. Each partition has one month data.
  3. Partitions are created with right boundary (less than)
  4.  Script checks either partition for next months data is added or not

Script



SET NOCOUNT ON
GO

Print 'Report for partitioning status on '+@@servername
-- Create tempDB to hold DB names with partitioned tables
IF OBJECT_ID(N'tempdb.dbo.#DBs') IS NOT NULL
	DROP TABLE #DBs

CREATE TABLE #DBs (DBName VARCHAR(50))

-- Insert DB names with partitioned tables
INSERT INTO #DBs (DBName)
EXEC sp_msforeachdb 
'use ?; if exists (select top 1 * from ?.[sys].[partitions] where partition_number >1) select db_name()'
GO

-- Declare variables
DECLARE @DB_Name VARCHAR(50)
DECLARE @Command NVARCHAR(1000)
-- Declare cursor for checking DBs one by one
DECLARE database_cursor CURSOR
FOR

SELECT DBname
FROM #DBs

OPEN database_cursor

FETCH NEXT
FROM database_cursor
INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Prepare sql for last partition check
	SELECT @Command = 'use ' + @DB_Name + ';
	 if (SELECT convert(bigint, max(r.value) ) 
	 FROM [sys].[partition_range_values] r inner join [sys].[partition_functions] f
  on r.function_id = f.function_id) 
  < (select CONVERT([bigint],replace
  (replace(CONVERT([varchar](13),DATEADD(DAY, 1,(DATEADD(MONTH,1 , EOMONTH(getdate())))),(121)),''-'',''''),'' '','''')+''00''))
  Print ''Partitioning issue in DB ' + @DB_Name + ''';' + 'else print ''Partitioning Ok in DB ' + @DB_Name + ''' ;'

	--print @Command;
	EXEC sp_executesql @Command;

	FETCH NEXT
	FROM database_cursor
	INTO @DB_Name
END

CLOSE database_cursor

DEALLOCATE database_cursor

GO

Output

Output will be in following format.

Verify next month partitions in all databases

 

Consult us to explore the Databases. Contact us