-- 10 List Id Generator_Primary Keys.sql
-- next id values are stored in the fpscdb001_system.id_generator table
-- Replace the default schema fpscdb001 to reflect your Footprints database schema if needed
-- this script creates a view that shows the next id values along with the cooresponding table name and pk column name
--
IF object_id('fpscdb001_system.v_TablePrimaryKey', 'V') IS NOT NULL
BEGIN
	DROP VIEW fpscdb001_system.v_TablePrimaryKey;
END
GO

CREATE VIEW fpscdb001_system.v_TablePrimaryKey
AS
SELECT t.TABLE_SCHEMA
	,t.TABLE_NAME
	--	,KCU.TABLE_NAME AS Table_Name
	--	,KCU.CONSTRAINT_NAME AS Constraint_Name
	,CASE 
		WHEN t.TABLE_NAME IN ('data_mart_load_lock')
			THEN 'data_mart_load_lock_id'
		WHEN t.TABLE_NAME IN ('time_zone_region')
			THEN 'time_zone_region_id'
		ELSE KCU.COLUMN_NAME
		END AS PK_COLUMN_NAME
	,CASE 
		WHEN t.TABLE_NAME IN (
				'data_mart_load_history'
				,'data_mart_load_lock'
				)
			THEN t.TABLE_NAME
		ELSE t.TABLE_SCHEMA + '.' + t.TABLE_NAME
		END AS db_table_name_for_id_generator
	,t.TABLE_SCHEMA + '.' + t.TABLE_NAME AS FullTableName
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN (
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
		AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
		AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
		AND KCU.TABLE_NAME = TC.TABLE_NAME
	) ON tc.TABLE_SCHEMA = t.table_schema
	AND tc.TABLE_NAME = t.table_name
	AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
	AND ORDINAL_POSITION = 1
WHERE t.TABLE_TYPE = 'BASE TABLE';
GO

IF object_id('fpscdb001_system.v_IdGeneratorPrimaryKey', 'V') IS NOT NULL
BEGIN
	DROP VIEW fpscdb001_system.v_IdGeneratorPrimaryKey;
END
GO

CREATE VIEW fpscdb001_system.v_IdGeneratorPrimaryKey
AS
SELECT i.*
	,v.FullTableName
	,v.PK_COLUMN_NAME
FROM fpscdb001_system.id_generator i
LEFT JOIN fpscdb001_system.v_TablePrimaryKey v ON v.db_table_name_for_id_generator = i.db_table_name;
GO

SELECT *
FROM fpscdb001_system.v_IdGeneratorPrimaryKey
Order by db_table_name;
