Get the maximum value of the primary key of each table in a database. (SQL Server)

MaxPK

-- This query generates another query which you will then run to get the maximum IDs.
SELECT STUFF((
	SELECT	'UNION SELECT '''+TABLE_SCHEMA+''' AS TABLE_SCHEMA,'''+TABLE_NAME+''' AS TABLE_NAME,'''+COLUMN_NAME+''' AS COLUMN_NAME,'
		+'CAST(MAX(['+COLUMN_NAME+']) AS VARCHAR) FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] AS [MaxPrimaryKeyValue] '
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 
	ORDER BY TABLE_SCHEMA,TABLE_NAME
	FOR XML PATH('')
),1,6,'') + 'ORDER BY TABLE_SCHEMA,TABLE_NAME'

-- Example of resulting query:
-- SELECT 'dbo' AS TABLE_SCHEMA,'Appointments' AS TABLE_NAME,'ID' AS COLUMN_NAME,MAX([ID]) AS [MaxPrimaryKeyValue] FROM [dbo].[Appointments] 
-- UNION SELECT 'dbo' AS TABLE_SCHEMA,'Assessments' AS TABLE_NAME,'ID' AS COLUMN_NAME,MAX([ID]) AS [MaxPrimaryKeyValue] FROM [dbo].[Assessments] 
-- UNION SELECT 'dbo' AS TABLE_SCHEMA,'Classes' AS TABLE_NAME,'ID' AS COLUMN_NAME,MAX([ID]) AS [MaxPrimaryKeyValue] FROM [dbo].[Classes] 
-- ORDER BY TABLE_SCHEMA,TABLE_NAME